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( '<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.
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
