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

eric.berg at barclayscapital.com eric.berg at barclayscapital.com
Wed Jul 27 19:38:37 BST 2011


Hey, thanks, Martin.

It's super simple.  Here you go:

my $dbh = DBI->connect(....);
my $sth = $dbh->prepare("exec workdb..HS_post_message2");
my $rs = $sth->execute;
warn "Execute done.";

Thanks again.  I'll check in with you tomorrow.

Hey, while I've got your attention, a little while ago when we were dealing with the last segfault issue, Richard suggested that I add "-DSIZEOF_LONG=8 -DBUILD_REAL_64_BIT_MODE" to the CFLAGS line in the generated makefile, and that's been very helpful.  What can we do about fixing DBD::ODBC to avoid having to do this manually every time.  This afternoon, for example, I did a cpan update of a bunch of modules and immediately we started seeing the segfault issue again, because cpanplus thought we needed to update dbd.odbc.

Thanks!

Eric

> -----Original Message-----
> From: Martin J. Evans [mailto:bohica at ntlworld.com]
> Sent: Wednesday, July 27, 2011 2:34 PM
> To: Support for the unixODBC project
> Cc: Berg, Eric: IT (NYK); PointDB Support
> Subject: Re: [unixODBC-support] Stored procedure messages returned in
> reverse cron order from DBD::ODBC mssql/EasySoft
> 
> 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