Skip to Content.
Sympa Menu

freetds - Re: [freetds] UTF-8 support: What about N'xxx' string literals?

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] UTF-8 support: What about N'xxx' string literals?
  • Date: Thu, 14 Aug 2008 11:08:45 +0200

James,

James K. Lowden wrote:
> Sebastien FLAESCH wrote:
>> So should not ALL FreeTDS application use the N prefix for string
>> literals, even if the encoding is for example ISO-8859-1?
>
> Only if they're careful about their use of non-Unicode columns.

Is this not the case for most single-byte / non-UNICODE/UCS-2 applications
using other characters as US-ASCII?

I mean, why would I use NCHAR/NVARCHAR if my ISO-8859-? characters fit
in a CHAR/VARCHAR columns?

Ok... one should go with UNICODE now, but we have customers out there with
millions of lines of legacy code, using ISO-8859-1 charset...

Remember our product is a 4GL language, where you can write for example:

MAIN
DATABASE stores
INSERT INTO tab1 VALUES ( 123, "abcéîô" )
END MAIN

That code must work with different RDBMS (Oracle, SQL Server, Informix),
in the charset defined by LANG/LC_ALL. So I don't want to add the N prefix
syntax in our language, that stuff should be transparent...

As far as FreeTDS is concerned, I assume that we can automatically add
the N prefix before any string literal, whatever FreeTDS client charset is
...... correct?

Just to test:

I made a little ODBC test (attached), combining CHAR/VARCHAR, NCHAR/NVARCHAR
and string with and without N prefix, in a GREEK environment (client gets
GREEK charset and database was created with collation Greek_BIN).

In any case the characters are correctly inserted in the database and fetched
back... I checked on the server side if characters are ok in the tables.

Seb

James K. Lowden wrote:
Sebastien FLAESCH wrote:
Otherwise, non-prefixed strings are converted by SQL Server to the
default code page of the database... (following docs).

Actually SQL Server docs talk about "Server side" programming, where
strings should use N prefix for UNICODE (UCS-2), but what about the
clients then?

From my understanding, if I want to use UTF-8 encoded strings in my SQL
stmts, (defining my FreeTDS client charset as UTF-8 and forcing FreeTDS
to convert from UTF-8 to UCS-2 for TDS exchanges), I need always to
specify N prefix to avoid the server-side conversion to the database
charset defined by the collation.

Since FreeTDS converts from any client charset to/from UCS-2 for TDS
exchanges, the final string literal will be encoded in UCS-2 and so the
client is seen as a UCS-2 client from the server... right?

Right. The server parses and interprets the SQL regardless of where and
how it's entered. Unprefixed strings are converted to the single-byte
encoding first, then again to UCS-2 as needed.
I decided there's a method in the madness: by losing information in this
way, the user gains a transparency between char/nchar columns. No matter
the column's datatype, the character stored is always the same, given
"naïve" use i.e. unprefixed string literals. Only when you manifestly
declare your interest in using Unicode -- by prefixing your strings with
'N' -- do you start seeing differences.
Not my first choice, but not utterly stupid, either.
So should not ALL FreeTDS application use the N prefix for string
literals, even if the encoding is for example ISO-8859-1?

Only if they're careful about their use of non-Unicode columns.
Note that the first insert with latin characters "abcéÓÎ" / "abcÛÊé"
(2 bytes long) works, it's the second insert using Asian characters
(3 bytes long)...

I bet you can store the word "naïve" (two dots over the i) in a CHAR
column in your database, because it's within ISO 8859-1. (The "ï" is
0xEF.) It's not a matter of "Latin" or not; it's a matter of whether or
not the character exists in your single-byte character set. Try something
like a 'T' with '^' over it. See the earlier thread a few weeks ago
wherein it lost (or not) its hat, depending on whether the string was
prefixed with 'N'.
Maybe some words about this could be added to:

http://www.freetds.org/userguide/localization.htm

Good suggestion. I'm working on it. Thanks.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


/* Test GREEK character set
* Author: Sebastien FLAESCH
*
* This sample combines string literal usage with and without N prefix, using
* non-UNICODE/UCS-2 CHAR/VARCHAR and UNICODE/UCS-2 types NCHAR/NVARCHAR.
*
* The ODBC config parameter ClientCharset must be set:
* ClientCharset = GREEK
*
* The database must be created with this collation:
* Greek_BIN
*/

static const char * g_dbname = "freetds_msvtest2_cobra_greek";
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);
}

static void doFetch(SQLHSTMT m_hstmt, int dir, int pos)
{
SQLRETURN rcode = SQLFetchScroll(m_hstmt,dir,pos);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFetchScroll");
if (rcode != SQL_NO_DATA)
fprintf(stdout,">> fetch : %d [%s] [%s]\n",
v_ind_key ? v_key : -1,
v_ind_char ? v_char : "",
v_ind_varchar ? v_varchar : "");
else
fprintf(stdout,">> fetch : no data found\n");
}

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

fprintf(stdout, ">> CONNECT...\n");
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");

/* Table 1 with CHAR/VARCHAR */
fprintf(stdout, ">> CREATE TABLE 1 ...\n");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table tab1", SQL_NTS);
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab1 (k int, c
char(10), vc varchar(10))", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect create table 1");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into tab1 values
(1,'abcÈÉÃÕÐ','abcÈÉÃÕÐ')", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect insert 1.1");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into tab1 values
(2,N'abcÈÉÃÕÐ',N'abcÈÉÃÕÐ')", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect insert 1.2");

/* Table 2 with NCHAR/NVARCHAR */
fprintf(stdout, ">> CREATE TABLE 2 ...\n");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table tab2", SQL_NTS);
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab2 (k int, nc
nchar(10), nvc nvarchar(10))", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect create table 2");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into tab2 values
(1,'abcÈÉÃÕÐ','abcÈÉÃÕÐ')", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect insert 2.1");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into tab2 values
(2,N'abcÈÉÃÕÐ',N'abcÈÉÃÕÐ')", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect insert 2.2");

/* Now insert into table 1 with SQL parameters */
fprintf(stdout, ">> INSERT INTO TABLE 1 ...\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab1 values
(?,?,?)", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare insert 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.1");
SQLBindParameter(m_hstmt1, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 40, 0, v_char, 0, &v_ind_char);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 1.2");
SQLBindParameter(m_hstmt1, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_VARCHAR, 40, 0, v_varchar, 0,
&v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 1.3");

v_key = 3; v_ind_key = 0;
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 1.1 StmtH");

/* Now insert into table 2 with SQL parameters */
fprintf(stdout, ">> INSERT INTO TABLE 2 ...\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab2 values
(?,?,?)", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare insert 2");
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 2.1");
SQLBindParameter(m_hstmt1, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 40, 0, v_char, 0, &v_ind_char);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2.2");
SQLBindParameter(m_hstmt1, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_VARCHAR, 40, 0, v_varchar, 0,
&v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2.3");

v_key = 3; v_ind_key = 0;
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 2.1 StmtH");

/* Fetch rows from table 1 */
fprintf(stdout, "\n>> SELECT 1\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "select * from tab1 order by k",
SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare select");
rcode = SQLBindCol(m_hstmt1, 1, SQL_C_LONG, &v_key, 0, &v_ind_key);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 1.1");
rcode = SQLBindCol(m_hstmt1, 2, SQL_C_CHAR, v_char, sizeof(v_char),
&v_ind_char);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 1.2");
rcode = SQLBindCol(m_hstmt1, 3, SQL_C_CHAR, v_varchar, sizeof(v_varchar),
&v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 1.3");
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute StmtH select 1");
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
rcode = SQLCloseCursor(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCloseCursor StmtH 1");

/* Fetch rows from table 2 */
fprintf(stdout, "\n>> SELECT 2\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "select * from tab2 order by k",
SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare select");
rcode = SQLBindCol(m_hstmt1, 1, SQL_C_LONG, &v_key, 0, &v_ind_key);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 2.1");
rcode = SQLBindCol(m_hstmt1, 2, SQL_C_CHAR, v_char, sizeof(v_char),
&v_ind_char);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 2.2");
rcode = SQLBindCol(m_hstmt1, 3, SQL_C_CHAR, v_varchar, sizeof(v_varchar),
&v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindCol 2.3");
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute StmtH select 2");
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
doFetch(m_hstmt1, SQL_FETCH_NEXT, 0);
rcode = SQLCloseCursor(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCloseCursor StmtH 2");

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