CODEWITHSUNDEEP
phppostgresqlpdo
Kirill Titov
Kirill Titov

Reputation: 2109

PDO PostgreSQL binding error

I found that when I'm trying to run update query with params, I'm getting error

inconsistent types deduced for parameter

Maybe that's because the type of target field (character varying), everything works fine with text column type. But I don't want to change column type only because of this. Then I was told that I should pass params directly (using bindValue or bindParam, determining the type of each value) instead of sending params array to execute method.

But when I do so I'm getting error

ERROR: bind message supplies 0 parameters, but prepared statement "pdo_stmt_00000001" requires 1

The test code is

$Stmt = $DB->prepare("SELECT * FROM test_table WHERE test_field=:test_field");
$Stmt->bindValue(':test_field', 'test', PDO::PARAM_STR);
$Stmt->execute();
var_dump($DB->errorInfo());

So, as far as understand, binding does not work at all. Or I'm doing it wrong. But maybe there is a way of solving it?

I'm running PHP 5.4.12 with PostgreSQL 9.2.3, libpq 8.4.16.

Upvotes: 4

Views: 5397

Answers (2)

Kirill Titov
Kirill Titov

Reputation: 2109

Well, it seems that the only solution is to cast all text values to text like this:

update test_table set test_field = :test_field::text

Otherwise error about inconsistent types is occurring.

Upvotes: 5

Jonast92
Jonast92

Reputation: 4967

I do it this way:

$stmt = $DB->prepare("SELECT * FROM test_table WHERE test_field=:test_field");
$stmt->execute(array(":test_field" => 'test'));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if($result)
{
    return $result['id'];
    // Or whatever you're trying to get.
}
return null;

You just have to throw in an array of parameters in the execute function, no need to add a special line for the binding.

Tell me if it works for you (or not).

Upvotes: 0

Related Questions