Thank you! This is a pattern I've used in a bunch of applications with Oracle,
so this helps me fix a bunch of stuff. (Like this app that got pushed live
without adequate testing.)
On Oct 16, 2023, at 6:35 AM, Peter J. Holzer <h...@hjp.at> wrote:
On 2023-10-15 23:26:03 +0000, Johnson, Bruce E - (bjohnson) wrote:
I’ve move an application to Postgres for the database and am having a problem
with using named parameters.
My code:
my $csr_newinv =$lda->prepare("insert into inventory (inv_id, instid, av_id,
vials, volume, expiration_date, update_date) values (nextval(invid_seq),:INST,
:AV,:VI,:VO, to_date('$exp','MM-DD-YYYY'), now()) returning inv_id as :NEWID");
my $newid;
$csr_newinv->bind_param(':INST',$instid);
$csr_newinv->bind_param(':AV',$av_id);
$csr_newinv->bind_param(':VI',$vials);
$csr_newinv->bind_param(':VO',$volume);
$csr_newinv->bind_param_inout(':NEWID',\$newid,"SQL_NUMERIC");
$csr_newinv->execute();
That doesn't look correct. PostgreSQL returns the result of "...
returning ..." as a result set.
So you would normally do something like
my $csr_newinv = $lda->prepare("insert ... returning inv_id")
$csr_newinv->execute()
$result = $csr_newinv->fetchrow_hashref();
$newid = $result->{inv_id};
[Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746] [client
10.139.39.203:49782] AH01215: DBD::Pg::st execute failed: ERROR: syntax error
at or near "$5": /home/allwebfiles/perl/edit_inst_inv2.pl, referer: https://
avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
[Sun Oct 15 16:01:01.059943 2023] [cgi:error] [pid 814746:tid 814746] [client
10.139.39.203:49782] AH01215: LINE 1: ...te('10-15-2023','MM-DD-YYYY'), now())
returning inv_id as $5: /home/allwebfiles/perl/edit_inst_inv2.pl, referer:
https://avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
Is $5 referring to :NEWID ?
Yes. And a parameter isn't allowed in this position, only an identifier
(column alias).
based on my reading of the Postgres Insert syntax I think it should be
correct..
I think something like that works in PL/PgSQL. But in SQL you need to
fetch the result.
(also, I do not know if the error logging is a DBD::Pg thing or Postgres thing,
but it very hard to follow these kinds of errors compared to DBD::Oracle )
Having nested output from three different systems in the same line is a
bit confusing, yes. It becomes clearer if you know where the boundaries
are:
Apache: [Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746]
[client 10.139.39.203:49782] AH01215:
Perl/DBD: DBD::Pg::st execute failed:
Postgres: ERROR: syntax error at or near "$5"
Perl/DBD: /home/allwebfiles/perl/edit_inst_inv2.pl,
Apache: referer: https:// avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
Also, PostgreSQL error messsages contain context, so you know that the
LINE 1: ...te('10-15-2023','MM-DD-YYYY'), now()) returning inv_id as $5
tells you wich line the error occured in (which is very handy on long
multi-line SQL queries) and the next line (which you didn't quote) shows
you where in the line the error was (with an ^ character pointing at
$5).
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
External Email
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
