FreeTDS Development Group

[Sebastien FLAESCH <sf AT 4js.com>]

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

}