Re: How can I get insert_id from DBD::Oracle?
> > But for minimal app code change I'd suggest a using a PL/SQL trigger
> > that implements the auto increment functionality and saves the last
> > value into a per-session package variable.
> >
> > If you, or anyone, does the latter then I'd be very interested in
> > having a copy as I'll need to do something like that soon myself.
>
> An insert trigger that sets the 'autoincrement' column from a sequence
> is something that I've done before:
>
> create or replace trigger
> mytable_ai_trigger
> before insert
> on mytable
> for each row
> begin
> :new.ai_column := mytable_ai_seq.nextval;
> end;
> /
>
> You can then get the value just inserted by selecting sequencename.currval
> (from dual, of course).
Sure, but the code emulating $h->{mysql_insertid} would need to know which sequence
was most recently used. It the trigger also copied the last id into a session variable
then you wouldn't have that issue. Migration from mysql would thus be simpler.
Tim.
RE: How can I get insert_id from DBD::Oracle?
All,
Using a sequence and trigger as discussed will allow the following
to work :
INSERT INTOtab (value) VALUES ('foo');
SELECT MAX(id) FROM tab;
as long as these two statements are within the same transaction
block.
I do however like the Oracle 8 returning clause a lot.
--Rich
RE: How can I get insert_id from DBD::Oracle?
Actually, if you are using Oracle 8 or higher, you can use the RETURNING
clause of a DML statement to give you any column. The syntax is something
like this ..
:
$insJR = $dbh -> prepare (q (
INSERT
INTOIIJobRun (IIJobID, StartTimeStamp)
VALUES (:jobid, pkg_Calc.Now)
RETURNING
IIJobRunID INTO :jobrunid
)) ;
:
#
# Insert the IIJobRun row, and get back the IIJobRunID column value
#
$insJR -> bind_param (":jobid", $kIIJobID) ;
$insJR -> bind_param_inout (":jobrunid", \$JobRunID, 10) ;
$insJR -> execute ;
This is an extract from an actual script, so I apologize if it doesn't mean
much to you.
Hope this helps,
Steve
-Original Message-
From: Jim Spath [mailto:[EMAIL PROTECTED]]
Sent: Saturday, June 09, 2001 5:22 AM
To: Gregory; [EMAIL PROTECTED]
Subject: Re: How can I get insert_id from DBD::Oracle?
There is no DBD::Oracle equivalent for retrieving the value of
insert_id. You must create a sequence within oracle, then retrieve its
value and assign it to a variable within your script. You can then use
that value when you execute your insert and later in your script.
Jim
At 12:14 PM 6/8/01 -0700, Gregory wrote:
>I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with
>DBD::mysql I could get the id of inserted row with DBH->{mysql_insertid}.
>Is there any way to do the same thing with Oracle (DBD::Oracle)?
>
>Thanks. Grisha.
#!/usr/local/bin/perl
push@J,[($")x70]for 0..20;sub
p{$J[$q][$p]=$_;print"\e[H\e[J";print@$_,$/for@J
;$J[$q][$p]=$"}sub f{sprintf"%.f",pop}for('Just another Perl
hacker.'=~/./g){$
t=/
/and$J[20][$X++]=$_,next;{$x=70+$t*($X-70)*.8;$y=20-63.25*$t+50*$t**2;last
if$x<$X;$p=f$x;p$q=f$y;$t+=.1;select$,,$,,$,,.1;redo}$J[20][$X++]=$_}p
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: How can I get insert_id from DBD::Oracle?
On Fri, Jun 08, 2001 at 11:23:07PM +0100, Tim Bunce wrote: > For maximum perfomance I'd suggest selecting from an Oracle 'sequence' > before you do the insert. Done that plenty of times... > But for minimal app code change I'd suggest a using a PL/SQL trigger > that implements the auto increment functionality and saves the last > value into a per-session package variable. > > If you, or anyone, does the latter then I'd be very interested in > having a copy as I'll need to do something like that soon myself. An insert trigger that sets the 'autoincrement' column from a sequence is something that I've done before: create or replace trigger mytable_ai_trigger before insert on mytable for each row begin :new.ai_column := mytable_ai_seq.nextval; end; / You can then get the value just inserted by selecting sequencename.currval (from dual, of course). Rob -- Rob Fugina, Systems Guy [EMAIL PROTECTED] -- http://www.geekthing.com My firewall filters MS Office attachments. I'm glad we're not getting all the government we pay for...
Re: How can I get insert_id from DBD::Oracle?
On Fri, Jun 08, 2001 at 12:24:33PM -0700, Kokarski, Anton wrote:
> Greg,
>
> Look around on mysql.com I've seen a mentioning of the utility that allows
> you to port MySQL to oracle. I think it comes from Oracle.
And is a mostly hopeless marketing tick box gimmick.
> I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with
> DBD::mysql I could get the id of inserted row with DBH->{mysql_insertid}.
> Is there any way to do the same thing with Oracle (DBD::Oracle)?
For maximum perfomance I'd suggest selecting from an Oracle 'sequence'
before you do the insert.
But for minimal app code change I'd suggest a using a PL/SQL trigger
that implements the auto increment functionality and saves the last
value into a per-session package variable.
If you, or anyone, does the latter then I'd be very interested in
having a copy as I'll need to do something like that soon myself.
[There's even an undocumented magic hook in the DBI that lets you
emulate other drivers private attributes by storing a code ref
into the attribute :-) That you you can emulate the mysql_insertid
attribute. In theory. I've never tried it.]
Tim.
RE: How can I get insert_id from DBD::Oracle?
Yes there is one, as a matter effect mysql prides itself on that one.
Ilya Sterin
-Original Message-
From: Kokarski, Anton
To: 'Gregory'; [EMAIL PROTECTED]
Sent: 6/8/01 1:24 PM
Subject: RE: How can I get insert_id from DBD::Oracle?
Greg,
Look around on mysql.com I've seen a mentioning of the utility that
allows
you to port MySQL to oracle. I think it comes from Oracle.
Hope that helps,
Anton Kokarski
-Original Message-
From: Gregory [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 08, 2001 12:15 PM
To: [EMAIL PROTECTED]
Subject: How can I get insert_id from DBD::Oracle?
I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with
DBD::mysql I could get the id of inserted row with
DBH->{mysql_insertid}.
Is there any way to do the same thing with Oracle (DBD::Oracle)?
Thanks. Grisha.
RE: How can I get insert_id from DBD::Oracle?
Greg,
Look around on mysql.com I've seen a mentioning of the utility that allows
you to port MySQL to oracle. I think it comes from Oracle.
Hope that helps,
Anton Kokarski
-Original Message-
From: Gregory [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 08, 2001 12:15 PM
To: [EMAIL PROTECTED]
Subject: How can I get insert_id from DBD::Oracle?
I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with
DBD::mysql I could get the id of inserted row with DBH->{mysql_insertid}.
Is there any way to do the same thing with Oracle (DBD::Oracle)?
Thanks. Grisha.
Re: How can I get insert_id from DBD::Oracle?
There is no DBD::Oracle equivalent for retrieving the value of
insert_id. You must create a sequence within oracle, then retrieve its
value and assign it to a variable within your script. You can then use
that value when you execute your insert and later in your script.
Jim
At 12:14 PM 6/8/01 -0700, Gregory wrote:
>I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with
>DBD::mysql I could get the id of inserted row with DBH->{mysql_insertid}.
>Is there any way to do the same thing with Oracle (DBD::Oracle)?
>
>Thanks. Grisha.
#!/usr/local/bin/perl
push@J,[($")x70]for 0..20;sub p{$J[$q][$p]=$_;print"\e[H\e[J";print@$_,$/for@J
;$J[$q][$p]=$"}sub f{sprintf"%.f",pop}for('Just another Perl hacker.'=~/./g){$
t=/ /and$J[20][$X++]=$_,next;{$x=70+$t*($X-70)*.8;$y=20-63.25*$t+50*$t**2;last
if$x<$X;$p=f$x;p$q=f$y;$t+=.1;select$,,$,,$,,.1;redo}$J[20][$X++]=$_}p
How can I get insert_id from DBD::Oracle?
I'm migrating from MySQL to Oracle. When I'm executing "INSERT... " with DBD::mysql I
could get the id of inserted row with DBH->{mysql_insertid}. Is there any way to do
the same thing with Oracle (DBD::Oracle)?
Thanks. Grisha.
