Skip to Content.
Sympa Menu

freetds - [developers] SQLPrepare/SQLExecute with sp_prepare/sp_execute

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Andrea Spinelli" <aspinelli AT imteam.it>
  • To: freetds AT franklin.oit.unc.edu
  • Subject: [developers] SQLPrepare/SQLExecute with sp_prepare/sp_execute
  • Date: Thu, 19 Sep 2002 06:22:30 -0400


Hi list,

I'd like to start a thread about a possible different implementation of
SQLPrepare/SQLExecute.

A document suggested to me by Frediano, that is,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp

explains that SQL server uses internal procedures (sp_prepare)
for prepared statements.

Since I need prepared statements with placeholders
on a Linux box which accesses a SQL Server 7.0, I
tried to explore the matter.

The current implementation does not work very well
because it essentially re-submits the same query every
time. Moreover, at least in my case, it mysteriously used
the double quote " as comment character, which made SQLServer
angry at me.

I have done a bit of reverse engineering with windump
and discovered some things.

Currently, in my working copy of odbc.c, I have changed things
so that, e.g., preparing

select * from T where a = ?

becomes:

declare @handle int
set @handle = NULL
exec sp_prepare @HANDLE OUTPUT, N'@SP0 varchar(80)',
N'select * from T where a = @SP0', 1

SQLs answers with a numeric handle, e.g. 10, and executing
with ? = 4 becomes

exec sp_execute 10, '4'

SQL answers with the result rows, all right.

After that I ought to do sp_unprepare 10, but
currently I'm lazy and do not do anything...

I do have problems, for instance with update statements,
because I fixed things up for statements which
return rows, and the situation is all messed up for
statements without returned rows - actually, my
problem is that I do not understand the syntax of TDS!
I am just exploiting the tds library, and my particular
case is new.

I ask you:

. do you think the sp_prepare way is interesting?
of course, it should be made compatible with all
the servers, maybe falling through the current behaviour
. any clue about handling variable result sets?

Thanks for your attention
Andrea




  • [developers] SQLPrepare/SQLExecute with sp_prepare/sp_execute, Andrea Spinelli, 09/19/2002

Archive powered by MHonArc 2.6.24.

Top of Page