Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Jerry Grooms <grooms at uky dot edu>
Subject: DBD::Sybase 1.01 & queries against temp table
Date: Dec 3 2003 4:12PM

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:

while (@myrow = $sth->fetchrow_array()) { # process rows returned
    print "$cnt @myrow\n";


What IS being returned?  Good question.

Sometimes I get one (empty) row returned.  Sometimes I get something like

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 ./ 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

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.



    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,
into #cpuhrs
from ParDetail
   UsageType = 'CPUH' and
   SvrCd like 'SDX%' and
   SampleDate between 'Jan 1, 2002  00:00' and 'Dec 1, 2003  00:00'
   group by ProjectId

'Cpu Hrs'=convert(numeric (10,0),sum(D.CpuHrs))
from Project C, #cpuhrs D
   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 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
    describe() -> col 0, type 2, realtype 2
describe() retcode = 1
    st_next_result() -> lasterr = 0, lastsev = 0
    <- execute= 176 at line 89
    -> fetchrow_array for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
    dbih_setup_fbav for 1 fields => 0x40306638
    <- fetchrow_array= ( 0 ) [1 items] row1 at line 91
1 0
    -> fetchrow_array for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
    st_next_result() -> ct_results(4040) == 1
    ct_res_info() returns 2 columns
    ct_describe(0): type = 1, maxlen = 25
    describe() -> col 0, type 1, realtype 1
    ct_describe(1): type = 15, maxlen = 8
    describe() -> col 1, type 15, realtype 15
describe() retcode = 1
    st_next_result() -> lasterr = 0, lastsev = 0
    syb_st_fetch() -> st_next_results() == 4040
    <- fetchrow_array= ( ) [0 items] row2 at line 91
    -> fetchrow_array for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
    <- fetchrow_array= ( 'Physics                  ' 640719 ) [2 items] row3 at line 101
    -> finish for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
    syb_st_finish() -> ct_cancel(CS_CANCEL_ALL)
    syb_st_finish() -> resetting ACTIVE, moreResults, dyn_execed
    <- finish= 1 at line 103

    -> DESTROY for DBD::Sybase::st (DBI::st=HASH(0x40306644)~INNER) thr#4001bfb0
    syb_st_destroy: called on 40164620...
    syb_st_destroy(): freeing imp_sth->statement
    syb_st_destroy(): cmd dropped: 1
    <- DESTROY= undef
    -> DESTROY for DBD::Sybase::db (DBI::db=HASH(0x402f3e48)~INNER) thr#4001bfb0
    syb_db_disconnect() -> ct_close()
    <- DESTROY= undef