0

A little background: I have a perl script which is performing a number of operations inside of a loop.

Inside of the loop, I create a prepared statement like so:

  // inside loop
  my $statement = "select xxxx from zzzzzz where $foobar;";
  my $query = $connect->prepare($statement);
  $query->execute();

$foobar is updated to a new value everytime the loop completes, which is why i want to change the query every run, however I am getting an error when I try to do so.

When the script is run I get this error:

  DBD::Pg::st execute failed: ERROR:  prepared statement "xxxxxxxxxxxxx" already exists at

Because I am passing a variable which is updated every time the loop runs, and assuming I need to use this prepared statement, how do I get around this error?

Thank you for your time.

Edit:

For future people having this problem, check this out here :

I was trying to get 

my $sth = $dbh->prepare('select interval ?');

http://gborg.postgresql.org/pipermail/dbdpg-general/2006-January/001972.html

http://gborg.postgresql.org/pipermail/dbdpg-general/2006-February/002007.html

list some workarounds: either turning off the server-side prepares through the pg_server_prepare flag or using eg. '?::interval' instead of 'interval ?'

Beau Bouchard
  • 835
  • 11
  • 28
  • What versions of *everything* (perl, Postgres libs, Postgres server, DBI, DBD::Pg) are you using? This may be a [bug in a recent DBD::Pg](http://comments.gmane.org/gmane.comp.bug-tracking.request-tracker.user/65741). – pilcrow Jun 22 '12 at 14:53
  • Just to be clear, the 'interval ?' issue is a *syntax error* generated by the Pg backend when asked to prepare a statement, behavior which was introduced around 2006 when the DBD driver introduced true server-side preparation. The 'prepared statement already exists' issue which opened this question has a different root cause. – pilcrow Jun 22 '12 at 16:50

1 Answers1

4

Move your prepared statement outside of the loop. If you have a variable that needs to be passed to the prepared statement, you'll use the ? marker to pass in the variable to the prepared statement. Here's an example:

my $stmt = $dbh->prepare('SELECT * FROM table WHERE id = ?');
foreach ... {

    my $var = <something>;
    $stmt->execute($var);
}

Here, the prepared statement is created once, and the ? is used to pass in variables. When you call execute, you pass in the variable as parameters.

Joel
  • 3,435
  • 2
  • 23
  • 33
  • 1
    Can the same thing be done with more than one variable? Such as my $stmt = $dbh->prepare("SELECT * FROM table WHERE id = ? AND name ='?'"); $stmt->execute($var1,$var2); ?? – Beau Bouchard Jun 22 '12 at 14:48
  • 1
    @BeauBouchard Yep, and that's exactly how you do it. `$var1` would be matched to `id` and `$var2` would be matched to `name` in your example. – Joel Jun 22 '12 at 14:50
  • well you got me over that hump, thanks, marking your answer as correct, although now I get DBD::Pg::st execute failed: ERROR: syntax error at or near "$1" LINE 1: SELECT $1 + interval '5 seconds' ^ at ./xxxscriptxxxx.pl line 516. I am passing in a string, and i am not sure how to use "'" marks. but thank for all your help – Beau Bouchard Jun 22 '12 at 15:03
  • or I get DBD::Pg::st execute failed: called with 1 bind variables when 0 are needed at ./tedect_dev.pl line 516. when I do $connect->prepare("SELECT timestamp '?' + interval '5 seconds'"); – Beau Bouchard Jun 22 '12 at 15:08
  • Found may answer :D thank for all your help http://docstore.mik.ua/orelly/linux/dbi/ch05_03.htm $sth->bind_param( 1, "WHERE name = 'Avebury'" ); – Beau Bouchard Jun 22 '12 at 15:12
  • I'm suprised binding a parameter as "where name = 'Avebury'" works at all as it changes the SQL. Normally you can only bind a column value. i.e. it would be better as "select x where y = ?" and then you bind a value for y. – bohica Jun 22 '12 at 16:29
  • 1
    You don't use quotes when you use bound parameters. `$dbh->prepare(" SELECT * FROM table WHERE id = ? AND name = ? ");` – Dondi Michael Stroma Jun 22 '12 at 17:31