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, 01 Sep 2008 16:54:24 +0200

Frediano,

While testing ParameterType = SQL_WVARCHAR, I faced a problem with
ColumnSize...

According to MSDN, ColumnSize defines the precision of the type in
*characters* (not bytes):

http://msdn.microsoft.com/en-us/library/ms711786(VS.85).aspx

With attached example, when using a CHAR(2) column, I just bind with
ParameterType=SQL_WVARCHAR and ColumnSize=2 ...

But I get an error:

SQL State: 42000
SQL code : 8016
Message : [FreeTDS][SQL Server]The incoming tabular data stream (TDS) remote
procedure call (RPC) protocol stream is incorrect.
Parameter 3 (""): Data type 0xE7 has an invalid data length or
metadata length.

Please could you have a look at this?

Note in my SQL Native Client driver, I do also use SQL_WVARCHAR and
ColumnSize has to be passed as a number of characters.


Thanks!
Seb


Frediano Ziglio wrote:
Il giorno gio, 14/08/2008 alle 11.53 -0400, James K. Lowden ha scritto:
Hi Sebastien,
James K. Lowden wrote:
"Wide" characters have a fixed width, usually 16 bits. UTF-8 has a
variable width and for the most part can be treated like ASCII
(strlen(3) et al.). Declaring UTF-8 as SQL_C_WCHAR incorrectly tells
the driver there's a character in every two bytes.
Oh take care, actually there are 2 type specifications in
SQLBindParameter:

SQLRETURN SQLBindParameter(
SQLHSTMT StatementHandle,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ValueType, -- This is the C type
SQLSMALLINT ParameterType, -- This is the SQL equivalent
SQLULEN ColumnSize,
SQLSMALLINT DecimalDigits,
SQLPOINTER ParameterValuePtr,
SQLINTEGER BufferLength,
SQLLEN * StrLen_or_IndPtr);

For sure, when using (char *) / UTF-8, ValueType *must* be SQL_C_CHAR,
not SQL_C_WCHAR... (that one is for wchar_t).

I was talking about ParameterType...

Should it be SQL_W[VAR]CHAR or SQL_[VAR]CHAR when binding UTF-8?
Oh, I see. Your question surprises me, then, because IIUC the client-side
encoding doesn't matter for ParameterType. ParameterType describes the
data type of the parameter as used in the query, not as represented in the
client buffer. (I"m just an ODBC student. Correct me if I'm wrong.)

ParameterType specify the type to use with the server so if SQL_VARCHAR
is used a VARCHAR is sent to the server while using SQL_WVARCHAR an
NVARCHAR is used. This cause the problem with japanese encoding,
SQL_VARCHAR is not able to "contain" japanese so the error. Some
updates:
- I split my working patch for wide support committing support for
server N(VAR)CHAR, very short and seems to work correctly with UTF-8
(the other part is to support *W functions and SQL_C_WCHAR... and it's a
very very long work)
- I ported Sebastian tests in a new src/odbc/unittests/utf8.c test, it
works correctly and it tests data inserted too. I used a trick to force
client encoding (using SQLDriverConnect instead of SQLConnect)
- I wrote a workaround for invalid TDS protocol if a conversion error
happen. Now libTDS send an empty string... not correct but at least you
get an error from library and you can continue to issue queries. The
patch was backported to 0.82 too. I'll write a definitive fix for this
(it require to handle correctly errors and cancel sending query).

For instance, any of these pairs works:

ValueType ParameterType ------------- -------------
SQL_C_CHAR SQL_DECIMAL
SQL_C_CHAR SQL_VARCHAR
SQL_C_DOUBLE SQL_FLOAT

For character data parameters, the choice of SQL_W[VAR]CHAR or
SQL_[VAR]CHAR is governed by the data type of the parameter as understood
by the server. For stored procedures, the parameter type is obvious: it's
declared within the procedure text. For placeholders in open SQL text, I
would say the type is always SQL_[VAR]CHAR.

No, the client encodes the parameter type as specified by the
application. The server converts the parameter as needed.

That is, if your query is,
CREATE TABLE t (name nvarchar(30))
INSERT t values( ? )

You would use SQL_[VAR]CHAR.
Now, what to do when what you really want is equivalent to "INSERT t
values( N'string' )"? I assume it's still SQL_[VAR]CHAR, but I won't know
for sure until either Frediano explains or I figure out how sp_execute
works.

sp_execute is a special store procedure witch is embedded in sql server
and support variable parameters... let's say it's like a printf instead
of a strcpy. You specify parameters you pass followed by parameters and
server execute the query.

One thing you could try is SQLDescribeParam with various combinations
using Microsoft's driver. We would follow whatever it says/expects.

SQLDescribeParam is a workaround on mssql... it parse the query to get
parameters type. Obviously on complicated query it isn't able to tell
the truth... Assuming query is "SELECT * FROM table WHERE x = ?" it
build a "SET FMTONLY ON SELECT x FROM table" and get x type... this is
the reason is still unsupported by our driver... nobody wants to write
the sql parser :)

bye
Frediano


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


/* Testing SQL_WVARCHAR and ColumnSize parameter of SQLBindParameter().
* Author: Sebastien FLAESCH
*
* This is a simple ASCII sample, using ColumnSize = 2 (characters).
*
* With freetds-0.83.dev.20080901, we get this error:
*
* SQL State: 42000
* SQL code : 8016
* Message : [FreeTDS][SQL Server]The incoming tabular data stream (TDS)
* remote procedure call (RPC) protocol stream is incorrect.
* Parameter 3 (""):
* Data type 0xE7 has an invalid data length or metadata length.
*
*/

static const char * g_dbname = "freetds_msvtest1_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_varchar[50];
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 tab1", SQL_NTS);
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab1 (k int, c
varchar(2))", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.1");

rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab1 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");

SQLBindParameter(m_hstmt1,
/* ParameterNumber */ 2,
/* InputOutputType */ SQL_PARAM_INPUT,
/* ValueType */ SQL_C_CHAR,
/* ParameterType */ SQL_WVARCHAR,
/* ColumnSize */ 2, /* Characters! */
/* DecimalDigits */ 0,
/* ParameterValuePtr */ v_varchar,
/* BufferLength */ sizeof(v_varchar),
/* StrLen_or_IndPtr */ &v_ind_varchar);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 2");

v_key = 1;
strcpy(v_varchar, "ab");
v_ind_varchar = strlen(v_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