PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: DBD::Sybase 1.01 & queries against temp table
Date: Dec 3 2003 6:07PM

On Wed, 2003-12-03 at 08:12, Jerry Grooms wrote:
> Due to an OS upgrade, I recently upgraded DBD::Sybase from 0.95 to
> 1.01 along with a change of FreeTDS from 0.53 to 0.62-dev. (MS SQL
> server; TDS version 7.0).
> 
> I had a bunch of previously written perl query scripts that created
> a temp table (SELECT INTO) and then did a second select with a join
> against the temp table.  These queries were working with no problems
> prior to upgrading DBD::Sybase.  Post-upgrade I'm having problems
> and trying to determine the root cause.
> 
> Here are the knowns:
> 
> o) executing a SQL query as described above outside of DBI/DBD (with
>    sqsh) returns the correct results, so the query itself and
>    FreeTDS operation look ok (I assume).
> 
> o) executing the same SQL query with DBI/DBD and looking at TDSDUMP
>    output, I see the correct query results returned, so, again,
>    DBD::Sybase appears to be talking to FreeTDS at some level.
> 
> In the Perl scripts, I'm using the standard fetch loop which, again,
> was working before upgrade; eg:
> 
> $sth->execute;
> $cnt=0;
> while (@myrow = $sth->fetchrow_array()) { # process rows returned
>     $cnt++;
>     print "$cnt @myrow\n";
> 
> }
> 
> What IS being returned?  Good question.
> 
> Sometimes I get one (empty) row returned.  Sometimes I get something like
> this:
> 
> DBD::Sybase::st execute failed: Server message number=208
> severity=16 state=0 line=3 server=blah-blah text=Invalid object name
> '#proj_totals'. at ./foo.pl line 136.
> 
> as if the second select cannot access the temp table (#proj_totals).
> 
> 
> If I reduce things down and focus on the join field for a minute...
> 
> I can do a simple DBI "select blah from PERMANENT_TABLE where
> join_field = "blah" and the results are correct.
> 
> However, if I change PERMANENT_TABLE to TEMP_TABLE (should be the
> same), I get no results.  (if I do "select *" from TEMP_TABLE, the
> data IS there).
> 
> I have enabled tracing which is attached.  In the trace, I can see
> evidence of the first row that I know should be returned (right
> before the finish for DBD::Sybase).  But, the query appears to stop
> there.
> 
> Can anyone see where any possible oversight might be lurking?  Are
> there any known 1.01 DBD::Sybase changes that could possibly be
> affecting this?
> 
> Thanks for any hints.
> 
> Jerry
> 
> 
> 
> ---
> 
> 
>     DBI::db=HASH(0x402f3e48) trace level set to 3 in DBI 1.38-ithread
>     Note: perl is running without the recommended perl -w option
> 
> 
> 
> Department                      CPU Hours
> ----------------------------    ---------
>     -> prepare for DBD::Sybase::db (DBI::db=HASH(0x402f3b6c)~0x402f3e48 '
> 
> select ProjectId,
> CpuHrs=sum(UsageValue)
> into #cpuhrs
> from ParDetail
>    where
>    UsageType = 'CPUH' and
>    SvrCd like 'SDX%' and
>    SampleDate between 'Jan 1, 2002  00:00' and 'Dec 1, 2003  00:00'
>    group by ProjectId
> 
> 
> select
> 'Department'=substring(Department,1,25),
> 'Cpu Hrs'=convert(numeric (10,0),sum(D.CpuHrs))
> from Project C, #cpuhrs D
>    where
>    C.ProjectId = D.ProjectId
>    and C.I...') thr#4001bfb0
>     dbih_setup_handle(DBI::st=HASH(0x402f3f44)=>DBI::st=HASH(0x40306644), DBD::Sybase::st, 403065cc, Null!)
>     dbih_make_com(DBI::db=HASH(0x402f3e48), 40305ed0, DBD::Sybase::st, 396, 0) thr#4001bfb0
>     <- prepare= DBI::st=HASH(0x402f3f44) at usage_by_dept_test.pl line 63
>     -> execute for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
>     syb_st_execute() -> ct_command() OK
>     syb_st_execute() -> ct_send() OK
>     st_next_result() -> ct_results(4047) == 1
>     st_next_result() -> ct_results(4046) == 1
>     st_next_result() -> ct_results(4043) == 1
>     ct_res_info() returns 1 columns
>     ct_describe(0): type = 2, maxlen = 4

Hmmm - this bit is strange. Why do we think that there is a result set
with a single column. The first query in your set is a select into, so
does not produce any results, and the second has two columns, no?

Have you tried coding the fetch loop so that you get all of the result
sets?

do {
   while(@data = $sth->fetchrow_array()) {
       ....
   }
} while($sth->{syb_more_results});

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                 http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or 
long term contract positions - http://www.mbay.net/~mpeppler/resume.html