I've answered Raf privately, but just so that others can be aware of the
issue:
First, AutoCommit and/or Chained Mode (syb_chained_txn attribute) are
not affecting declaring local Transact-SQL variable (declare @foo int).
In T-SQL a variable declared with "declare @foo <type>" is only visible
in the current SQL batch. In DBD::Sybase a $dbh->do(...) is one batch -
so writing:
$dbh->do('declare @foo int');
$dbh->do('select @foo = @@identity');
will fail for the second statement, with the message that @foo is not
declared.
To correctly use @variables with DBD::Sybase (or any other Sybase client
app) you have to send the entire SQL batch in one go:
$dbh->do(q(
declare @foo int
insert sometable (...) values(...)
select @foo = @@identity
insert someothertable (....) values(..., @foo)
));
Hopefully this clears up the confusion around this issue.
Michael
On Wed, 2003-07-02 at 10:29, Raf wrote:
> 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
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] 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
