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