[freetds] RE: :ODBC & FreeTDS: problem binding parameter as SQL_WCHAR or SQL_WVARCHAR

Frediano Ziglio freddyz77 at tin.it
Mon Sep 20 15:15:42 EDT 2004


Il lun, 2004-09-20 alle 11:14, ZIGLIO, Frediano, VF-IT ha scritto:
> > 
> > I need to access MS SQL server from CGI scripts running on Linux box.
> > 
> > Here in Russia we have a pain using 2 different
> > charsets for Cyrillic: CP1251 on Windows and KOI8-R on Unices.
> > So all the data bases on MS SQL server have data in CP1251,
> > but CGI scripts use KOI8-R.
> > 
> > Transferring data from SQL server to CGI scripts is handled in
> > a very simple manner: FreeTDS converts UCS-2LE from SQL server to
> > KOI8-R according to the value of "client charset" in its config file.
> > So all SELECT statements work perfectly.
> > 
> > The problem is with parameterized UPDATE/INSERT statements:
> > when we do something like
> > 
> > $sth->prepare('INSERT INTO MYTABLE (MYFIELD) VALUES (?)');
> > $sth->exec('plain KOI8-R text here');
> > 
> > the parameter for the placeholder is passed through FreeTDS
> > to the SQL server as is (this can be found in a FreeTDS trace file).
> > 
> > FreeTDS documentation says that conversion is performed on data
> > of types nchar, nvarchar and ntext (Unicode types in MS SQL).
> > So I've changed types of the string fields in my database tables
> > to "n...", but the parameter values continued to pass unconverted.
> > 
> > Then I've decided to force DBI (or whatever?) treat my parameter as
> > being destined to Unicode column:
> > 
> > $sth->prepare(...);
> > my $param = 'plain KOI8-R text string';
> > $sth->bind_param(1, $param, { TYPE=>SQL_WVARCHAR });
> > $sth->execute;
> > 
> > But this fails in DBD::ODBC while trying to perform "execute"
> > (please, see the log recorded using DBI_TRACE=2 in the attach).
> > 
> > I'm new to DBI so I don't know at which level this error is actually
> > occur. Or may be I just don't understand how one should handle such
> > a case with different charsets?
> > 
> > P.S.
> > 1. For now we use Text::Iconv to explicitly convert each parameter
> > to the SQL server encoding.
> > 2. Making SQL statements with hard-coded parameters is no way: we need
> > them prepared and executed multiple times with different parameters.
> > 
> > 
> 
> Well, I'm a FreeTDS developer. It seems that FreeTDS libraries do not
> set character conversion informations sending parameters so core libTDS
> do not convert but just send bytes as they are. This problem reside in
> both 0.62 and 0.63 (developement) trunks. I'll fix ASAP. In the meantime
> what FreeTDS version are you using?
> 

Here you are a patch for 0.63 that should solve your problem

freddy77

-------------- next part --------------
A non-text attachment was scrubbed...
Name: paramconv.diff.gz
Type: application/x-gzip
Size: 449 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20040920/153699f0/attachment.gz 


More information about the FreeTDS mailing list