sybperl-l Archive

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:

$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
    describe() -> col 0, type 2, realtype 2
describe() retcode = 1
    st_next_result() -> lasterr = 0, lastsev = 0
    <- execute= 176 at usage_by_dept_test.pl 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 usage_by_dept_test.pl 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 usage_by_dept_test.pl line 91
    -> fetchrow_array for DBD::Sybase::st (DBI::st=HASH(0x402f3f44)~0x40306644) thr#4001bfb0
    <- fetchrow_array= ( 'Physics                  ' 640719 ) [2 items] row3 at usage_by_dept_test.pl 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 usage_by_dept_test.pl 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