Error "cannot insert multiple commands into a prepared statement"

pdo, php, postgresql, postgresql-8.4

Solution

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

Problem

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()); } ```

Original source