gaggio has asked for the wisdom of the Perl Monks concerning the following question:

Dear brothers in Perl, I have now tried to tackle this problem for quite a while by myself without any success - time to request some of your wisdom!

I am on a Windows box, trying to access through ODBC / Perl DBI/DBD a database that has a table containing a column having the "XML" data type, and I just can't figure out a way to access this data in a bare select query: as soon as my query includes the XML-typed column, the query does not return any row.

The XML-typed column's name is "XmlResults".

The funny thing is 2) returns the expected rows perfectly well when I run the query through my database querying IDE (SqlDbx), connecting to SQL Server the exact same way (using the same ODBC DSN).

Note that no specific error is thrown - it's just that I get 0 row returned.

I have tried using other accessors than the CONVERT statement, XQuery for example, like

SELECT XmlResults.value('/whatever[1]','varchar(50)'), etc.

but I get the same behaviour: works from the IDE, but not from my Perl code :(

My code is quite standard:

my $sth = $dbh->prepare($sql);
my @row = $sth->fetchrow_array;

[download]

I have tried using $sth->fetchall_arrayref rather than $sth->fetchrow_array but it made no difference.

I have the latest CPAN modules installed.

What would you do to resolve my issue? Any hint/advice on how to analyse the issue further?

Thanks!

Replies are listed 'Best First'.
Re: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns
by mje (Curate) on Jul 21, 2010 at 08:08 UTC

    Reduce code to a simple connect and select. Enable DBI tracing by setting and exporting the DBI_TRACE environment variable to something like 15=x.log. Run your code and post the log file output.

    export DBI_TRACE=15=x.log

    [download]
    or 
    set DBI_TRACE=15=x.log

    [download]
[reply]
[d/l]
[select]

      Thank you for the DBI_TRACE tip.

      After having enabled tracing, I easily found what the problem was: the default LongReadLen of 80 was not sufficient to grab the full XML strings, resulting in the abortion of the query.

      Solved!!!

[reply]

        Glad you are working although I might have pointed at LongReadLen myself if you hadn't given the impression you were getting no data back at all for that column.

[reply]
Re: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns
by mje (Curate) on Jul 22, 2010 at 14:52 UTC

    I looked into this and I cannot reproduce on a simple xml column with this:

    create table mje (a int primary key, b xml);
my $data = '<xx>' . ('z' x 500) . '</xx>';
insert into mje values (1, $data);
select * from mje

    [download]

    I always get data truncation errors whether I use the MS ODBC driver or the MS native client driver. However, DBD::ODBC is not doing the right thing if the driver reports the column type as -152 (XML) so I've rt'ed it at Does not handle MS SQL Server xml and hope to fix it in the next few days.

    I doubt the change will make any difference to you since I cannot reproduce your problem. If you get a chance to confirm the things I asked elsewhere in this node I'll look into it further.

[reply]
[d/l]

      OK, I have to admit that I wasn't exhaustive in my explanations, and that there was indeed a data truncation error being thrown, except I did not see them. The reason is I was developing my code in a CGI script that wasn't capturing stderr...

      After I ran the script on the command line I could see the truncation error.

      Thanks mje for following up with the bug report on CPAN!

[reply]

        No problems and that rt should be fixed later today and hopefully I'll have time for a new development release.

[reply]

Back to Seekers of Perl Wisdom