FreeTDS Development Group

["Andrea Spinelli" <aspinelli AT imteam.it>]

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