All the Perl that's Practical to Extract and Report
use Perl Log In
jdavidb (1361)
(email not shown publicly)
http://voiceofjohn.blogspot.com/
J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.
Did you know you could do this?
Problem: write a routine for Oracle using DBI that inserts a record into a table, using an Oracle sequence to generate the new ID for the record, and returns the record. The catch? Use only one SQL statement.
Solution:
sub new_record
{
my($dbh, $field1, $field2) = @_;
my $sql = <<"EOF";
INSERT INTO mytable
(id, field1, field2)
VALUES
(mysequence.NEXTVAL, ?, ?)
RETURNING id
INTO ?
EOF
my $id;
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $field1);
$sth->bind_param(2, $field2);
$sth->bind_param_inout(3, \$id, 1024);
$sth->execute;
return $id;
}
The key is that RETURNING
Updated 5/24/2004: fixed bug; thanks djberg
--Donald E. Knuth
Minor error (Score:2)
Re:Minor error (Score:2)
Yes; that's correct. Thanks for catching it! :)
J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers