Re: UTF-8 support: What about N'xxx' string literals?
Sebastien FLAESCH <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Organization | Four J's Development Tools |
| Message-ID | <[email protected]> |
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
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
odbctest11-greek.c
(text/plain, 10 KB)
/* 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");
}