Re: Is this a DBD::Pg problem or Postgres problem?

[Peter J. Holzer]

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!"