Prepared statements w/ the tds protocol
G. Sumner Hayes
SumnerH at fool.com
Wed Mar 14 17:11:55 EST 2001
I'm working on Python bindings for freetds and I'm stuck on prepared
statements w/ SQL2000. I want to do the moral equivalent of:
declare @statement nvarchar(500),
set @statement = 'select * from car where car_id = @p'
set @parameters = '@p int'
exec sp_executesql @Statement, @params = @parameters , @p = 10000000
However, I can't seem to get it to work. Notice that statement and
params need to be nvarchar, otherwise the query would look like:
exec sp_executesql 'select * from car where car_id=@p', @params = '@p int', @p=1000000
So what I tried was essentially:
a=UnicodeEncode("select * from car where car_id=@p");
c="exec sp_executesql"+a+", @params = "+b+", @p=1000000"; /* Really more complicated, but c winds up being this concatenation*/
tds_submit_query_l(tds, c, length(c))
Where tds_submit_query_l my rewrite of tds_submit_query that accepts the
length of a string coming in (since the UTF16 encoded parts of c will
have NUL bytes in them). That just didn't work, though a packet sniffer
shows the string going out as I would expect it to and
tds_submit_query_l works just fine for normal queries.
So I'm guessing that doing this UTF16 encoding of the query isn't the
right way to go.
Has anyone gotten any sort of prepared or sp_executesql statements to
work with MS-SQL 7.0 or 2000? Can you point me in the right direction
at the TDS level?
More information about the FreeTDS