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