FreeTDS Development Group

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

Frediano,

While testing ParameterType = SQL_WVARCHAR, I faced a problem with 
ColumnSize...

According to MSDN, ColumnSize defines the precision of the type in 
*characters* (not bytes):

      http://msdn.microsoft.com/en-us/library/ms711786(VS.85).aspx

With attached example, when using a CHAR(2) column, I just bind with 
ParameterType=SQL_WVARCHAR and ColumnSize=2 ...

But I get an error:

SQL State: 42000
SQL code : 8016
Message  : [FreeTDS][SQL Server]The incoming tabular data stream (TDS) remote
           procedure call (RPC) protocol stream is incorrect.
           Parameter 3 (""): Data type 0xE7 has an invalid data length or 
metadata length.

Please could you have a look at this?

Note in my SQL Native Client driver, I do also use SQL_WVARCHAR and 
ColumnSize has to be passed as a number of characters.


Thanks!
Seb


Frediano Ziglio wrote:
> Il giorno gio, 14/08/2008 alle 11.53 -0400, James K. Lowden ha scritto:
> > Hi Sebastien, 
> >
> > > James K. Lowden wrote:
> > > > "Wide" characters have a fixed width, usually 16 bits.  UTF-8 has a
> > > > variable width and for the most part can be treated like ASCII
> > > > (strlen(3) et al.).  Declaring UTF-8 as SQL_C_WCHAR incorrectly tells
> > > > the driver there's a character in every two bytes.
> > > Oh take care, actually there are 2 type specifications in
> > > SQLBindParameter:
> > >
> > > SQLRETURN SQLBindParameter(
> > >       SQLHSTMT     StatementHandle,
> > >       SQLUSMALLINT     ParameterNumber,
> > >       SQLSMALLINT     InputOutputType,
> > >       SQLSMALLINT     ValueType,          -- This is the C type
> > >       SQLSMALLINT     ParameterType,      -- This is the SQL equivalent
> > >       SQLULEN     ColumnSize,
> > >       SQLSMALLINT     DecimalDigits,
> > >       SQLPOINTER     ParameterValuePtr,
> > >       SQLINTEGER     BufferLength,
> > >       SQLLEN *     StrLen_or_IndPtr);
> > >
> > > For sure, when using (char *) / UTF-8, ValueType *must* be SQL_C_CHAR,
> > > not SQL_C_WCHAR... (that one is for wchar_t).
> > >
> > > I was talking about ParameterType...
> > >
> > > Should it be  SQL_W[VAR]CHAR  or  SQL_[VAR]CHAR  when binding UTF-8?
> > Oh, I see.  Your question surprises me, then, because IIUC the client-side
> > encoding doesn't matter for ParameterType.  ParameterType describes the
> > data type of the parameter as used in the query, not as represented in the
> > client buffer.  (I"m just an ODBC student.  Correct me if I'm wrong.) 
>
> ParameterType specify the type to use with the server so if SQL_VARCHAR
> is used a VARCHAR is sent to the server while using SQL_WVARCHAR an
> NVARCHAR is used. This cause the problem with japanese encoding,
> SQL_VARCHAR is not able to "contain" japanese so the error. Some
> updates:
> - I split my working patch for wide support committing support for
> server N(VAR)CHAR, very short and seems to work correctly with UTF-8
> (the other part is to support *W functions and SQL_C_WCHAR... and it's a
> very very long work)
> - I ported Sebastian tests in a new src/odbc/unittests/utf8.c test, it
> works correctly and it tests data inserted too. I used a trick to force
> client encoding (using SQLDriverConnect instead of SQLConnect)
> - I wrote a workaround for invalid TDS protocol if a conversion error
> happen. Now libTDS send an empty string... not correct but at least you
> get an error from library and you can continue to issue queries. The
> patch was backported to 0.82 too. I'll write a definitive fix for this
> (it require to handle correctly errors and cancel sending query).
>
> > For instance, any of these pairs works:
> >
> > 	ValueType	ParameterType 
> > 	-------------	-------------
> >         SQL_C_CHAR      SQL_DECIMAL
> >         SQL_C_CHAR      SQL_VARCHAR
> >         SQL_C_DOUBLE    SQL_FLOAT       
> >
> > For character data parameters, the choice of SQL_W[VAR]CHAR or
> > SQL_[VAR]CHAR is governed by the data type of the parameter as understood
> > by the server.  For stored procedures, the parameter type is obvious: it's
> > declared within the procedure text.  For placeholders in open SQL text, I
> > would say the type is always SQL_[VAR]CHAR.  
>
> No, the client encodes the parameter type as specified by the
> application. The server converts the parameter as needed.
>
> > That is, if your query is, 
> >
> >         CREATE TABLE t (name nvarchar(30))
> >         INSERT t values( ? )    
> >
> > You would use SQL_[VAR]CHAR.  
> >
> > Now, what to do when what you really want is equivalent to "INSERT t
> > values( N'string' )"?  I assume it's still SQL_[VAR]CHAR, but I won't know
> > for sure until either Frediano explains or I figure out how sp_execute
> > works.  
>
> sp_execute is a special store procedure witch is embedded in sql server
> and support variable parameters... let's say it's like a printf instead
> of a strcpy. You specify parameters you pass followed by parameters and
> server execute the query.
>
> > One thing you could try is SQLDescribeParam with various combinations
> > using Microsoft's driver.  We would follow whatever it says/expects.  
>
> SQLDescribeParam is a workaround on mssql... it parse the query to get
> parameters type. Obviously on complicated query it isn't able to tell
> the truth... Assuming query is "SELECT * FROM table WHERE x = ?" it
> build a "SET FMTONLY ON SELECT x FROM table" and get x type... this is
> the reason is still unsupported by our driver... nobody wants to write
> the sql parser :)
>
> bye
>   Frediano
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

/* Testing SQL_WVARCHAR and ColumnSize parameter of SQLBindParameter().
 * Author: Sebastien FLAESCH
 *
 * This is a simple ASCII sample, using ColumnSize = 2 (characters).
 *
 * With freetds-0.83.dev.20080901, we get this error:
 *
 *  SQL State: 42000
 *  SQL code : 8016
 *  Message  : [FreeTDS][SQL Server]The incoming tabular data stream (TDS)
 *             remote procedure call (RPC) protocol stream is incorrect.
 *             Parameter 3 (""):
 *             Data type 0xE7 has an invalid data length or metadata length.
 *
 */

static const char * g_dbname = "freetds_msvtest1_cobra";
static const char * g_usernm = "msvuser";
static const char * g_passwd = "fourjs";

#ifdef _WIN32
#include <WINDOWS.H>
#endif
#include <stdio.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); \
   }

static int v_key;
static SQLINTEGER v_ind_key;

static char v_varchar[50];
static SQLINTEGER v_ind_varchar;

static 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_hstmt1;
    int i,j;
    const char * dbname;
    const char * usernm;
    const char * passwd;

    if (argc == 4) {
       dbname = argv[1];
       usernm = argv[2];
       passwd = argv[3];
    } else {
       dbname = g_dbname;
       usernm = g_usernm;
       passwd = g_passwd;
    }

    m_henv = NULL;
    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 = NULL;
    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_hstmt1 = NULL;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 1");

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table tab1", SQL_NTS);
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab1 (k int, c 
varchar(2))", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.1");

    rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab1 values (?,?)", 
SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare insert");

    SQLBindParameter(m_hstmt1, 1, SQL_PARAM_INPUT,
                     SQL_C_LONG, SQL_INTEGER, 0, 0, &v_key, 0, &v_ind_key);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 1");

    SQLBindParameter(m_hstmt1,
                     /* ParameterNumber */         2,
                     /* InputOutputType */         SQL_PARAM_INPUT,
                     /* ValueType */               SQL_C_CHAR,
                     /* ParameterType */           SQL_WVARCHAR,
                     /* ColumnSize */              2, /* Characters! */
                     /* DecimalDigits */           0,
                     /* ParameterValuePtr */       v_varchar,
                     /* BufferLength */            sizeof(v_varchar),
                     /* StrLen_or_IndPtr */        &v_ind_varchar);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2");

    v_key = 1;
    strcpy(v_varchar, "ab");
    v_ind_varchar = strlen(v_varchar);
    rcode = SQLExecute(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 1.1 StmtH");

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFreeHandle StmtH 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");

}