Questions about return codes and transactions

[mgilkey]

Hi,

I work as a Software QA Engineer for a database-related company and I
am starting to use pyodbc to help test some of our software.

I have several questions that ought to be answered by the
documentation,
but aren't (or else I haven't found all of the documentation!). Here
are the first few.

1) Most or all of my "BEGIN WORK;" statements get back a non-zero
return code (presumably an error). I have set
connection.autocommit
to False. I expected this would require me not only to explicitly
COMMIT WORK, but also to explicitly execute BEGIN WORK. Am I
wrong?
When autocommit is off, does each COMMIT implicitly start a new
transaction, thus making BEGIN WORK redundant or wrong?
And when I first make the connection, is a transaction already
started?

2) I have been trying to use pyodbc to do simple concurrent tests in
which 2 (or more) users "take turns". For example, one user might
acquire a lock on a row and then wait while the other user tries
to update that row (which should of course be blocked due to the
lock).
This more or less works, but I do not seem to get appropriate
return
codes from the calls to the pyodbc library's connection.execute()
method.
Here are three contrasting cases -- the first one gets what I
assume
is an error code (-1) while the second gets a value (0) that seems
to indicate success and the third gets yet a different value (1).

cmd = "CREATE TABLE table4 (id INTEGER); COMMIT WORK;"
returnValue = connection1.execute(cmd)
# returnValue is 0, presumably meaning no error.


cmd = "GRANT ALL ON table4 TO role2; COMMIT WORK;"
returnValue = connection1.execute(cmd)
# returnValue is -1, presumably meaning error.
# Yet subsequent statements by role2 work, indicating
# that role2 was granted the privileges.


cmd = "INSERT INTO table4 (id) VALUES (1); COMMIT WORK;"
returnValue = connection1.execute(cmd)
# returnValue is 1. The record does show up in subsequent
# queries, so presumably it got inserted.

If subsequent parts of the test show that all of these statements
worked, why do I get different error messages from all of them?

Note: I executed COMMIT WORK as an SQL statement, rather than call
the connection1.commit() method. Is that a bad idea?
(For my tests, I often need to issue COMMITs that do not coincide
with
the last statement that I pass in an execute() call.)


3) For concurrency testing, I do not commit every statement as soon as
I execute it. For example, I might do the following (through
ODBC):

# user1:
cmd = "BEGIN WORK; UPDATE foo WHERE id = 123;"
connection1.execute(cmd)
# Do not commit yet!

# user2:
cmd = "BEGIN WORK; UPDATE foo WHERE id = 123;"
connection2.execute(cmd)
# Expect either an error code or a delay/timeout because
the
# row is already locked.

# user1:
cmd = "COMMIT WORK;"
connection1.execute(cmd)
# I get a non-zero return code here, which implies an
error,
# but I don't see why I can't commit here, and in fact
the
# earlier UPDATE (or INSERT or whatever) seems to work,
so
# the commit seems to have succeeded, not failed.

Why can't I execute a COMMIT WORK this way without getting a
return value
that seems to imply an error?

Yes, I know I'm complaining a lot ;-) so let me say that I really
like
the basic functionality of pyodbc. I just wish that the documentation
was more extensive. If you have a pre-release draft of improved
documentation, I would like to see a copy and would be happy to send
feedback on it.

--mgilkey

[mkleehammer]

1) You are correct about autocommit. When autocommit is off, the
default, then implicit transactions are created automatically. When
you call cnxn.commit(), everything since the previous commit is
committed. In fact, this is normal ODBC behavior and the database
itself is managing the implicit transactions. You do not need your
BEGIN WORK statements.


2) First, I would use cursors. Your example code is using cnxn.execute
() instead of using a cursor. This creates a new cursor for every
call.

Second, make sure you are using the latest version (2.1.16 I
believe). Cursor.execute should always return the cursor, never a
return value. Some earlier versions had a bug that sometimes returned
a row count. This row count (accessible via Cursor.rowcount) is
always -1 for statements that can't return values (CREATE TABLE) and
the number of rows affected for update and delete statements. This
would be 0 if your SQL didn't affect any rows.

pyodbc doesn't generally return error codes. If an error occurs, you
will get an exception. These are documented in the DB API document
and in the pyodbc wiki pages.

3) I really don't know what you mean by return value unless it is the
rowcount described above. If you didn't get an exception, it worked
fine. As I mentioned in (1) though, you really want to use cnxn.commit
() instead.

I hope that helps. Good luck.