Skip to Content.
Sympa Menu

freetds - Re: [freetds] Status of UTF-8 support in 0.83 dev

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Sebastien FLAESCH <sf AT 4js.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Status of UTF-8 support in 0.83 dev
  • Date: Mon, 08 Sep 2008 17:35:19 +0200

Frediano,

Sorry my YES was too fast: I should have checked with a simple ODBC program.

Even inserting does not fully work for now:

When binding with a small ColumnSize (like 2), I get invalid data in the
SQL Server Profiler trace (only pieces of the value).

Looks like the conversion from UTF-8 to UCS-2 relies on the ColumnSize
and does not work properly when that size is small...
(Maybe the conversion buffer used is allocated according to ColumnSize,
as a number of bytes?)

See attached example: Using ColumnSize=2, it does not insert all data,
but when using ColumnSize>=6 it works...

Binding first value (4 bytes) with ColumnSize=4, it works, and second
value (6 bytes) it works with ColumnSize=6...

Since ColumnSize defines a number of CHARACTERS, something is wrong here.

Seb

ZIGLIO, Frediano, VF-IT wrote:
Frediano,

I have just tested latest sources with SQLBindParameter using:

ParameterType = SQL_WVARCHAR
ColumnSize = 2

It works now but I just wanted to confirm something with you:

When binding with SQL_VARCHAR (no W) with ColumnSize=20, SQL Server
Profile trace shows the parameter for sp_prepare is defined as:

@P1 VARCHAR(20)

When binding with SQL_WVARCHAR, I see following type:

@P1 NVARCHAR(40)
^^----!!!

Even if ColumnSize=20 ...

Why 40 and not 20? Is this expected?


Try this patch

freddy77


------------------------------------------------------------------------

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

/* Test UTF-8 character set with SMALL columns sizes like NCHAR(2)
* Author: Sebastien FLAESCH
*
* The ODBC config parameter ClientCharset must be set:
* ClientCharset = UTF-8
*
* When looking at the SQL Server Profiler output, you see only pieces of
values
* or even nothing for Chinese characters.
* Looks like the conversion from UTF-8 to UCS-2 allocates a conversion buffer
* according to ColumnSize. If ColumnSize=2 you can only convert TWO ASCII
chars
* (like ab) of ONE Latin char with accute (like é).
*/

static const char * g_dbname = "freetds_msvtest1_cobra_utf8";
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];
static SQLINTEGER v_ind_char;

static char v_varchar[41];
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 mytab1", SQL_NTS);
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table mytab1 (k int,
c nchar(2), vc nvarchar(2))", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.1");

fprintf(stderr,">> SQLPrepare insert...\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into mytab1 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");

/* REPLACE ColumnSize by 4 and it works */
SQLBindParameter(m_hstmt1, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_WCHAR, 2, 0, v_char, sizeof(v_char),
&v_ind_char);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2");

/* REPLACE ColumnSize by 6 and it works */
SQLBindParameter(m_hstmt1, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_WVARCHAR, 2, 0, v_varchar,
sizeof(v_varchar), &v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 3");

v_key = 1;
strcpy(v_char, "éô");
v_ind_char = strlen(v_char);
strcpy(v_varchar, "鴻鴻");
v_ind_varchar = strlen(v_varchar);
fprintf(stderr,">> SQLExecute insert:\n");
fprintf(stderr,">> Param 1 : [%s] indic=(%d)\n", v_char, v_ind_char);
fprintf(stderr,">> Param 2 : [%s] indic=(%d)\n", v_varchar,
v_ind_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");

}




Archive powered by MHonArc 2.6.24.

Top of Page