FreeTDS Development Group

["Michael Kochetkov" <Michael.Kochetkov AT synartra.com>]

> > Hello,
> > I have a problem with the following case:
> > I have MS SQL Server and two clients: Windows (MS ODBC 
> > Driver) and Linux
> > (FreeTDS).
> > The following procedure inserts data into a TLSP table (@ObjectId,
> > @ObjectTypeRef and @ConfigurationId are the foreign keys):
> > create procedure dbo.insertTLSP
> > @ObjectId int, @ObjectTypeRef int, @ConfigurationId int, 
> > @Name varchar(255),
> > @Type int, @CreationDate DateTime, @LoadDate DateTime, @Data 
> > image, @id int
> > output as
> > set nocount on
> > if @ObjectTypeRef is null
> > begin
> >     select @ObjectTypeRef = Type from TObject where 
> > ObjectId = @ObjectId
> > end
> > insert TLSP (ObjectId, ObjectTypeRef, ConfigurationId, Name, Type,
> > CreationDate, LoadDate, Data)
> > values(@ObjectId, @ObjectTypeRef, @ConfigurationId, @Name, @Type,
> > @CreationDate, @LoadDate, @Data)
> > select @id = SCOPE_IDENTITY()
> > go
> > 
> > I call the SP above the following way:
> > "{? = call insertTLSP(3, null, 1, 'test name', 1, {ts '2004-10-13
> > 10:12:03'}, null, ?, ?)}"
> > 
> 
> Although seems similar wire it's very different. Calling 
> store procedure
> with fixed parameters it's still not supported. For this 
> reason FreeTDS
> try to prepare statement using normal prepare (instead of using store
> procedure calls). However (even from ODBC specifications) output
> parameters are supported only for store procedure calls.
> However the log show me that when FreeTDS call prepared statement
> (SQLExecute) it use output parameters. It seems also that mssql don't
> claim about the call but about the declarations... Perhaps 
> it's possible
> to use prepare and output parameters in msql ?? It this it's true it
Yes, it is. At least I have never thought about it.

> should be possible to fix your problem without implementing constant
> store procedure parameters (that is fixing the problem 
> quickly). If this
> it's not true you'll have to wait more...
> 
> I'll write a test...
Well, we are pretty in a hurry. So, I have had some time today and I have
written some tests. Sorry, I was unable to do it several days ago.
The code below is a little bit modified MSDN
howto\odbc\ProcessReturnCodes\ProcessReturnCodes.cpp examle. But please,
note that I need to be able to run "{? = call insertT2(1,?)}" call as
prepared statement too. Though I might be able to change my framework to
calls SPs the way you recommend for FreeTDS.

Thank you for your help.
-
Michael Kochetkov.

P.S. The test case code:
///////// (Portions copyright by MS MSDN)
// if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[T2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
// drop table [dbo].[T2]
// GO
// 
// CREATE TABLE [dbo].[T2] (
//      [f1] [int] NULL 
// ) ON [PRIMARY]
// GO
// CREATE PROCEDURE insertT2 @f1 int, @id int output AS
// set nocount on
// insert T2(f1) values (@f1)
// select @id = @f1
// GO

#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

#define MAXBUFLEN  255

SQLHENV   henv = SQL_NULL_HENV;
SQLHDBC   hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT  hstmt1 = SQL_NULL_HSTMT;

void Cleanup();

int main() {
  RETCODE retcode;
  // SQLBindParameter variables.
  SWORD   sParm1=0, sParm2=1;
  SDWORD  cbParm1=SQL_NTS, cbParm2=SQL_NTS;

  // Allocate the ODBC environment and save handle.
  retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
  if( (retcode != SQL_SUCCESS_WITH_INFO) &&
      (retcode != SQL_SUCCESS)) {
    printf("SQLAllocHandle(Env) Failed\n\n");
    Cleanup();
    return(9);
  }

  // Notify ODBC that this is an ODBC 3.0 app.
  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
              (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
  if( (retcode != SQL_SUCCESS_WITH_INFO) &&
      (retcode != SQL_SUCCESS)) {
    printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
    Cleanup();
    return(9);    
  }

  // Allocate ODBC connection handle and connect.
  retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
  if( (retcode != SQL_SUCCESS_WITH_INFO) &&
      (retcode != SQL_SUCCESS)) {
    printf("SQLAllocHandle(hdbc1) Failed\n\n");
    Cleanup();
    return(9);
  }

  retcode = SQLConnect(hdbc1, (UCHAR*)"Mike2003", SQL_NTS,
              (UCHAR*)"mike",SQL_NTS, (UCHAR*)"mike", SQL_NTS);

  if( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLConnect() Failed\n\n");
    Cleanup();
    return(9);
  }

  retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
  if( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLAllocHandle(hstmt1) Failed\n\n");
    Cleanup();
    return(9);
  }

  // Bind the return code to variable sParm1.
  retcode = SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,
      SQL_INTEGER,0,0,&sParm1,0,&cbParm1);
  if( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLBindParameter(sParm1) Failed\n\n");
    Cleanup();
    return(9);
  }

  // Bind the output parameter to variable sParm2.
  retcode = SQLBindParameter(hstmt1,2,SQL_PARAM_OUTPUT,SQL_C_SSHORT,
              SQL_INTEGER,0,0,&sParm2,0,&cbParm2);
  if( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLBindParameter(sParm2) Failed\n\n");
    Cleanup();
    return(9);
  }

  // Execute the command. 
  retcode = SQLExecDirect(hstmt1, (UCHAR*)"{? = call insertT2(1,?)}", 
              SQL_NTS);
  if( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLExecDirect Failed\n\n");
    Cleanup();
    return(9);
  }

  while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA );

  // Clean up. 
  SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
  SQLDisconnect(hdbc1);
  SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
  SQLFreeHandle(SQL_HANDLE_ENV, henv);
  return(0);
}

void Cleanup() 
{
  if (hstmt1 != SQL_NULL_HSTMT)
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
  if (hdbc1 != SQL_NULL_HDBC)
  {
    SQLDisconnect(hdbc1);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
  }
  if (henv != SQL_NULL_HENV)
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
}