RE: [Jython-users] zxJDBC not committing ?

[brian z. <bz...@zi...>]

Alex,

You are correct that the code snippet below will not commit the
transaction.  By default, the cursor created by calling .cursor() is not
in auto-commit mode.  In order to commit your statements you need to do
one of the following:

[1] execute db.commit() after a series of statements you want commited
[2] turn on auto-commit

To do [2]:

 db = zxJDBC.connect(database, dbuser, dbpass, driver)

 # no statements will be auto-commited
 print db.autocommit
 c = db.cursor()
 c.execute("create table a (b int, c int)")
 c.execute("insert into a values (1, 1)")
 c.execute("insert into a values (1, 2)")
 c.execute("insert into a values (1, 3)")
 db.commit()

 # all statements will now be auto-commited
 db.autocommit = 1
 print db.autocommit
 c.execute("insert into a values (1, 4)")
 # db.commit() is unnecessary since the change is auto-commited

 db.close()

It also makes sense that the interactive interpreter's queries worked
because you were within the same database session and as such had access
to the uncommited changes.

[NOTE] Since both the Python DB API and JDBC have .commit() on the
connection be careful with threads.  A connection used to create cursors
across threads is dangerous since one thread might call .commit() on the
connection while other threads are in the middle of something.  It is
best to allocate a new connection per thread or use a connection pool
(Datasource or otherwise) to make sure no two threads are sharing a
single connection.

I'll update the documentation to make this more apparent.  Let me know
if you have any other issues.

thanks,

brian

> -----Original Message-----
> From: jyt...@li... 
> [mailto:jyt...@li...] On Behalf 
> Of Ale...@di...
> Sent: Friday, March 22, 2002 10:22 PM
> To: jyt...@li...
> Subject: [Jython-users] zxJDBC not committing ?
> Importance: High
> 
> 
> I am putting together a simple application which has to 
> update an MSSQL
> database.  Here is what I do:
> 
> database = "jdbc:microsoft:sqlserver://localhost:1433"
> dbuser = "user"
> dbpass = "password"
> driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"
> 
> 
> db = zxJDBC.connect(database, dbuser, dbpass, driver)
> dbcursor = db.cursor()
> dbcursor.execute("Use PRS")
> 
> ...............................
> batchname = 'abcdef' # generated as the application goes 
> through a couple
> of directories
> curtime = time.strftime("%m-%d-%Y %H:%M:%S",time.localtime())
> stmt = "INSERT INTO dbo.FTP_Recon (Batch_name, Date_sent) VALUES
> ('%s','%s')" % (batchname,curtime)
> cursor.execute(stmt)
> 
> ......
> dbcursor.close()
> db.close()
> 
> 
> So, what happens is this: when I run the whole script, it 
> doesn't seem to
> update anything. However, if I start the query analyzer after 
> I run the
> script, it seems like mssql is messed up because if I run a 
> query on it, it
> wouldn't go for quite a while.
> 
> Now, if I run these commands at the python shell, they 
> execute ok. After I
> do the insert statement, if I do a "select * from FTP_recon" 
> it gives me a
> result , reflecting the insert statement. However, if I run a 
> query in the
> query analyzer to see whether the database has updated, it 
> shows me that it
> hasn't.
> 
> Any ideas / hints why it is not inserting the data , or it is 
> not commiting
> the data to the database ? Btw, it is not a permissions problem in the
> mssql since I log into query analyzer with the same username 
> that I use in
> the script...Also, when I run the same query in the query analyzer, it
> works just fine.
> 
> 
> Thanks,
> 
> Alex Kotchnev
> Diversified Information Technologies
> 
> 
> _______________________________________________
> Jython-users mailing list
> Jyt...@li...
> https://lists.sourceforge.net/lists/listinfo/jython-users
>