platiumky / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

Prepared statement is not reused on repeated run of same query #357

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago
What steps will reproduce the problem?
======================================
1. create a connection and cursor using pyodbc
2. run the same query twice
3. while doing that inspect traffic with a network sniffer such as wireshark

What is the expected output? What do you see instead?
=====================================================
Expected output is to see the readable SQL statement go over the line once, 
then the second time just binary communication. Instead I can read the 
statement twice in the package sniffer

import pyodbc
cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER=XXX;UID=XXX;PWD=XXX;')
cursor = cnxn.cursor()
x = '--I can read this\nselect 1'
cursor.execute(x)
row = cursor.fetchone()
cursor.execute(x)
row = cursor.fetchone()

What version of the product are you using? On what operating system?
====================================================================
Latest pyodbc python 2.7 windows with sql server

Please provide any additional information below.
================================================
The line

   if (pSql != cur->pPreparedSQL)

in params.cpp seems to compare two pointers to python objects rather than their 
string content for unicity. That's a hunch, I'm not a c professional and don't 
know how to debug c

Original issue reported on code.google.com by rbrt8...@gmail.com on 23 Jan 2014 at 8:47

GoogleCodeExporter commented 8 years ago
I have to withdraw this defect-report. I was testing this with a 
non-parameterized test-query which is smartly optimized away. In 'real life' 
queries this works as expected.

Original comment by rbrt8...@gmail.com on 24 Jan 2014 at 11:22

GoogleCodeExporter commented 8 years ago
Thanks for the follow up comment!  Saved me some time :)

Good luck and I'm impressed that you were analyzing the logs in the first place.

Original comment by mich...@kleehammer.com on 3 Feb 2014 at 12:53

GoogleCodeExporter commented 8 years ago
Actually there's a layer more to this:

I was stumped to find out sql server native driver fakes the preparation
and just internally caches the sql text itself. I can see, stepping through
the C code in visual studio, pyodbc correctly skipping preparation after
the first run, then on the single "sqlexec" call immediately the entire
statement is sent over the line again and again. I 'fixed' some long text
query by reworking to a stored procedure.

I'm using sql server express 2008 and the behaviour is undenyable.

Thanks.

Robert

Original comment by rbrt8...@gmail.com on 3 Feb 2014 at 9:48