My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 357: Prepared statement is not reused on repeated run of same query
1 person starred this issue and may be notified of changes. Back to list
Status:  NoFix
Owner:  ----
Closed:  Feb 2014


 
Reported by rbrt8...@gmail.com, Jan 23, 2014
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


Jan 24, 2014
#1 rbrt8...@gmail.com
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.
Feb 2, 2014
Project Member #2 mich...@kleehammer.com
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.

Status: NoFix
Feb 3, 2014
#3 rbrt8...@gmail.com
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

Powered by Google Project Hosting