| Issue 161: | Transaction not returning valid ResultSet | |
| 1 person starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem?
Simplifying the script involved to the pertinant steps:
cnxn1 = pyodbc.connect(DBServer.odbc_info, autocommit=True)
c1 = cnxn1.cursor()
insertSQL =
"""BEGIN
SET NOCOUNT ON;
declare @message varchar(300)
BEGIN TRANSACTION insert_bill
BEGIN TRY
declare @newBillId int
select @newBillId = MAX(bill_id)+1 from bill
insert into bill (bill_id,updated,updated_by)
values (@newBillId,CURRENT_TIMESTAMP,'PythonTool')
insert into bill_item values (@newBillId,1,123.45)
insert into bill_item values (@newBillId,2,678.90)
COMMIT TRANSACTION insert_bill
SELECT @message=@newBillId --as 'Message'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION insert_bill
SELECT @message='ERROR! '+ERROR_MESSAGE() --as 'Message'
END CATCH
select @message as 'message'
END"""
SQLresult = c1.execute(insertBillSQL)
for row in SQLresult:
print row.message
What is the expected output? What do you see instead?
Expected:
Transaction should _always_ return a single row with a single column called message. If the inserts were successful, this message will be the new bill_id used. If there were errors (ie PK or FK conflict or bad data type), the message will be the text of the error message.
Instead:
The when I try to access the rows of SQLresult, I get:
"pyodbc.ProgrammingError: No results. Previous SQL was not a query."
This is unexpected because the transaction should always return 1 result row.
What version of the product are you using? On what operating system?
python 2.6 (32bit)
eclipse 3.4.2
pyodbc 2.1.8 (for win32 2.6)
Win7 Pro (64bit)
Please provide any additional information below.
I am running thousands of these transactions, and most are fine. However, a sizable chunk (say 5-10%) are hitting this error.
More interestingly, the transaction is actually fully successful even when this occurs. I can look up the desire new data in the database, and it is fully intact. Moreover, copying the exact same query into MS SQL Studio execute exactly as expected, with the expected single row result.
This makes it very difficult for me to diagnose, because identical SQL works as expected most of the time, and it is even successful in these istances. I'm not sure why this error occurs.
While I can just catch the exception and move on (since they have been successful so far), I would like to know when the transaction has actually failed, and with this error I cannot be sure of that.
I'm happy to provide further diagnostics if you need them.
Thanks,
Brendan
Mar 4, 2011
#1
brendan....@gmail.com
Mar 4, 2011
I'd also like to add that I've tried this with autocommit=False. It did not seem impact the outcome.
Mar 6, 2011
I noticed that the above test script contains a few errors (eg insertBillSQL is not defined) so I take it to be paraphrased rather than what was actually run. Here is a complete standalone test script which actually works correctly for me (see attached). Brendan does this work for you too? If so then can you adjust it to reproduce your error?
Mar 6, 2011
Whoops, sorry. Yes, I've stripped this longer query to the essentials and apparently changed the core SQL variable name in only one of its two places. In the actual script the string variable and execution command do indeed match. Sorry about that. I will try the script you posted and report back.
Mar 6, 2011
Comment #4 was me under another account my mistake. I've run the posted script, and as expected it runs fine. I modified it to run many thousands of inserts, and they all run fine. I inserted a bad data type, and the expected "ERROR!" message was returned correctly. In short, I have not been able to recreate the error in the standalone test. My trouble stems from the fact that I don't understand what server-side behaviour is causing this result. Taking the exact same SQL which causes this error via pyodbc will run exactly as expected/intended in MS SQL Manager. I have, however, identified a common thread between the statements that fail (involving a foreign key excluded from the scope of my example above). This leads me to believe that my server is doing something under these conditions which pyodbc does not know how to handle, but which is invisible via SQL Manager. Tomorrow, I will test some of these transactions using a different connection from a C# project and see how it responds. This common thread at least eliminates the relevance of the ~25% (after careful measurement) occurrence of this issue. That's just the appearance rate of these conditions in my source data. I still think there is an issue to be concerned about, as some condition or action on my server is causing pyodbc to not handle the results of these queries correctly, despite my best effort to capture all errors. I will update after further investigation. Thanks for your help.
Mar 7, 2011
Ok, I've identified the cause of the issue, and can reproduce it with a small modification to the standalone test script (attached). The problem was that there was a "print" output statement in a trigger on one of my tables, only executed under the FK conditions mentioned above. I have modified the standalone test script to create an analagous trigger on the bill table which does indeed cause the error I have been experiencing. I am now able to resolve the issue for myself by disabling that (unnecessary) output, but I am happy to continue to help diagnose this issue to have it corrected. Thanks, Brendan
Mar 8, 2011
I observe that in this last example there are *two* result sets returned by the server. The first result set contains no data (as indicated by cursor.description being None, and by the exception you have observed if you try to read rows from the cursor). You can then progress to the next result set using cursor.nextset(), and then the data you are after is available in this second result set. I expect that the message from your print statement would be associated with the first result set, available via the SQLGetDiagRec function in the ODBC API (not exposed to python via pyodbc). This is the same concept as what happens when you omit "SET NOCOUNT ON", in which case the server sends extra informational messages which in python look like extraneous empty result sets. Really pyodbc is doing the right thing here. It would be nice if pyodbc provided a way to read informational messages via SQLGetDiagRec, but otherwise it is faithfully returning the multiple result sets returned by the server.
Mar 8, 2011
I see. That is unexpected, and admittedly is not something I considered (obviously). Yes, it would be helpful to be able to read that data, if only to confirm that no SQL errors occurred. Is there any command that can be used like SET NOCOUNT ON to suppress these messages? I couldn't find reference to anything like that in my searching. Thanks again for your help with this. -Brendan
Sep 13, 2011
Unfortunately there isn't that I know of. I don't know who l...@deller.id.au is, but I am very grateful for the troubleshooting here...
Status:
NoFix
|