Error "cannot insert multiple commands into a prepared statement"
Solution 1
I've got a tip at the mailing list to get rid of the temp. tables and it works for me:
select r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY HH24:MI') as day,
c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
u.id, u.first_name, u.avatar, u.female, u.city, u.vip > CURRENT_DATE as vip
from pref_rounds r, pref_cards c, pref_users u
where u.id = c.id and
r.rid = c.rid and
r.rid in (
select rid
from pref_cards
where stamp > CURRENT_TIMESTAMP - interval '1 day' and
id in (
select id
from pref_money
where yw = to_char(CURRENT_TIMESTAMP - interval '1 week', 'IYYY-IW')
order by money
desc limit 10) and
bid = 'Misere' and
trix > 0
)
order by r.rid, c.pos
Solution 2
Try this, begin a transaction and split your querys up, as your not inserting values from user input there is no need to prepare the query, also as your not expecting a result from any but the last one exec is fine. on the last one you can use query(). If an exception occurs then you can rollback the changes.
<?php
try {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
//Transaction
$db->beginTransaction();
$db->exec("create temporary table temp_ids (id varchar not null) on commit drop;");
$db->exec("insert into temp_ids (id)
select id
from pref_money
where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW')
order by money
desc limit 10;");
$db->exec("create temporary table temp_rids (rid integer not null) on commit drop;");
$db->exec("insert into temp_rids (rid)
select rid
from pref_cards
where stamp > now() - interval '1 day' and
id in (select id from temp_ids) and
bid = 'Мизер' and
trix > 0;");
//Commit changes before doing your select
$db->commit();
$sth = $db->query("SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY HH24:MI') as day,
c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
u.id, u.first_name, u.avatar, u.female, u.city, u.vip > CURRENT_DATE as vip
FROM pref_rounds r, pref_cards c, pref_users u
WHERE u.id = c.id and
r.rid = c.rid and
r.rid in (select rid from temp_rids)
order by rid, pos;");
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
# stuff a JSON object
}
} catch (Exception $e) {
//Transaction rollback
$db->rollback();
exit('Database problem: ' . $e->getMessage());
}
?>
Alexander Farber
/me/likes: Java, С#, Perl, PHP, JavaScript, PostgreSQL, Linux, Azure /me/speaks: German, English, Russian /me/learns: https://github.com/afarber/android-questions https://github.com/afarber/unity-questions https://github.com/afarber/ios-questions
Updated on July 18, 2022Comments
-
Alexander Farber almost 2 years
Using PHP 5.3.3, PostgreSQL 8.4.11, pgbouncer 1.3.4 (in
session
mode) on CentOS 6.2 I'm trying to execute several SQL commands and fetch the results by a PHP script.When I copy the commands from script to psql prompt they work flawlessly and return 12 rows:
But when I run from script I get the error:
SQLSTATE[42601]: Syntax error: 7 ERROR: cannot insert multiple commands into a prepared statement
Any help please?
Below is my failing PHP code, I tried using
$db->query()
instead of$db->prepare/execute
too:try { $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s', DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options); $sth = $db->prepare(" start transaction; create temporary table temp_ids (id varchar not null) on commit drop; insert into temp_ids (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; create temporary table temp_rids (rid integer not null) on commit drop; insert into temp_rids (rid) select rid from pref_cards where stamp > now() - interval '1 day' and id in (select id from temp_ids) and bid = 'Мизер' and trix > 0; SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY HH24:MI') as day, c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip > CURRENT_DATE as vip FROM pref_rounds r, pref_cards c, pref_users u WHERE u.id = c.id and r.rid = c.rid and r.rid in (select rid from temp_rids) order by rid, pos; commit; "); $sth->execute(); while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { # stuff a JSON object } } catch (Exception $e) { exit('Database problem: ' . $e->getMessage()); }
-
Alexander Farber almost 12 yearsI get the error:
Undefined table: 7 ERROR: relation "temp_rids" does not exist LINE 7: r.rid in (select rid from temp_rids)
(even if I disable the pgbouncer).