From: <Ale...@di...> - 2002-03-23 04:22:47
|
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 |
From: brian z. <bz...@zi...> - 2002-03-23 15:01:43
|
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 > |
From: Noel R. <no...@ya...> - 2002-03-23 19:50:49
|
> >[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. Brian -- while we're on the subject I have a follow up question... is there a connection pool tool that you've used with zxJDBC that works particularly well (or works at all)? I've been looking around for pool tools that hit the JDBC 2.0 spec (which it seems like zxJDBC needs), and haven't found anything that I really like. Thanks, Noel _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com |
From: brian z. <bz...@zi...> - 2002-03-24 23:40:57
|
Noel, I have not use Poolman but have heard good things about (though I see the SF project has stopped being supported). I generally either use an app server's pool if running in [Py]Servlet or a ConnectionPooledDataSource if I'm running the interpreter or just coding a script. In general I don't have too many threads in scripts so it's not generally a big deal. When I deal with threads it's primarily in an appserver in which case the provided pool is generally sufficient. hope this helps, brian > -----Original Message----- > From: Noel Rappin [mailto:no...@ya...] > Sent: Saturday, March 23, 2002 1:46 PM > To: brian zimmer; Ale...@di...; > jyt...@li... > Subject: RE: [Jython-users] zxJDBC not committing ? > Importance: High > > > > > > >[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. > > Brian -- while we're on the subject I have a follow up > question... is there > a connection pool tool that you've used with zxJDBC that > works particularly > well (or works at all)? I've been looking around for pool > tools that hit > the JDBC 2.0 spec (which it seems like zxJDBC needs), and > haven't found > anything that I really like. > > Thanks, > > Noel > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > |