FreeTDS Development Group

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

Sorry I mean SQL_WVARCHAR, not SQL_NVARCHAR...

I made a little UTF-8 test (see attachment *warning*: UTF-8 file!).

Simple SQL statements without SQL parameters seem to work... (CREATE TABLE, 
INSERT).
But some characters are not converted properly (iconv issue?):
The 'abcéáô' and 'abcéîô' strings become 'abceao' and 'abceio' in the 
database...

Further, I can't execute an INSERT with SQLBindParameter():

When using SQL_WVARCHAR, I get an error but diagnostic info shows unreadable 
strings:

Error -1 at: SQLExecute StmtH
Diagnostic info:
  SQL State: \uffff\uffffu
  SQL code : 0
  Message  :

With SQL_VARCHAR, I get this:

Error -1 at: SQLExecute StmtH
Diagnostic info:
  SQL State: 42000
  SQL code : 4002
  Message  : [FreeTDS][SQL Server]The incoming tabular data stream (TDS) 
protocol stream is incorrect. The stream ended unexpectedly.


Seb

Sebastien FLAESCH wrote:
> Hi all,
>
> I would like to start some tests using ODBC in a UTF-8 environment.
>
> What is the current status of this project?
>
> I found this in the TODO file:
>
> . conversion from ucs2 to utf8, provide for 2+ bytes/character
>
> I have set ClientCharset = UTF8 in the odbc.ini file...
>
> What SQL_* types should I use to bind buffers with SQLBindParameter
> or SQLBindCol, when using UTF8 charset?
>
> For example, would this be ok?
>
>      char v_char[41];   /* Can hold up to 10 UTF-8 characters */
>
>      SQLBindParameter(m_hstmt1, 2, SQL_PARAM_INPUT,
>                       SQL_C_CHAR,
>                       SQL_NVARCHAR, 10, 0, v_char, 0,
>                       &v_ind_char);
>
> Assuming that I use a NVARCHAR(10) in the db?
>
> Thanks!
> Seb
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

/* Test UTF-8 character set
 * Author: Sebastien FLAESCH
 *
 * The ODBC config parameter ClientCharset must be set:
 *   ClientCharset = UTF8
 */

static const char * g_dbname = "freetds_msvtest2_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_char[41];           /* Can hold 10 UTF-8 chars */
static SQLINTEGER v_ind_char;

static char v_varchar[41];        /* Can hold 10 UTF-8 chars */
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 mytab王鴻", 
SQL_NTS);
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table mytab王鴻 (k 
int, c nchar(10), vc nvarchar(10))", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.1");

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab王鴻 
values (1,'aaa','aaa')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.2");
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab王鴻 
values (2,'abcéáô','abcéîô')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.3");
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab王鴻 
values (3,'abc王鴻','abc王鴻傑王鴻傑')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.4");

    /* Now with SQL parameters */
    rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into mytab王鴻 values 
(?,?,?)", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare 1");

    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, 2, SQL_PARAM_INPUT,
                     SQL_C_CHAR,
                     SQL_WCHAR, 10, 0, v_char, 0,
                     &v_ind_char);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2");

    SQLBindParameter(m_hstmt1, 3, SQL_PARAM_INPUT,
                     SQL_C_CHAR,
                     SQL_WVARCHAR, 10, 0, v_varchar, 0,
                     &v_ind_varchar);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 3");

    /* Assign UTF-8 values using SBCS functions, sorry */
    strcpy(v_char, "abc王鴻" );
    v_ind_char = strlen(v_char);
    strcpy(v_varchar, "abc王鴻傑王鴻傑" ); 
    v_ind_varchar = strlen(v_varchar);

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

}