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
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
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 effect with
client code).
-
Ron Reidy
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();
the 'select from
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
Does anybody have a clue on how to bind oracle sequences.
Background:
Due to the large amount of data to be loaded into our oracle9i db by our
applications, a bulk-load software would have been our first choice to
handle the inserts. However, since our data is higly dynamic in its
construction,
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:
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
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
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
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
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
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 anybody
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 =
This solution requires an extra network hop. The better solution is a before insert
trigger to populate the column.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Ravi Kongara [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 31, 2004 4:55 PM
To:
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 'perldoc
16 matches
Mail list logo
