type casting for DBI SQL statement doesn't work

S

stroncococcus

Hy there!

I am just writing a little script to put something into a postgresql
database table.
My prepare statement:
my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = (loop_links || ARRAY[?]) WHERE mat_accession=?");

But whenever I execute this, with
$mature_link_update->execute($loop_table_id, $mat_accession);
or
$mature_link_update->execute(int($loop_table_id), $mat_accession);
then I get this error:
"operator does not exist: integer[] || text[]
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts."

So the variable $loop_table_id is interpreted as text everytime.
What can I do, that it is handled as integer?

Many thanks in advance,
Kai
 
S

stroncococcus

This time I tried it with PostgreSQL type casting ... but I also can't
get those to work.
I know this is not anymore Perl specific, but perhaps someone knows a
solution here, too.

my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = loop_links || CAST (ARRAY[?] AS interger[] WHERE
mat_accession=?");
or
my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = loop_links || (CAST (? AS interger[]) WHERE
mat_accession=?");

gives me the error:
"ERROR: syntax error at or near "WHERE" at character 75"

Anyone here, who knows how to use those CAST's in SQL?
 
D

DJ Stunks

stroncococcus said:
the variable $loop_table_id is interpreted as text everytime.
What can I do, that it is handled as integer?

the piece of documentation you need to read is outlined in

perldoc DBI

under the heading Statement Handle Methods. Read the section on
bind_param() - specifically "Data Types for Placeholders".

-jp
 
D

DJ Stunks

stroncococcus said:
This time I tried it with PostgreSQL type casting ... but I also can't
get those to work.
I know this is not anymore Perl specific, but perhaps someone knows a
solution here, too.

my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = loop_links || CAST (ARRAY[?] AS interger[] WHERE
mat_accession=?");
or
my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = loop_links || (CAST (? AS interger[]) WHERE
mat_accession=?");

-ITYM 'integer'------------------------------^

this would appear to be a spelling mistake, but I know nothing of
Postgres.

-jp
 
S

stroncococcus

Thanks a lot for the help ... I will look into the bind_param thing.
my $mature_link_update = $dbh->prepare("UPDATE mature_mirna SET
loop_links = loop_links || (CAST (? AS interger[]) WHERE
mat_accession=?");

-ITYM 'integer'------------------------------^

this would appear to be a spelling mistake, but I know nothing of
Postgres.

Uups ... you are right ... but after correction it still fails with the
same error message.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top