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( '<d>Käse</d>' AS XML));
> >
> > Käse Käse <d>Kä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
