On 2004-09-07 08:40:33 -0600, Reidy, Ron wrote:
> I've never seen before insert trigger being slower than doing the same
> thing in the client code. I guess it would depend on what you are
> doing in the trigger and how many sequences are cached (but the amount
> cached would also have the same ef
Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED]
Sent: Monday, September 06, 2004 9:06 PM
To: Reidy, Ron
Cc: Jean-Pierre Utter Löfgren; DBI List
Subject: RE: How to use sequences in DBI/DBD::Oracle with bind
On Tue, 2004-08-31 at 07:36,
Hallo,
why not using the returing clause?
$sth->prepare ( "insert into foo (my_id, data) values (my_id_seq.nextval,?) returning
my_id into ?" );
$sth->bind_param( 1, "bar");
my $id;
$sth->bind_param_inout( 2, \$id, 12);
$sth->execute();
Tuesday, September 7, 2004, 4:05:09 AM, Jared wrote:
>> my
On Tue, 2004-08-31 at 07:36, Reidy, Ron wrote:
> Use a before insert trigger to populate the column.
>
Triggers have the cachet of being a neat way to
automatically populate key columns, and I have
used them many times myself.
They are also much slower than the posters original code.
Jared
> my $start_num = $dbh->selectrow_array( "select my_id_seq.nextval from
> dual");
> my @seq = ( $start_num..100,000 ); # some end number
>
> $sth->prepare ( "insert into foo (my_id, data) values (?,?)" );
> $sth->bind_param( 1, shift(@seq) );
> $sth->bind_param( 2, "bar");
> $sth->execute();
>
On Tue, Aug 31, 2004 at 07:54:40AM -0400, Hardy Merrill wrote:
> I'm no Oracle expert, but I've always done it the way you originally did it:
>
> $sth->prepare ( "insert into foo (my_id, data) values
> (my_id_seq.nextval, 'bar')" );
> $sth->execute();
>
> I searched for "seq" in '
: Jean-Pierre Utter Löfgren
Cc: [EMAIL PROTECTED]
Subject: Re: How to use sequences in DBI/DBD::Oracle with bind
Alternative solution could be like this, but it's based on assumption that
the sequence is exclusively used by current application/transaction...
At the start of program
Alternative solution could be like this, but it's based on assumption that
the sequence is exclusively used by current application/transaction...
At the start of program fetch my_id_seq.nextval to a variable and build
a sequence array starting with that nextval.
my $start_num = $dbh->selectrow_ar
try $data = 'bar';$sth->prepare (insert into foo (my_id, data)
values(my_id_seq.nextval,?));$sth->execute($data);What you are doing
below is passing the string value my_id_seq.nextval to the field
my_id.
>>>Jean-Pierre Utter Lfgren <[EMAIL PROTECTED]> 08/30 10:19 pm >>>
Does anybo
Sorry, I meant to reply to the list.
In most cases a trigger probably is the best.
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 31, 2004 10:54 AM
To: Helck, Timothy
Subject: RE: How to use sequences in DBI/DBD::Oracle with bind
A trigger is better
Use a before insert trigger to populate the column.
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-Original Message-
From: Jean-Pierre Utter Löfgren [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 31, 2004 5:20 AM
To: [EMAIL PROTECTED]
Subject: How to use sequences in DBI/
On Tue, Aug 31, 2004 at 01:19:52PM +0200, Jean-Pierre Utter Löfgren wrote:
> Does anybody have a clue on how to bind oracle sequences.
I don't think you have to.
> is however that I use sequences in the database inserts for various
> reasons, as some inserts use the same primary key, sub-data
Thanks for your response, Hardy!
I read that too, but since I dynamicaly build the "meta-sql" code in
another application, I haven't really control over knowing where the
sequence are located and wether the occcur in a statment or not.
Kind of tricky this one.
Thanks also Andy and Jon for your m
On 2004-08-31 13:19:52 +0200, Jean-Pierre Utter Löfgren wrote:
> Using DBI/DBD, it handles inserts using sequneces very nicely as long
> as I do
>
> $sth->prepare ( "insert into foo (my_id, data) values
> (my_id_seq.nextval, 'bar')" );
> $sth->execute();
>
> But if I rearange the code to be more
I'm no Oracle expert, but I've always done it the way you originally did it:
$sth->prepare ( "insert into foo (my_id, data) values
(my_id_seq.nextval, 'bar')" );
$sth->execute();
I searched for "seq" in 'perldoc DBD::Oracle' and found this under LOB locator stuff:
15 matches
Mail list logo
