From: [EMAIL PROTECTED]
> Hi all, this is a difficult one to explain!!
>
> i have a perl script that is inserting entries into an oracle database
> table. so far so good!! the difficulty i have is that the table has a
> sequence against a unique id column (number) that can increment itself
> using seq.nextval.
>
> when i test the script without having an insert statement implemented
> i get the results i want ie:
>
> id entry
> 1 abc
> 2 def
> 3 ghi
>
> etc etc.
>
> however, when i implement the insert statement i get this:
> id entry
> 1 abc
> 3 def
> 6 ghi
> 10 jkl
>
> the offending code is:
>
> my $sth = $dbh->prepare("insert into "table" values (?, ?)");
> my $get_nextval = $dbh->prepare("select seq.nextval from "table");
>
> loop {
> $get_nextval->execute();
> $get_nextval->bind_col(1, \$id);
> $get_nextval->fetch();
> $get_nextval->finish();
> $sth->execute($id, $abc);
> }
Ahhh, this is a nice one.
Look at the select statement :
select seq.nextval from "table"
now suppose there is no row in that table, how many rows do you
get from this select? And how many you'd get if there was one row,
two rows, three, ...
What happens here is each time you insert a row into the table,
this select returns more rows ... more nextvals ..., you only use the
first one, but the sequence is moved by more, not just one.
Either change the select to :
select seq.nextval from dual
(dual is a "fake table" that "contains" one row with no columns) or
change your insert statement to
insert into "table"
select seq.nextval, ? from dual
(I don't have any Oracle by hand so I can't test it, but I think I
remember it right.) And then just
$insert->execute($abc)
If you'd need the ID of the row you've just inserted I think you could
also do something like
my $insert = $dbh->prepare(<<"*SQL*");
Declare vID Integer;
select vID = seq.nextval from dual;
insert into "table" values ( vID, ?);
select vID from dual;
*SQL*
And then
$insert->execute( $abc );
$insert->bind_col(1, \$id);
$insert->fetch();
$insert->finish();
Again I can't test it, but you get the idea.
Jenda
=========== [EMAIL PROTECTED] == http://Jenda.Krynicky.cz ==========
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain
I can't find it.
--- me
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
