Hi,
This is really troubling me. I've included a trace at the bottom of the
original mail. What I'm finding is that even though I set AutoCommit to 0
prior to my "do('declare @foo int'), the transaction appreas to occur
after it.
Can someone please help? I'd be really greatful.
Cheers,
Raf
-------- Original Message --------
Subject: DBD::Sybase - Transactions with Declare @foo and select @foo
From: "Raf" <[EMAIL PROTECTED]>
Date: Tue, July 1, 2003 5:52 pm
To: <[EMAIL PROTECTED]>
Hi,
I'm having some trouble and was hoping that someone might be able to
enlighten me a tad.
I'm using DBD::Sybase and trying to perform a transaction, which is
behaving rather weirdly.
My transaction is of the form:
* $dbh->{AutoCommit}=0
* $dbh->do('declare @foo int')
* $dbh->do(".. insert a tupple some place.. ")
* $dbh->do('select @foo=(select @@identity)')
* $dbh->do('insert into bar(f1,..,fi) values (v1,..,(select @foo),
..,vi)') * more inserts like this with the same reference to @foo
* $dbh->commit if all_eval_ok
Now what winds me up is that it'll happily declare @foo, however from
that point on all else seems to fall into trouble.
It will declare fine, however when I hit the first reference to @foo
(ie. in the 'select @foo=(select @@identity)') it starts to spew errors
telling me that I need to declare @foo before I can procede. This error
is reproduced for each of the subsequent inserts.
I'd be hugely greatful for any help?
Cheers,
Raf
=head2 Trace
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x505918)~INNER
'AutoCommit' 0)
<- STORE= 1 at DBSemi.pm line 673 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x505918)~INNER
'RaiseError' 1)
STORE DBI::db=HASH(0x505918) 'RaiseError' => 1
<- STORE= 1 at DBSemi.pm line 674 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
Doing:declare @last_insert int -> do for DBD::Sybase::db
(DBI::db=HASH(0x48a4d4)~0x505918 'declare @last_insert int')
1 -> prepare for DBD::Sybase::db (DBI::db=HASH(0x505918)~INNER 'declare
@last_insert int' undef)
dbih_setup_handle(DBI::st=HASH(0x5226c0)=>DBI::st=HASH(0xd43e9c),
DBD::Sybase::st, 51d394, Null!)
dbih_make_com(DBI::db=HASH(0x505918), DBD::Sybase::st, 396) thr#0
1 <- prepare= DBI::st=HASH(0x5226c0) at Sybase.pm line 150 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> execute for DBD::Sybase::st (DBI::st=HASH(0x5226c0)~0xd43e9c)
syb_db_opentran() -> ct_command(
BEGIN TRAN DBI506008
) = 1
syb_db_opentran() -> ct_send() = 1
syb_db_opentran() -> ct_results(4047) == 1
syb_db_opentran() -> ct_results(4046) == 1
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
ct_results() final retcode = -205
st_next_result() -> lasterr = 0, lastsev = 0
syb_st_execute() -> got CS_CMD_DONE: resetting ACTIVE, moreResults,
dyn_execed
<- execute= -1 at Sybase.pm line 151 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> err in DBD::_::common for DBD::Sybase::st
(DBI::st=HASH(0x5226c0)~0xd43e9c)
<- err= undef at Sybase.pm line 152 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> rows for DBD::Sybase::st (DBI::st=HASH(0x5226c0)~0xd43e9c)
<- rows= -1 at Sybase.pm line 153 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> FETCH for DBD::Sybase::st (DBI::st=HASH(0xd43e9c)~INNER
'syb_more_results')
.. FETCH DBI::st=HASH(0xd43e9c) 'syb_more_results' = undef
<- FETCH= undef at Sybase.pm line 154 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
<> DESTROY ignored for outer handle DBI::st=HASH(0x5226c0) (inner
DBI::st=HASH(0xd43e9c))
-> DESTROY for DBD::Sybase::st (DBI::st=HASH(0xd43e9c)~INNER)
syb_st_destroy: called on d45410...
syb_st_destroy(): freeing imp_sth->statement
syb_st_destroy(): cmd dropped: 1
<- DESTROY= undef at perl5db.pl line 1487
<- do= -1 at DBSemi.pm line 685
Doing:
insert into object_inserts(object_id,
status,
creation_time,
modification_time,
modified_by,
created_by)
values (?, ?, ?, ?, ?, ?)
, , 11, 0, 2003/06/10, 2003/06/10, 2, 2 -> do for
DBD::Sybase::db (DBI::db=HASH(0x48a4d4)~0x505918 '
insert into object_inserts(object_id,
status,
creation_time,
modification_time,
modified_by,
created_by)
values (?, ?, ?, ?, ?, ?)
' undef 11 0 '2003/06/10' '2003/06/10' 2 2)
1 -> prepare for DBD::Sybase::db (DBI::db=HASH(0x505918)~INNER '
insert into object_inserts(object_id,
status,
creation_time,
modification_time,
modified_by,
created_by)
values (?, ?, ?, ?, ?, ?)
' undef)
dbih_setup_handle(DBI::st=HASH(0xd43efc)=>DBI::st=HASH(0xd43ecc),
DBD::Sybase::st, d43f08, Null!)
dbih_make_com(DBI::db=HASH(0x505918), DBD::Sybase::st, 396) thr#0
dbd_preparse parameter :p1 ()
dbd_preparse parameter :p2 ()
dbd_preparse parameter :p3 ()
dbd_preparse parameter :p4 ()
dbd_preparse parameter :p5 ()
dbd_preparse parameter :p6 ()
dbd_preparse scanned 6 distinct placeholders
syb_st_prepare: ct_dynamic(CS_PREPARE) for DBDf
syb_st_prepare: ct_dynamic(CS_DESCRIBE_INPUT) for DBDf
syb_st_prepare: ct_results(CS_DESCRIBE_INPUT) for DBDf - restype 4051
syb_st_prepare: ct_res_info(CS_DESCRIBE_INPUT) statement has 6 parameters
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 1, type 16, status
256, length 35
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 2, type 16, status
256, length 35
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 3, type 12, status
256, length 8
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 4, type 12, status
256, length 8
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 5, type 16, status
256, length 35
syb_st_prepare: ct_describe(CS_DESCRIBE_INPUT) col 6, type 16, status
256, length 35
syb_st_prepare: ct_results(CS_DESCRIBE_INPUT) for DBDf - restype 4046
1 <- prepare= DBI::st=HASH(0xd43efc) at Sybase.pm line 150 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> execute for DBD::Sybase::st (DBI::st=HASH(0xd43efc)~0xd43ecc 11 0
'2003/06/10' '2003/06/10' 2 2)
bind :p1 <== '11' (attribs: )
bind :p1 () <== 11 (size 2/3/0, ptype 5, otype 0)
bind :p1 <== '11' (size 2, ok 1)
datafmt: type=16, status=256, len=35
saved type: 16
bind :p2 <== '0' (attribs: )
bind :p2 () <== 0 (size 1/2/0, ptype 5, otype 0)
bind :p2 <== '0' (size 1, ok 1)
datafmt: type=16, status=256, len=35
saved type: 16
bind :p3 <== '2003/06/10' (attribs: )
bind :p3 () <== '2003/06/10' (size 10/11/0, ptype 4, otype 0)
bind :p3 <== '2003/06/10' (size 10, ok 1)
datafmt: type=0, status=256, len=-9
saved type: 12
bind :p4 <== '2003/06/10' (attribs: )
bind :p4 () <== '2003/06/10' (size 10/11/0, ptype 4, otype 0)
bind :p4 <== '2003/06/10' (size 10, ok 1)
datafmt: type=0, status=256, len=-9
saved type: 12
bind :p5 <== '2' (attribs: )
bind :p5 () <== 2 (size 1/2/0, ptype 5, otype 0)
bind :p5 <== '2' (size 1, ok 1)
datafmt: type=16, status=256, len=35
saved type: 16
bind :p6 <== '2' (attribs: )
bind :p6 () <== 2 (size 1/2/0, ptype 5, otype 0)
bind :p6 <== '2' (size 1, ok 1)
datafmt: type=16, status=256, len=35
saved type: 16
syb_st_execute() -> ct_send() OK
st_next_result() -> ct_results(4047) == 1
st_next_result() -> ct_results(4046) == 1
ct_results() final retcode = -205
st_next_result() -> lasterr = 0, lastsev = 0
syb_st_execute() -> got CS_CMD_DONE: resetting ACTIVE, moreResults,
dyn_execed
<- execute= 1 at Sybase.pm line 151 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> err for DBD::Sybase::st (DBI::st=HASH(0xd43efc)~0xd43ecc)
<- err= undef at Sybase.pm line 152 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> rows for DBD::Sybase::st (DBI::st=HASH(0xd43efc)~0xd43ecc)
<- rows= 1 at Sybase.pm line 153 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> FETCH for DBD::Sybase::st (DBI::st=HASH(0xd43ecc)~INNER
'syb_more_results')
.. FETCH DBI::st=HASH(0xd43ecc) 'syb_more_results' = undef
<- FETCH= undef at Sybase.pm line 154 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
<> DESTROY ignored for outer handle DBI::st=HASH(0xd43efc) (inner
DBI::st=HASH(0xd43ecc))
-> DESTROY for DBD::Sybase::st (DBI::st=HASH(0xd43ecc)~INNER)
syb_st_destroy: called on d45410...
syb_st_destroy: ct_dynamic(CS_DEALLOC) for DBDf
syb_st_destroy(): freeing imp_sth->statement
syb_st_destroy(): cmd dropped: 1
<- DESTROY= undef at perl5db.pl line 1487
<- do= 1 at DBSemi.pm line 685
Doing:select @last_insert = (select @@identity) -> do for
DBD::Sybase::db (DBI::db=HASH(0x48a4d4)~0x505918 'select @last_insert =
(select @@identity)')
1 -> prepare for DBD::Sybase::db (DBI::db=HASH(0x505918)~INNER 'select
@last_insert = (select @@identity)' undef)
dbih_setup_handle(DBI::st=HASH(0xd43fe0)=>DBI::st=HASH(0xd43eb4),
DBD::Sybase::st, d43fec, Null!)
dbih_make_com(DBI::db=HASH(0x505918), DBD::Sybase::st, 396) thr#0
1 <- prepare= DBI::st=HASH(0xd43fe0) at Sybase.pm line 150 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
-> execute for DBD::Sybase::st (DBI::st=HASH(0xd43fe0)~0xd43eb4)
syb_st_execute() -> ct_command() OK
syb_st_execute() -> ct_send() OK
st_next_result() -> ct_results(4048) == 1
st_next_result() -> ct_results(4046) == 1
ct_results() final retcode = -205
st_next_result() -> lasterr = 137, lastsev = 15
st_next_result() -> restype is not data result or
syb_cancel_request_on_error is TRUE, force failFlag
st_next_result() -> failFlag set - clear request
syb_st_finish() -> ct_cancel(CS_CANCEL_ALL)
syb_st_finish() -> resetting ACTIVE, moreResults, dyn_execed
syb_st_execute() -> got CS_CMD_FAIL: resetting ACTIVE, moreResults,
dyn_execed
!! ERROR: 137 'Server message number=137 severity=15 state=1 line=1
server=pelicantext=Must declare variable '@last_insert'.
'
<- execute= undef at Sybase.pm line 151 via
/usr/local/lib/perl5/5.6.1/perl5db.pl line 1483
<> DESTROY ignored for outer handle DBI::st=HASH(0xd43fe0) (inner
DBI::st=HASH(0xd43eb4))
-> DESTROY for DBD::Sybase::st (DBI::st=HASH(0xd43eb4)~INNER)
syb_st_destroy: called on d45410...
syb_st_destroy(): freeing imp_sth->statement
syb_st_destroy(): cmd dropped: 1
error: 137 'Server message number=137 severity=15 state=1 line=1
server=pelicantext=Must declare variable '@last_insert'.
'
<- DESTROY= undef at perl5db.pl line 1487
!! ERROR: 137 'Server message number=137 severity=15 state=1 line=1
server=pelicantext=Must declare variable '@last_insert'.
'
<- do= undef at DBSemi.pm line 685
DBD::Sybase::db do failed: Server message number=137 severity=15 state=1
line=1 server=pelicantext=Must declare variable '@last_insert'.
... umm.. its broken...
DBD::Sybase::db do failed: Server message number=137 severity=15 state=1
line=1 server=pelicantext=Must declare variable '@last_insert'.
...propagated at gmdb/CaXinova/Model/DBSemi.pm line 688.
DB<9>
DB<9>
DB<9>
=cut
