freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] SQLExecDirect() + SQLDataParam() + SQLPutData() fails
- From: Sebastien FLAESCH <sf AT 4js.com>
- To: freetds AT lists.ibiblio.org
- Subject: Re: [freetds] SQLExecDirect() + SQLDataParam() + SQLPutData() fails
- Date: Fri, 29 Jun 2018 15:50:59 +0200
Attached, the code example to reproduce:
When using SQLPrepare() + SQLExecute():
$ gcc -D USE_PREP_EXEC -o odbctest3.bin odbctest3.c ...
$ ./odbctest3.bin
>> prepare insert...
>> bind ...
>> execute...
>> param data...
>>>>> put varchar(max)
>> param data...
When using SQLExecDirect():
$ gcc -o odbctest3.bin odbctest3.c ...
$ ./odbctest3.bin
>> bind ...
>> exec direct...
>> param data...
>>>>> put varchar(max)
Error -1 at: SQLPutData 1
Diagnostic info:
SQL State: HY010
SQL code : 0
Message : [FreeTDS][SQL Server]Function sequence error
What is wrong in my ODBC function sequence?
Cheers,
Seb
On 06/29/2018 03:09 PM, Sebastien FLAESCH wrote:
Hi all,
Just asking before sending a little code sample to reproduce:
Is there a know limitation regarding VARCHAR(MAX)/VARBINARY(MAX) parameters
and SQLExecDirect() + SQLParamData() + SQLPutData() ?
When using SQLPrepare() + SQLExecute() + SQLParamData() + SQLPutData(), I can
send Large Objects, but with SQLExecDirect(), I get the error:
[FreeTDS][SQL Server]Function sequence error
When doing the first SQLPutData() ...
Seb
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
https://lists.ibiblio.org/mailman/listinfo/freetds
/*
Author : sf AT 4js.com
FreeRDS version : 1.00.80
Platform : Linux 64b (Debian 8.10)
Database : SQL Server 2017
Test : Sending LOB data using SQLExecDirect() + SQLPutData()
Problem details:
Trying to send LOB data with SQLPutData() for an INSERT, we get a function
sequence error at SQLPutData(), when using SQLExecDirect().
When using SQLPrepare() + SQLExecute(), SQLPutData() succeeds.
*/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#define CHECK_RCODE(t,h,m) \
if ( rcode != SQL_NO_DATA \
&& rcode != SQL_SUCCESS \
&& rcode != SQL_SUCCESS_WITH_INFO \
&& rcode != SQL_NEED_DATA ) { \
fprintf(stderr,"Error %d at: %s\n",rcode,m); \
getErrorInfo(t,h); \
exit(1); \
}
void getErrorInfo(SQLSMALLINT sqlhdltype, SQLHANDLE sqlhandle)
{
SQLRETURN rcode = 0;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER naterror = 0;
SQLCHAR msgtext[SQL_MAX_MESSAGE_LENGTH + 1];
SQLSMALLINT msgtextl = 0;
int ifxerror = 0;
rcode = SQLGetDiagRec((SQLSMALLINT) sqlhdltype,
(SQLHANDLE) sqlhandle,
(SQLSMALLINT) 1,
(SQLCHAR *) sqlstate,
(SQLINTEGER *) & naterror,
(SQLCHAR *) msgtext,
(SQLSMALLINT) sizeof(msgtext),
(SQLSMALLINT *) & msgtextl);
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", (char *) sqlstate);
fprintf(stderr, " SQL code : %d\n", (int) naterror);
fprintf(stderr, " Message : %s\n", (char *) msgtext);
}
main(int argc,char **argv)
{
SQLRETURN rcode;
SQLHENV m_henv;
SQLHDBC m_hdbc;
SQLHSTMT m_hstmt;
SQLHSTMT m_hstmt2;
int i,j;
char * dbname;
char * usernm;
char * passwd;
char * sql;
SQLLEN v_ind;
void * myhandle = NULL;
#define BUFFER_SIZE 4096
void * buf = malloc(BUFFER_SIZE);
if (argc == 1) {
dbname = "ftm_msvtest1_ida_utf8_2017";
usernm = "msvuser";
passwd = "fourjs";
} else if (argc == 4) {
dbname = argv[1];
usernm = argv[2];
passwd = argv[3];
} else {
fprintf(stderr,"Usage: %s dsn user pswd\n", argv[0]);
exit(1);
}
m_henv = 0;
rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,NULL,"SQLAllocHandle EnvH");
rcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"ODBC V3");
m_hdbc = 0;
rcode = SQLAllocHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_henv, (SQLHANDLE *) &m_hdbc);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLAllocHandle DbcH");
rcode = SQLConnect((SQLHANDLE) m_hdbc,
(SQLCHAR *) dbname,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) usernm,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) passwd,
(SQLINTEGER) SQL_NTS);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLConnect");
rcode = SQLSetConnectAttr(m_hdbc,
SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLSetConnectAttr(autocommit)");
m_hstmt = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 1");
SQLExecDirect(m_hstmt, (SQLCHAR *) "drop table tab1", SQL_NTS);
rcode = SQLExecDirect(m_hstmt, (SQLCHAR *) "create table tab1 (k integer, t nvarchar(max))", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecDirect create table");
m_hstmt2 = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt2);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 2");
sql = "insert into tab1 values (1,?)";
#ifdef USE_PREP_EXEC
fprintf(stdout,">> prepare insert...\n");
rcode = SQLPrepare(m_hstmt2, (SQLCHAR *) sql, SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLPrepare insert");
#endif
fprintf(stdout,">> bind ...\n");
rcode = SQLBindParameter(m_hstmt2, 1, SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_LONGVARCHAR,
0x10000000, //SQL_SS_LENGTH_UNLIMITED,
0,
&myhandle,
0,
&v_ind);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLBindParameter 1");
v_ind = SQL_DATA_AT_EXEC;
#ifdef USE_PREP_EXEC
fprintf(stdout,">> execute...\n");
rcode = SQLExecute(m_hstmt2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLExecute insert #1");
#else
fprintf(stdout,">> exec direct...\n");
rcode = SQLExecDirect(m_hstmt2, (SQLCHAR *) sql, SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLExecute insert #1");
#endif
if (rcode == SQL_NEED_DATA) {
SQLRETURN rcode = SQL_NEED_DATA;
void * myobj;
while (rcode == SQL_NEED_DATA) {
fprintf(stdout,">> param data...\n");
rcode = SQLParamData(m_hstmt2, (SQLPOINTER) & myobj);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLParamData 1");
if (rcode == SQL_NEED_DATA) {
SQLRETURN rcode;
strcpy(buf, "123456789012345678901234567890");
fprintf(stdout,">>>>> put varchar(max) \n");
rcode = SQLPutData(m_hstmt2, (SQLPOINTER) buf, strlen(buf));
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLPutData 1");
}
}
}
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFreeHandle 2");
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFreeHandle 1");
rcode = SQLDisconnect(m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLDisconnect");
rcode = SQLFreeHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLFreeHandle DbcH");
rcode = SQLFreeHandle(SQL_HANDLE_ENV, (SQLHANDLE) m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLFreeHandle EnvH");
}
-
[freetds] SQLExecDirect() + SQLDataParam() + SQLPutData() fails,
Sebastien FLAESCH, 06/29/2018
- Re: [freetds] SQLExecDirect() + SQLDataParam() + SQLPutData() fails, Sebastien FLAESCH, 06/29/2018
Archive powered by MHonArc 2.6.24.