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.
-> DBI->connect(dbi:ODBC:SQL, vip, ****, HASH(0x814cbb8))
-> DBI->install_driver(ODBC) for linux perl=5.008004 pid=8763 ruid=1000 euid=1000
install_driver: DBD::ODBC version 1.10 loaded from
/usr/local/lib/perl/5.8.4/DBD/ODBC.pm
<- install_driver= DBI::dr=HASH(0x823d3b4)
-> connect for DBD::ODBC::dr (DBI::dr=HASH(0x823d3b4)~0x82fc208 'SQL' 'vip' ****
HASH(0x82fd120)) thr#814bf18
SQLConnect 'SQL', 'vip'
<- connect= DBI::db=HASH(0x82fc220) at DBI.pm line 591
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x82fd150)~INNER 'PrintError' 1)
thr#814bf18
DBD::ODBC unsupported attribute passed (PrintError)
<- STORE= 1 at DBI.pm line 635
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x82fd150)~INNER 'AutoCommit' 1)
thr#814bf18
<- STORE= 1 at DBI.pm line 635
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x82fd150)~INNER 'Username' 'vip')
thr#814bf18
DBD::ODBC unsupported attribute passed (Username)
<- STORE= 1 at DBI.pm line 638
<- FETCH= 'vip' ('Username' from cache) at DBI.pm line 638
<- connect= DBI::db=HASH(0x82fc220)
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x82fd150)~INNER 'dbi_connect_closure'
CODE(0x82bda80)) thr#814bf18
DBD::ODBC unsupported attribute passed (dbi_connect_closure)
<- STORE= 1 at DBI.pm line 652
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x82fc220)~0x82fd150 'use CurrentDB
insert into VipClients
(Famile)
values
(?)
') thr#814bf18
dbd_preparse scanned 1 distinct placeholders
SQLPrepare returned 0
dbd_st_prepare'd sql f137569416, ExecDirect=0
use CurrentDB
insert into VipClients
(Family)
values
(?)
<- prepare= DBI::st=HASH(0x82fd240) at mssql.pl line 32
-> bind_param for DBD::ODBC::st (DBI::st=HASH(0x82fd240)~0x8161020 1 'ч╔╘╠┼╥'
HASH(0x82fd234)) thr#814bf18
bind 1 <== 'ч╔╘╠┼╥' (attribs: HASH(0x82fd234)), type -9
<- bind_param= 1 at mssql.pl line 47
-> execute for DBD::ODBC::st (DBI::st=HASH(0x82fd240)~0x8161020) thr#814bf18
bind 1 <== 'ч╔╘╠┼╥' (size 6/7/0, ptype 4, otype 1, sqltype -9)
bind 1: CTy=1, STy=UNICODE VARCHAR, CD=6, Sc=6, VM=6.
dbd_st_execute (for hstmt 137569416 before)...
st_execute/SQLExecute error -1 recorded: (DBD: st_execute/SQLExecute err=-1)
!! ERROR: -1 '(DBD: st_execute/SQLExecute err=-1)'
<- execute= undef at mssql.pl line 48
DBD::ODBC::st execute failed: (DBD: st_execute/SQLExecute err=-1) at ./mssql.pl line
48.
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x8161020)~INNER) thr#814bf18
ERROR: -1 '(DBD: st_execute/SQLExecute err=-1)'
<- DESTROY= undef
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x82fd150)~INNER) thr#814bf18
ERROR: -1 '(DBD: st_execute/SQLExecute err=-1)'
<- DESTROY= undef
-- DBI::END
-> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x823d3b4)~0x82fc208) thr#814bf18
<- disconnect_all= '' at DBI.pm line 661
! -> DESTROY for DBD::ODBC::dr (DBI::dr=HASH(0x82fc208)~INNER) thr#814bf18
! <- DESTROY= (not implemented) during global destruction
