PDO PostgreSQL binding error

Question

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.

Kirill Titov · Accepted Answer · 2013-02-28 13:08:04Z

5

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.

answered Feb 28, 2013 at 13:08

Kirill Titov

2,1095 gold badges21 silver badges35 bronze badges

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

2 Comments

BadPirate Over a year ago

Can you provide more information on how this is done in PHP and why it is needed?

yodabar Over a year ago

I am curious about this feature, too. I had a similar binding problem (stackoverflow.com/questions/50236966/…) and tried with both :field::int / :field::integer and :field::text, but it did not work. No reference on it in the official documentation.

Jonast92 · Accepted Answer · 2013-02-28 12:17:00Z

0

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).

answered Feb 28, 2013 at 12:17

Jonast92

4,9671 gold badge20 silver badges33 bronze badges

1 Comment

Kirill Titov Over a year ago

The problem is I'm getting error about inconsistent types when I'm trying to update table like this — set field_name = :field_name.

Collectives™ on Stack Overflow

PDO PostgreSQL binding error

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related