Skip to Content.
Sympa Menu

freetds - RE: [freetds] Need help with SQL server stored procedure

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Staats, Frederick W" <frederick.w.staats AT intel.com>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Need help with SQL server stored procedure
  • Date: Sat, 19 Jul 2003 09:25:07 -0700

I noticed that DBD::Sybase was broken with freetds 0.61. When I tracked
down the problem in June it appeared to be due to a patch in the return
codes from a different bug. Here is the email thread I had with the
Michael
Peppler the maintainer of Sybase::DBD. He was going to follow up on it
but I don't know the current status. Included in the email is a patch
to DBD::Sybase but if the CVS for freetds is working then someone also
fixed on the freetds side.

--
Frederick (Fritz) Staats

-----Original Message-----
From: Michael Peppler [mailto:mpeppler AT bluewin.ch]
Sent: Friday, June 27, 2003 4:36 AM
To: Staats, Frederick W
Cc: quozl AT us.netrek.org
Subject: DBD::Sybase & FreeTDS (was RE: Web site down for bug lists and
email list)


Thanks for the patch. I still wonder if this is the right thing to do as
the code works correctly with the Sybase libs. However, I need to do
some testing to see what the correct behavior is supposed to be.

Michael

On Thu, 2003-06-26 at 21:33, Staats, Frederick W wrote:
> Michael,
>
> I tracked down the bug with $sth->{syb_more_results} and freetds.
> Since your bug tracking web site is down I'm forwarding this bugfix
> directly to you. Please let me know if you plan on rolling it
> into the next release of DBD::Sybase.
>
> >From the change log in freetds version 0.61:
> Wed Oct 31 12:21:57 2001 James Cameron <quozl AT us.netrek.org>
> * src/ctlib/ct.c (ct_results): return CS_END_RESULTS instead
of
> CS_SUCCEED on subsequent call after we had returned
CS_SUCCEED.
> Related to special handling of queries with no result sets.
PHP
> was looping trying to flush the connection of result sets
after
> the first one.
>
> This new return code was not recognized in your code which made
> it fail. I've got a simple patch that seems to have fixed it
> for me:
>
> *** dbdimp.c Thu Jun 26 12:24:59 2003
> --- dbdimp.c.old Thu Jun 26 10:58:15 2003
> ***************
> *** 2471,2481 ****
> if(failFlag || retcode == CS_FAIL || retcode == CS_CANCELED)
> return CS_CMD_FAIL;
>
> - /* FreeTDS added a result code CS_END_RESULTS */
> - /* Do the right thing with it Frederick Staats, 6/26/03 */
> - if(retcode == CS_END_RESULTS)
> - restype= CS_CMD_DONE;
> -
> imp_sth->lastResType = restype;
>
> return restype;
> --- 2471,2476 ----
>
>
>
> -----Original Message-----
> From: Michael Peppler [mailto:mpeppler AT peppler.org]
> Sent: Thursday, June 26, 2003 5:25 AM
> To: Staats, Frederick W
> Subject: Re: Web site down for bug lists and email list
>
>
> On Wed, 2003-06-25 at 02:18, Staats, Frederick W wrote:
> > Michael,
> >
> > http://www.peppler.org/ seems to be down for bug lists and
> > your archived email list. I'm sending this email to you
> > as a FYI if you didn't know this already.
>
> Yes, the connection to my ISP from my home machine is hosed, and I'm
> away from home at the moment (for a prolonged period).
>
>
> > I've been working at trying to get DBI version 1.37,
> > DBD::Sybase 1.0, freeTDS 0.61 on Perl 5.8 running on a
> > Linux machine. I'm talking to a Microsoft SQL Server
> > 2000 and have tried using 4.2, 7.0 and 8.0 TDS protocols.
> >
> > I can get simple SELECT queries to work correctly
> > but I've had trouble with the $sth->{syb_more_results}
> > always returning true (causing the job to hang on the
> > next $sth->fetch) when looking at the results from
> > a system stored procedure:
>
> <snip>
>
> I suspect that this is a FreeTDS problem. DBD::Sybase simply uses the
> return values from the various Client Library API calls to determine
how
> to set syb_more_results, and this gets set correctly when using the
> Sybase libraries.
>
> Have you brought this up on the FreeTDS mailing list?
>
> Michael

From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org]On Behalf Of Lowden, James K
Sent: Friday, July 18, 2003 3:47 PM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] Need help with SQL server stored procedure
output?


> From: Gaurav Nangia [mailto:gnangia AT mspintl.com]
> Sent: July 18, 2003 12:04 PM
>
> I am using freetds 0.61 with DBD::Sybase on Redhat linux to
> connect to SQL Server 7.0.
...
> I am having an issue with
> running a stored procedure that RETURNS A VALUE. How do I
> retrieve that value.
...
> #!/usr/bin/perl
>
> use DBI;
> $dbh = DBI->connect('DBI:Sybase:server=DEV;', $user, $passwd)
> or die "Cannot
> connect:$@\n";
> $sql = ("declare \@test numeric(9,4) exec TEST
> \@results=\@test OUTPUT");
> $sth=$dbh->prepare($sql);
> $sth->execute;
> while(@d = $sth->fetchrow_array)
> {
> foreach $xyz(@d)
> {
> print "REsult=>$xyz<=\n";
> }
> }

DBD::Sybase returns the procedure's return code as a row, setting a
statement handle attribute to denote the row's type. Your loop isn't
enough
afaik; cf. "perldoc DBD::Sybase" for more. I think you need loop more
like:

do {
while($d = $sth->fetch) {
print @{$d}, $/;
}
} while($sth->{syb_more_results});

However, FreeTDS 0.61 appears broken wrt this feature in our ct-lib. I
just
finished testing the above, and got these errors:

1. I got syb_result_type=4043, but no data.
2. $sth->{syb_more_results} always returns 1.
3. $sth->{syb_result_type} returns -1077946652 past the "end of the
data".

The good news is that current CVS (and recent snapshots) work correctly.

I had to rebuild DBD::Sybase to make it link to
/usr/local/lib/libct.so.1.
If you're already using libct.so.1, then you need to update just
FreeTDS.
If you do have to rebuild DBD::Sybase, it might complain about lacking
tdsiconv.h, which we don't install even though we should. I'm going to
look
at the issue more closely this weekend, to see what we really should do,
and
if tdsiconv.h is serving a real purpose.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page