[Jython-users] bug on jdbc with commit behavior ?

[olivier m. <ome...@gm...>]

Hello,

when using jython 2.1, i was surprised to see that the commit was really
done only when the connection is release :
conn.close()

Look at this code :

from oracle.jdbc.driver import OracleDriver
from java.sql import DriverManager
from java.sql import SQLException
#from java.lang import Integer
from java.lang import Boolean

def connect(un, pw, sid, host, port):
     driver = OracleDriver()
     DriverManager.registerDriver(driver)
     connection = "jdbc:oracle:thin:@%s:%s:%s" % (host, port, sid)
     conn = DriverManager.getConnection(connection, un, pw)
     conn.setAutoCommit(Boolean.parseBoolean("false"));
     return conn

def getConnection():
   connect("scott","tiger","ome1","localhost",'1522')


def populate(conn, begin_row=0, batch_size=100, batch_count=100):
    try:
        #print "Before prepare"
        t0=time.time()
        pstmt = conn.prepareStatement("insert into
scm_data(id,msisdn,iccid,imsi,persoId,firstSwitch,capName,capValue) values
(?,?,?,?,?,?,?,?)");
        nbRows=0
        for i in range(batch_count):
            rows=[]
            for j in range(batch_size):
                #print "Before set"
                pstmt.setLong(1,begin_row+i*batch_size+j);
                pstmt.setString(2,str(begin_row+i*batch_size+j));
                pstmt.setString(3,'ICCID'+str(begin_row+i*batch_size+j));
                pstmt.setString(4,'IMSI'+str(begin_row+i*batch_size+j));
                pstmt.setString(5,'**PersoId**');
                pstmt.setLong(6,1);
                pstmt.setString(7,'**RenderingName**');
                pstmt.setString(8,'id'+str(i));
                pstmt.addBatch();
            #print "before executeBatch"
            rows = pstmt.executeBatch()
            conn.commit
            print "Inserted %d lines" % len(rows)
            nbRows+=len(rows)
        conn.commit
        print "Commited %d lines" % nbRows
        print "Batch insert %d lines in : %3.2f sec" % (nbRows, time.time()
- t0)

    except SQLException, e:
        print "ERROR !"
        print(e)
        conn.rollback()
        raise e

if __name__ == "__main__":
    un = 'scott'
    pw = 'tiger'
    sid = 'ome1'
    host = 'localhost'
    port = '1522'
    conn = connect(un, pw, sid, host, port)
    print "Connected as %s on %s:%s:%s" % (un,host,sid,port)
    populate(conn,1000,100,100)
    try:
        conn.close()
    except:
        pass

In the populate() procedure you have a commit every batch_size.
I was surprised to see that it does not work
The commit is not taken into account and is done only in the conn.close()

If you put in commentary the conn.close() line, nothing is inserted in the
table which is not the normal behaviour

I did many test with a DBA to understand and :
- there is only a commit in the Oracle trace at the end with the conn.close
()
- this code of course work very well in pure java

What's wrong ? I don't see any reference to this type of error
Thanks for your help,
Olivier