Skip to Content.
Sympa Menu

freetds - RE: [freetds] Inserting data with stored procedure problem

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Michael Kochetkov" <Michael.Kochetkov AT synartra.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Inserting data with stored procedure problem
  • Date: Fri, 15 Oct 2004 16:25:51 +0400

> > 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);
}





Archive powered by MHonArc 2.6.24.

Top of Page