All SELECTs work properly. However, any UPDATE or INSERT, even if it works when executing it in the mysql query browser, while being logged in with the same user as PHP connects to the server, fails when executed by PDO.

As an example:

SELECT *  FROM Projects WHERE ssdUserID = :ssdUserID;

works properly all the time, whereas

INSERT INTO Projects SET ssdUserID = :ssdUserID, json = :json;

always fails when executed from PHP, although I do addslashes() on all parameters.

Th开发者_如何学Pythone way I call PDO is via prepared statements:

$connection = new PDO("mysql:host=$mysqlServer;
    dbname=$mysqlSchema", $mysqlUser, $mysqlPassword);;
$statement = $connection->prepare($sql);
$result = $statement->execute($parameters);

$result is always false, and although a commit happens at the end (for debugging purposes, for the moment), there's no update or insert to be seen in the database.

I enabled the mysql trace log, and it seems the sql doesn't even get sent to the mysql server - all there's to be seen in the trace log is the begin and the end of the transaction, with nothing in between.

Since searches work, it is obvious that PDO is able to establish a connection with the database. Also, since searches go through with the same code as the one that doesn't work for write statements, I can't think of any other place to have problems than PDO itself.

One issue that might be of importance is that the column json in the example above is of type text. I already use PDO in another place, where I only have various VARCHAR cols, and there it works properly.

Does or did anybody ever have a similar problem? Can anybody help me with this?

Update:

After some digging and reading (http://www.php.net/manual/en/pdo.lobs.php) I switched to a different solution. I now bind both inpput parameters and output columns. I'll leave the initial text in place, however, so ppl doing the same initial mistake might find out easier than I did.

Binding the parameters is easy. I bind the output columns in a generic function:

01  private function __extractResultsEx($statement, $columns)
02  {
03      $data = array();
04      $msg = "query returned ".$statement->rowCount()." rows";
05      $receiverRow = array();
06      $i = 1;
07      foreach ($columns as $column => $type)
08      {
09          $receiverRow[$column] = NULL;
10          $statement->bindColumn($i++, &$receiverRow[$column], $type);
11      }
12      while ($statement->fetch(PDO::FETCH_BOUND))
13      {
14          $row = array();
15          foreach ($columns as $column => $type)
16              $row[$column] = $receiverRow[$column];
17          $data[] = $row;
18      }
19      return $data;
20  }

Columns are ID/PDO::PARAM_INT, userID/PDO::PARAM_INT and json/PDO::PARAM_LOB.

I don't know if line 09 is needed, only, after an initial attempt in which $receiverRow did have three elements in the end but none was initialized, I thought this might help - but it doesn't.

With this approach, the problem is that although in line 04 $statement says it has retrieved one row, execution never reaches inside the while loop (rows 14 - 17), since $statement-fetch() always returns false, no matter which PDO::FETCH_ constant I use (I tried PDO::FETCH_OBJ, PDO::FETCH_BOUND and PDO::FETCH_ASSOC, the other don't seem to make much sense for LOBs, and anyway the manual page only says about PDO::FETCH_BOUND for LOBs).

There's a bug documented in comments to the above mentioned manual page which says that it should be safe and OK to simply read the LOB out of the row. Nevertheless, my problem occurs before I even get to access the row data, although I seem to have done everything exactly as in the example code.

There's one improvement now, however. The mySQL trace shows the SQL I'm executing. Also, since line 04 says about the row I expect, I suppose I'm close to a solution - but I seem not to be able to nail it.

You have multiple problems:

Make sure $parameters is an array like this:

array('ssdUserID' => "val", 'json' => "{}");

Also, you don't need to call addslashes when use prepared statements. PDO does that for you.

To find the error you can add this (after your $statement->execute() call):

var_dump($connection->errorInfo());

PS: you have 1 too many semicolons on the new PDO(... line.

Can you try calling bindParam instead of passing parameters to execute? You may need to specify length for the column of type text.

$statement->bindParam(':ssdUserID',$user_id, PDO::PARAM_INT);
$statement->bindParam(':json',$json_str,  PDO::PARAM_STR,strlen($json_str) );
$result = $statement->execute();