RE: DBD::OCBC support for XML datatype in SQL Server 2005
Thanks to Perl Monks for identifying the length() problem and to Tim for pointing me at other areas where it could be a problem. And thanks to you, Martin! Michael
Re: DBD::OCBC support for XML datatype in SQL Server 2005
Michael has confirmed that DBD::ODBC 1.24_6 fixes the problem he saw with SQL Server XMLType columns. 1.24_6 defaults binding of XMLType parameters as SQL_WCHAR on Unicode enabled DBD::ODBCs now and the length() issue (which turned out to be an error in DBD::ODBC ignoring magic) is also fixed for unicode strings. There may be other magic issues which I cannot confirm as yet as I've not had a chance to work through the code to examine all instances of changing a scalar DBD::ODBC did not create. Thanks to Perl Monks for identifying the length() problem and to Tim for pointing me at other areas where it could be a problem. Martin On 15/09/2010 21:47, Michael Ludwig wrote: Martin J. Evans schrieb am 15.09.2010 um 21:27 (+0100): DBD::ODBC 1.24_5 uploaded to CPAN now but your mirror might take longer for you to see. Not there yet. I would not get too excited as I did not try the native client yet and don't have it at home so it will have to wait until tomorrow. Comes free with SS 2008 Express. Which is what I have at home. :-) It might be less bother for you to wait until I've tried native client (tomorrow hopefully). Hehe. Just noticing the CPAN mirror picker dropdown menu features broken Unicode for places in Finland, France, Germany etc. http://search.cpan.org/mirror
Re: DBD::OCBC support for XML datatype in SQL Server 2005
Michael,
When reporting issues with DBD::ODBC it is useful to know the operating system
name and version and the ODBC driver and version as this can have a big effect.
On 15/09/10 01:18, Michael Ludwig wrote:
I'm facing encoding issues in trying to make use of the XML datatype in
SQL Server 2005, which I'm accessing using DBD::ODBC 1.23 and Perl 5.12.
CREATE TABLE T2 (a VARCHAR(99), u NVARCHAR(99), x XML);
Three columns here, a for single-byte characters, u for Unicode, and
x for XML.
The XML type is not known to DBD::ODBC as a unicode type. You can override this
by adding a TYPE attribute of value -8 (sorry there is no symbol for SQL_WCHAR
in DBI at present - that might be worth RTing in itself) to the end of the
bind_param call - see later for my example. There is an argument that the XML
type should be known as a unicode type in DBD::ODBC and I'd consider that if
you care to RT it.
The following statements works correctly in SSMS (SS Management Studio):
INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( 'dKäse/d' AS XML));
KäseKäsedKäse/d
Now German wasn't too difficult, so let's try some Russian.
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST('rМосква/r' AS XML));
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST(N'rМосква/r' AS XML));
?? Москва r??/r
?? Москва rМосква/r
I think you would have found the XML column was incorrectly inserted if you'd
viewed this in SQL Server Enterprise manager - I did.
We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).
Better to use bind_param and set the type than try and set the type in the SQL.
How can we handle this situation from Perl? Here's a script.
\,,,/
(o o)
--oOOo-(_)-oOOo--
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;
my $txt_de = 'Käse';
my $txt_ru = 'Москва';
binmode STDOUT, ':utf8';
my @dsn = qw/DBI:ODBC:MY_DB my_username my_password/;
my %opt = (PrintError = 0, RaiseError = 1, AutoCommit = 1);
my $dbh = DBI-connect( @dsn, \%opt );
$dbh-{LongReadLen} = 4000;
$dbh-{LongTruncOk} = 1; # Dies muß, ob logisch oder nicht.
my $sth_ins = $dbh-prepare(
'INSERT INTO T2 (a, u, x) VALUES (?, ?, CAST( ? AS XML) )' );
$sth_ins-execute( $txt_de, $txt_de, d$txt_de/d );
$sth_ins-execute( $txt_ru, $txt_ru, r$txt_ru/r );
my $sth_sel = $dbh-prepare( 'SELECT u, x FROM T2' );
$sth_sel-execute;
$sth_sel-bind_columns( \my( $txt, $xml ) );
my $i = 0;
while ( $sth_sel-fetch ) {
printf %3u %3u [%s] [%s]\n, ++$i, length($txt), $txt, $xml;
}
$dbh-disconnect;
-
The problem is the INSERT statement, more specifically, in the XML part.
I can't seem to get it to accept my Unicode strings as Unicode for that
column. Instead, they're treated as octets, resulting in garbage.
Käse KäsedKäse/d
МоÑква Москва rМоÑква/r
I've tried to use an N introducer with the XML column, but that leads to
errors.
CAST( N? AS XML) - Invalid column name 'n...@p3'. (SQL-42S22)
CAST( N ? AS XML) - Incorrect syntax near '@P3'. (SQL-42000)
I'd appreciate your advice.
There is good news and some not so good news (unless you can build DBD::ODBC
for your platform).
To fix the insert bind the parameter as SQL_WCHAR (-8).
However, the select cannot be fixed with the same trick as DBD::ODBC does not
currently act on the TYPE attribute to bind_col. I've fixed that and can send
you a new DBD::ODBC but you'll have to build it yourself (which is easy if you
are UNIX or using Strawberry Perl and only slightly harder if you are using
ActiveState on Windows assuming it is a recentish version).
By all means RT a request to default XML columns to unicode and that will mean
you don't have to specify the TYPE in bind_param.
Below is my example which may also indicate another subtle error with length()
but I've not had time to look into it yet.
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;
use bytes;
no bytes;
my $txt_de = 'Käse';
my $txt_ru = 'Москва';
binmode STDOUT, ':utf8';
my @dsn = qw/DBI:ODBC:xxx xx xx/;
my %opt = (PrintError = 0, RaiseError = 1, AutoCommit = 1, ChopBlanks = 1);
my $dbh = DBI-connect( @dsn, \%opt );
$dbh-{LongReadLen} = 4000;
$dbh-{LongTruncOk} = 1; # Dies muß, ob logisch oder nicht.
my $sth_ins = $dbh-prepare(
'INSERT INTO T2 (a, u, x) VALUES (?, ?, CAST( ? AS XML) )' );
foreach my $row ([$txt_de, $txt_de, d$txt_de/d],
[$txt_ru, $txt_ru, r$txt_ru/r]) {
$sth_ins-bind_param(1, $row-[0]);
$sth_ins-bind_param(2, $row-[1]); # defaults to SQL_WCHAR
$sth_ins-bind_param(3, $row-[2], {TYPE = -8});
$sth_ins-execute;
}
#$sth_ins-execute( $txt_de, $txt_de, d$txt_de/d );
#$sth_ins-execute( $txt_ru, $txt_ru, r$txt_ru/r );
my $sth_sel =
Re: DBD::OCBC support for XML datatype in SQL Server 2005
I've had some time to look at this more and would like to correct a few things.
Firstly, DBI DOES define SQL_WCHAR so if you pull in :sql_types you can used
SQL_WCHAR instead of using -8.
Also, the mystery over the length() call producing the wrong result was a
missing call to SvSETMAGIC (big thanks to Perl Monks and in particular
ikegami). You can find the node at http://www.perlmonks.org/?node_id=860211,
but some of the discussion took place in the chatterbox. I will fix this for
the next release.
So, Michael, you really need to make a few changes to your script and then you
need a new DBD::ODBC which I can send you. Let me know.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
On 15/09/10 11:12, Martin J. Evans wrote:
Michael,
When reporting issues with DBD::ODBC it is useful to know the operating
system name and version and the ODBC driver and version as this can have a
big effect.
On 15/09/10 01:18, Michael Ludwig wrote:
I'm facing encoding issues in trying to make use of the XML datatype in
SQL Server 2005, which I'm accessing using DBD::ODBC 1.23 and Perl 5.12.
CREATE TABLE T2 (a VARCHAR(99), u NVARCHAR(99), x XML);
Three columns here, a for single-byte characters, u for Unicode, and
x for XML.
The XML type is not known to DBD::ODBC as a unicode type. You can override
this by adding a TYPE attribute of value -8 (sorry there is no symbol for
SQL_WCHAR in DBI at present - that might be worth RTing in itself) to the end
of the bind_param call - see later for my example. There is an argument that
the XML type should be known as a unicode type in DBD::ODBC and I'd consider
that if you care to RT it.
The following statements works correctly in SSMS (SS Management Studio):
INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( 'dKäse/d' AS XML));
KäseKäsedKäse/d
Now German wasn't too difficult, so let's try some Russian.
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST('rМосква/r' AS XML));
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST(N'rМосква/r' AS XML));
?? Москва r??/r
?? Москва rМосква/r
I think you would have found the XML column was incorrectly inserted if you'd
viewed this in SQL Server Enterprise manager - I did.
We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).
Better to use bind_param and set the type than try and set the type in the
SQL.
How can we handle this situation from Perl? Here's a script.
\,,,/
(o o)
--oOOo-(_)-oOOo--
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;
my $txt_de = 'Käse';
my $txt_ru = 'Москва';
binmode STDOUT, ':utf8';
my @dsn = qw/DBI:ODBC:MY_DB my_username my_password/;
my %opt = (PrintError = 0, RaiseError = 1, AutoCommit = 1);
my $dbh = DBI-connect( @dsn, \%opt );
$dbh-{LongReadLen} = 4000;
$dbh-{LongTruncOk} = 1; # Dies muß, ob logisch oder nicht.
my $sth_ins = $dbh-prepare(
'INSERT INTO T2 (a, u, x) VALUES (?, ?, CAST( ? AS XML) )' );
$sth_ins-execute( $txt_de, $txt_de, d$txt_de/d );
$sth_ins-execute( $txt_ru, $txt_ru, r$txt_ru/r );
my $sth_sel = $dbh-prepare( 'SELECT u, x FROM T2' );
$sth_sel-execute;
$sth_sel-bind_columns( \my( $txt, $xml ) );
my $i = 0;
while ( $sth_sel-fetch ) {
printf %3u %3u [%s] [%s]\n, ++$i, length($txt), $txt, $xml;
}
$dbh-disconnect;
-
The problem is the INSERT statement, more specifically, in the XML part.
I can't seem to get it to accept my Unicode strings as Unicode for that
column. Instead, they're treated as octets, resulting in garbage.
Käse KäsedKäse/d
МоÑква Москва rМоÑква/r
I've tried to use an N introducer with the XML column, but that leads to
errors.
CAST( N? AS XML) - Invalid column name 'n...@p3'. (SQL-42S22)
CAST( N ? AS XML) - Incorrect syntax near '@P3'. (SQL-42000)
I'd appreciate your advice.
There is good news and some not so good news (unless you can build DBD::ODBC
for your platform).
To fix the insert bind the parameter as SQL_WCHAR (-8).
However, the select cannot be fixed with the same trick as DBD::ODBC does not
currently act on the TYPE attribute to bind_col. I've fixed that and can send
you a new DBD::ODBC but you'll have to build it yourself (which is easy if
you are UNIX or using Strawberry Perl and only slightly harder if you are
using ActiveState on Windows assuming it is a recentish version).
By all means RT a request to default XML columns to unicode and that will
mean you don't have to specify the TYPE in bind_param.
Below is my example which may also indicate another subtle error with
length() but I've not had time to look into it yet.
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;
use bytes;
no bytes;
my
Re: DBD::OCBC support for XML datatype in SQL Server 2005
Martin,
Martin J. Evans schrieb am 15.09.2010 um 11:12 (+0100):
Michael,
When reporting issues with DBD::ODBC it is useful to know the
operating system name and version and the ODBC driver and version as
this can have a big effect.
This is on Windows XP Home SP3 using SQL Server Native Client 10.0.
I was incorrect in stating that the server was SS 2005; it is SS 2008.
Perl 5.12 and DBD::ODBC 1.23 are correct, though.
The XML type is not known to DBD::ODBC as a unicode type.
Thanks to your investigation, we know by now that SQL_WCHAR is made
available by the DBI module when importing :sql_types.
The following statements works correctly in SSMS (SS Management Studio):
INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( 'dKäse/d' AS XML));
KäseKäsedKäse/d
Now German wasn't too difficult, so let's try some Russian.
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST('rМосква/r' AS XML));
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST(N'rМосква/r' AS XML));
?? Москва r??/r
?? Москва rМосква/r
I think you would have found the XML column was incorrectly inserted
if you'd viewed this in SQL Server Enterprise manager - I did.
I don't have that program, so I don't know. The XML column needs the N
introducer, than Greek and Russian look fine. Without the N introducer,
Russian is all question marks; as for Greek, *some* characters - alpha,
beta, sigma, tau - are ineptly translated to their latin cousins.
We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).
Better to use bind_param and set the type than try and set the type in
the SQL.
Sounds promising!
There is good news and some not so good news (unless you can build
DBD::ODBC for your platform).
To fix the insert bind the parameter as SQL_WCHAR (-8).
$sth-bind_param( 1, $txt );
$sth-bind_param( 2, $txt );
$sth-bind_param( 3, u$txt/u, {TYPE = DBI::SQL_WCHAR} );
Gives me an error with 1.23, which has been reported here:
Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
http://www.martin-evans.me.uk/node/39
I'll look into upgrading.
However, the select cannot be fixed with the same trick as DBD::ODBC
does not currently act on the TYPE attribute to bind_col. I've fixed
that and can send you a new DBD::ODBC but you'll have to build it
yourself (which is easy if you are UNIX or using Strawberry Perl and
only slightly harder if you are using ActiveState on Windows assuming
it is a recentish version).
Thanks. ActivePerl 5.12!
By all means RT a request to default XML columns to unicode and that
will mean you don't have to specify the TYPE in bind_param.
https://rt.cpan.org/Public/Bug/Display.html?id=61370
Below is my example which may also indicate another subtle error with
length() but I've not had time to look into it yet.
Yes, I noted there was an inaccuracy - but it's not the main trouble
right now. :-)
To resume:
* INSERT still not working because of SQL-HY104 error
* SELECT will need patch
Thanks a lot for your prompt and encouraging help with this issue!
--
Michael Ludwig
Re: DBD::OCBC support for XML datatype in SQL Server 2005
Martin J. Evans schrieb am 15.09.2010 um 17:55 (+0100): Firstly, DBI DOES define SQL_WCHAR so if you pull in :sql_types you can used SQL_WCHAR instead of using -8. Yes. Also, the mystery over the length() call producing the wrong result was a missing call to SvSETMAGIC (big thanks to Perl Monks and in particular ikegami). You can find the node at http://www.perlmonks.org/?node_id=860211, but some of the discussion took place in the chatterbox. I will fix this for the next release. Thanks. Internals is something I don't grok yet. But thanks for your investigative work! So, Michael, you really need to make a few changes to your script and then you need a new DBD::ODBC which I can send you. Let me know. As stated: * INSERT will need guidance or upgrade (see my other reply) * SELECT will need a patch from you -- Michael Ludwig
Re: DBD::OCBC support for XML datatype in SQL Server 2005
On 15/09/2010 21:11, Michael Ludwig wrote:
Martin,
Martin J. Evans schrieb am 15.09.2010 um 11:12 (+0100):
Michael,
When reporting issues with DBD::ODBC it is useful to know the
operating system name and version and the ODBC driver and version as
this can have a big effect.
This is on Windows XP Home SP3 using SQL Server Native Client 10.0.
I was incorrect in stating that the server was SS 2005; it is SS 2008.
Perl 5.12 and DBD::ODBC 1.23 are correct, though.
Thanks, that helps as I did not try native client.
The XML type is not known to DBD::ODBC as a unicode type.
Thanks to your investigation, we know by now that SQL_WCHAR is made
available by the DBI module when importing :sql_types.
The following statements works correctly in SSMS (SS Management Studio):
INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( 'dKäse/d' AS XML));
KäseKäsedKäse/d
Now German wasn't too difficult, so let's try some Russian.
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST('rМосква/r' AS XML));
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST(N'rМосква/r' AS XML));
?? Москваr??/r
?? МоскваrМосква/r
I think you would have found the XML column was incorrectly inserted
if you'd viewed this in SQL Server Enterprise manager - I did.
I don't have that program, so I don't know. The XML column needs the N
introducer, than Greek and Russian look fine. Without the N introducer,
Russian is all question marks; as for Greek, *some* characters - alpha,
beta, sigma, tau - are ineptly translated to their latin cousins.
We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).
Better to use bind_param and set the type than try and set the type in
the SQL.
Sounds promising!
There is good news and some not so good news (unless you can build
DBD::ODBC for your platform).
To fix the insert bind the parameter as SQL_WCHAR (-8).
$sth-bind_param( 1, $txt );
$sth-bind_param( 2, $txt );
$sth-bind_param( 3, u$txt/u, {TYPE = DBI::SQL_WCHAR} );
Gives me an error with 1.23, which has been reported here:
Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
http://www.martin-evans.me.uk/node/39
I'll look into upgrading.
I will upload DBD::ODBC 1.24_5 in the next 10 minutes but it may be a
while before you can see it on CPAN.
Read README.windows for how to build on activestate.
However, the select cannot be fixed with the same trick as DBD::ODBC
does not currently act on the TYPE attribute to bind_col. I've fixed
that and can send you a new DBD::ODBC but you'll have to build it
yourself (which is easy if you are UNIX or using Strawberry Perl and
only slightly harder if you are using ActiveState on Windows assuming
it is a recentish version).
Thanks. ActivePerl 5.12!
By all means RT a request to default XML columns to unicode and that
will mean you don't have to specify the TYPE in bind_param.
https://rt.cpan.org/Public/Bug/Display.html?id=61370
Cheers - will look into it as soon as I can find time.
Below is my example which may also indicate another subtle error with
length() but I've not had time to look into it yet.
Yes, I noted there was an inaccuracy - but it's not the main trouble
right now. :-)
See my later posting - it is fixed now.
To resume:
* INSERT still not working because of SQL-HY104 error
* SELECT will need patch
Thanks a lot for your prompt and encouraging help with this issue!
No problems. Please try 1.24_5 when it arrives on CPAN and keep me
informed. In the mean time I will try the native client driver.
Martin
Re: DBD::OCBC support for XML datatype in SQL Server 2005
Martin J. Evans schrieb am 15.09.2010 um 21:27 (+0100): DBD::ODBC 1.24_5 uploaded to CPAN now but your mirror might take longer for you to see. Not there yet. I would not get too excited as I did not try the native client yet and don't have it at home so it will have to wait until tomorrow. Comes free with SS 2008 Express. Which is what I have at home. :-) It might be less bother for you to wait until I've tried native client (tomorrow hopefully). Hehe. Just noticing the CPAN mirror picker dropdown menu features broken Unicode for places in Finland, France, Germany etc. http://search.cpan.org/mirror -- Michael Ludwig
DBD::OCBC support for XML datatype in SQL Server 2005
I'm facing encoding issues in trying to make use of the XML datatype in
SQL Server 2005, which I'm accessing using DBD::ODBC 1.23 and Perl 5.12.
CREATE TABLE T2 (a VARCHAR(99), u NVARCHAR(99), x XML);
Three columns here, a for single-byte characters, u for Unicode, and
x for XML.
The following statements works correctly in SSMS (SS Management Studio):
INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( 'dKäse/d' AS XML));
KäseKäsedKäse/d
Now German wasn't too difficult, so let's try some Russian.
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST('rМосква/r' AS XML));
INSERT INTO T2 VALUES
('Москва', N'Москва', CAST(N'rМосква/r' AS XML));
?? Москва r??/r
?? Москва rМосква/r
We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).
How can we handle this situation from Perl? Here's a script.
\,,,/
(o o)
--oOOo-(_)-oOOo--
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;
my $txt_de = 'Käse';
my $txt_ru = 'Москва';
binmode STDOUT, ':utf8';
my @dsn = qw/DBI:ODBC:MY_DB my_username my_password/;
my %opt = (PrintError = 0, RaiseError = 1, AutoCommit = 1);
my $dbh = DBI-connect( @dsn, \%opt );
$dbh-{LongReadLen} = 4000;
$dbh-{LongTruncOk} = 1; # Dies muß, ob logisch oder nicht.
my $sth_ins = $dbh-prepare(
'INSERT INTO T2 (a, u, x) VALUES (?, ?, CAST( ? AS XML) )' );
$sth_ins-execute( $txt_de, $txt_de, d$txt_de/d );
$sth_ins-execute( $txt_ru, $txt_ru, r$txt_ru/r );
my $sth_sel = $dbh-prepare( 'SELECT u, x FROM T2' );
$sth_sel-execute;
$sth_sel-bind_columns( \my( $txt, $xml ) );
my $i = 0;
while ( $sth_sel-fetch ) {
printf %3u %3u [%s] [%s]\n, ++$i, length($txt), $txt, $xml;
}
$dbh-disconnect;
-
The problem is the INSERT statement, more specifically, in the XML part.
I can't seem to get it to accept my Unicode strings as Unicode for that
column. Instead, they're treated as octets, resulting in garbage.
Käse KäsedKäse/d
МоÑква Москва rМоÑква/r
I've tried to use an N introducer with the XML column, but that leads to
errors.
CAST( N? AS XML) - Invalid column name 'n...@p3'. (SQL-42S22)
CAST( N ? AS XML) - Incorrect syntax near '@P3'. (SQL-42000)
I'd appreciate your advice.
--
Michael Ludwig
