FreeTDS Development Group

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

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

}