Re: MSSQL: INSERT into table with TRIGGER

  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