[unixODBC-support] Stored procedure messages returned in reverse cron order from DBD::ODBC mssql/EasySoft

Martin J. Evans bohica at ntlworld.com
Wed Jul 27 19:33:57 BST 2011


On 26/07/2011 23:24, eric.berg at barclayscapital.com wrote:
> Hey, Martin.  We've put together a test that is reliably reproducing
> the problem.
>
> I've been able to reproduce this using both EasySoft and FreeTDS data
> sources, so I'm pretty confident that it's something between unixodbc
> and the server.  Straight SQL shells (i.e., db artisan) print the
> messages in the correct order.  It's only with DBD::ODBC that we're
> seeing this at this point.

excellent.

> And, as you'd expect when seeing an error like this, the output
> doesn't appear until the sproc is finished running.
>
> And btw, I'm changing the " [Easysoft][SQL Server Driver 10.0][SQL
> Server]" string to just leave "[EasySoft]".
>
> Here are the sprocs:
>
> CREATE PROCEDURE [dbo].[HS_post_message2] as begin declare @msg_cnt
> int declare @ret_cde int
>
> select @msg_cnt = 0 while ( @msg_cnt<  10 ) begin
>
> execute HS_sp_trace 'mark' select @msg_cnt = @msg_cnt +1 waitfor
> delay '00:00:01' print @msg_cnt end
>
> end
>
> ...whcih uses this sp_trace:
>
> CREATE PROCEDURE [dbo].[HS_sp_trace] @msg       VARCHAR(255), @errval
> INT = NULL, @numrows   INT = NULL, @totalrows INT = NULL OUTPUT AS
> BEGIN DECLARE @message VARCHAR(255) IF @totalrows IS NOT NULL AND
> @numrows IS NOT NULL SELECT @totalrows = @totalrows + @numrows
>
> --IF USER_NAME() = 'dbo' OR dbo.site_name(DEFAULT) = 'pntdbuser'
> BEGIN SELECT @message =  CONVERT( CHAR(26), GETDATE(), 109 ) + CASE
> WHEN ISNULL(@numrows,0)   != 0 THEN ' : rows affected ' +
> STR(@numrows) ELSE '' END + CASE WHEN ISNULL(@totalrows,0) != 0 THEN
> ' / total rows ' + STR(@totalrows)  ELSE '' END + CASE WHEN
> ISNULL(@errval,0 )   != 0 THEN ' : error ' + STR(@errval)
> ELSE '' END + + ' - ' + @msg --PRINT @message RAISERROR(@message, 0,
> 1) WITH NOWAIT END
>
> IF @errval != 0 AND @errval IS NOT NULL RETURN @errval
>
> RETURN END

I've not looked into these procedures in detail but hopefully I can 
create them here and reproduce your problem.

> Which produces this output using unixodbc 2.3.0:
>
> [FreeTDS]10 [FreeTDS]Jul 26 2011  6:02:03:433PM - mark [FreeTDS]9
> [FreeTDS]Jul 26 2011  6:02:02:433PM - mark [FreeTDS]8 [FreeTDS]Jul 26
> 2011  6:02:01:433PM - mark [FreeTDS]7 [FreeTDS]Jul 26 2011
> 6:02:00:433PM - mark [FreeTDS]6 [FreeTDS]Jul 26 2011  6:01:59:430PM -
> mark [FreeTDS]5 [FreeTDS]Jul 26 2011  6:01:58:430PM - mark
> [FreeTDS]4 [FreeTDS]Jul 26 2011  6:01:57:430PM - mark [FreeTDS]3
> [FreeTDS]Jul 26 2011  6:01:56:430PM - mark [FreeTDS]2 [FreeTDS]Jul 26
> 2011  6:01:55:430PM - mark [FreeTDS]1 [FreeTDS]Jul 26 2011
> 6:01:54:430PM - mark
>
> What do you think?
>
> Eric

Not sure right now. I will look at this properly tomorrow morning.

How are you calling the procs - what is the Perl code?

Thanks for producing a (hopefully) self contained example.

Please ping me if you don't hear from me by lunchtime UK time tomorrow.

Martin

>
>> -----Original Message----- From:
>> unixodbc-support-bounces at mailman.unixodbc.org [mailto:unixodbc-
>> support-bounces at mailman.unixodbc.org] On Behalf Of Martin J. Evans
>> Sent: Thursday, July 21, 2011 12:41 PM To: Support for the unixODBC
>> project Subject: Re: [unixODBC-support] Stored procedure messages
>> returned in reverse cron order from DBD::ODBC mssql/EasySoft
>>
>> On 20/07/2011 19:11, eric.berg at barclayscapital.com wrote:
>>> I have an odbc_err_handler set to a very simple subroutine that
>>> strips off the Vendor and driver info and the just prints it to
>>> the console. It looks like this:
>>>
>>> $msg =~ s/\[Easysoft\]\[SQL Server Driver 10.0\]\[SQL
>>> Server\]//;
>>>
>>> warn $msg . "\n";
>>>
>>> return 1;
>>>
>>> we also use this command all over our sprocs:
>>>
>>> RAISERROR(@message, 0, 1) WITH NOWAIT
>>>
>>> Which generates "error" messages that hit this odbc_err_handler.
>>>
>>> The problem is that for MSSQL (DBD::ODBC/Easysoft), we are
>>> receiving the output of this command in reverse cron order after
>>> the sproc completes.
>>>
>>> Anyone run into this problem?
>>>
>>> Eric
>>
>> Sorry Eric, but I've only just seen your post.
>>
>> Are you suggesting that if you call RAISERROR('one') then
>> RAISERROR('two') the odbc_err_handler sees two before one.
>>
>> If so I'm not aware of this problem.
>>
>> Perhaps you could knock up a small self-contained example and I'll
>> take a look at it.
>>
>> Martin -- Martin J. Evans Wetherby, UK
>> _______________________________________________ unixODBC-support
>> mailing list unixODBC-support at mailman.unixodbc.org
>> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
> _______________________________________________
>
> This e-mail may contain information that is confidential, privileged
> or otherwise protected from disclosure. If you are not an intended
> recipient of this e-mail, do not duplicate or redistribute it by any
> means. Please delete it and any attachments and notify the sender
> that you have received it in error. Unless specifically indicated,
> this e-mail is not an offer to buy or sell or a solicitation to buy
> or sell any securities, investment products or other financial
> product or service, an official confirmation of any transaction, or
> an official statement of Barclays. Any views or opinions presented
> are solely those of the author and do not necessarily represent those
> of Barclays. This e-mail is subject to terms available at the
> following link: www.barcap.com/emaildisclaimer. By messaging with
> Barclays you consent to the foregoing.  Barclays Capital is the
> investment banking division of Barclays Bank PLC, a company
> registered in England (number 1026167) with its registered offi! ce
> at 1 Churchill Place, London, E14 5HP.  This email may relate to or
> be sent from other members of the Barclays Group.
> _______________________________________________
> _______________________________________________ unixODBC-support
> mailing list unixODBC-support at mailman.unixodbc.org
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
>


-- 
Martin J. Evans
Wetherby, UK


More information about the unixODBC-support mailing list