On 30/09/2010 20:22, Ludwig, Michael wrote:
Please keep me up to date with how you get on Michael.
You can always find me on #dbi irc on magnet (not withstanding time
differences).
Thanks for your support, Martin.
It seems that there are some actions done in a trigger that
some component in the Perl-MSSQL chain doesn't like. For
example, consider this block, which you can just run in
SSMS as is:
declare @x xml, @xmax xml, @i INT;
print 'a-2';
set @i = cast( '1' as int);
print 'a-1';
set @x = cast('<bla>
<dt>2010-09-30T04:45:39-05:00</dt>
<dt>2010-09-30T04:35:39-05:00</dt>
<dt>2010-09-30T01:45:39-05:00</dt>
</bla>' as xml);
print 'a'; -- still prints when called from Perl
set @xmax =
@x.query( 'max( for $d in /bla/dt return xs:dateTime($d) )');
print 'b'; -- doesn't print any more when called from Perl
print cast(@xmax as nvarchar(50)); -- not either
Messages in SSMS:
a-2
a-1
a
b
2010-09-30T09:45:39Z
Same result when put in a trigger and an insert causing
the trigger to fire is done.
But not when the INSERT is done from Perl. In that case
the "a" still appears alright, but not the "b" nor the
following max value. And the INSERT is not performed
either. An abort seems to take place.
Michael
Once you start calling procedures from Perl via DBD::ODBC which use
print you need to loop over SQLMoreResults
(http://search.cpan.org/~mjevans/DBD-ODBC-1.25/ODBC.pm#odbc_more_results).
Procedures can provide multiple result-sets/prints and odbc_more_results
moves from one to the next until there is no more. If you don't call
odbc_more_results the procedure does not finish and output parameters
are not available until the procedure finishes.
Take a look at odbc_more_results and the examples in the t/ subdir of
DBD::ODBC for odbc_more_results.
I think this will correct the differences you are seeing.
Martin
