How to execute mysql script with variables using PHP::PDO?

Question

I am unable to execut long script the pdo throws an exception:

SQLSTATE[HY000]: General error

If I submit script which does not contain variables it runs w/o problem. The same script runs on phpmyadmin interface.

Here is my code snippet:

 try {
 $dsn = "mysql:host=" . DB_SERVER . ";dbname=" . DB_DEFAULT;
 $db = new PDO($dsn, DB_USER, DB_PASS);
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $q = $db->query($query);
 if (!$q) {
 echo $db->errorInfo();
  } else {
        $rows = $q->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (PDOException $e) {
    var_dump($e);
 }

Here is some test which does not execute by PDO:

SET @ra_LMC:=80.9;
SELECT @ra_LMC;

How I should execut with pdo the multi line scripts?

Thanks
Arman.

The query fails if any "set" operator is exist.as written in example. — Arman
– Arman, Commented Jan 13, 2011 at 19:37

cytrinox · Accepted Answer · 2011-01-13 20:56:46Z

14

PDO does not allow the execution of multiple statements in one query() request. But your @ra_LMC variable should be visible in the current connection, so you can put your second line (SELECT) into a new query() call.

To read a whole script, you have to parse the file and run each statement with a call to query().

edited Jan 13, 2011 at 20:56

answered Jan 13, 2011 at 20:43

cytrinox

1,9665 gold badges28 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gen_Eric · Accepted Answer · 2011-01-13 22:02:26Z

7

PDO can only execute one statement at a time. You can ether run the SET and SELECT as 2 separate statements. Or you can set the variable using FROM.

SELECT @ra_LMC FROM (SELECT @ra_LMC:=80.9) q

answered Jan 13, 2011 at 22:02

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Collectives™ on Stack Overflow

How to execute mysql script with variables using PHP::PDO?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related