Why Doctrine Param_str_array conversion is not working as expected?

Question

I'm trying to bind an array of strings into the where-in condition. I used the parameters conversions constants also. But it seems to be not working.

Following one is my query.

$buQuery = "SELECT bu.ID, bu.BUSINESS_NAME FROM business as bu WHERE bu.ID IN (:business)";

$buStmt = self::getConnection($this->entityManager)->prepare($buQuery);

$buStmt->bindValue("business", $business, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

$buStmt->execute();

$responseData = $buStmt->fetch();

return $responseData;

and the array I'm passing for the in condition is (I have printed this array from the parameters list and copied it over here.)

$business = Array ( 0 => 'mobile', 1 => 'iot' );

The error the doctrine throws me is:

An Exception was thrown while handling: An exception occurred while executing SELECT bu.ID, bu.BUSINESS_NAME FROM business as bu WHERE bu.ID IN (:business) with params "[["mobile","iot"]]:"

Notice: Array to string conversion

I have noticed the array is not getting converted properly. I have referred the following links, but nothing helped me.

Stackoverflowlink1 Stackoverflowlink2 Doctrinelink1

Note: I have used the "PARAM_INT_ARRAY" also. And also I tried "array_values" while passing the array as parameter in the bind statement.

could you try to rename `:business` bind name to something that probably never used, like `:businessBindValue` — Majesty, Jan 30 '19 at 07:40
@Lunin: It's noting to do with that. As I have tried that as well. It will result in the same error, even if I change the name of the bind value. — Kiran, Jan 30 '19 at 08:18

score 4 · Answer 1 · answered Jan 30 '19 at 11:09

According to official documentation, it's because

The parameter list support only works with Doctrine\DBAL\Connection::executeQuery() and Doctrine\DBAL\Connection::executeUpdate(), NOT with the binding methods of a prepared statement.

so you can bind arrays only using those functions, in your case

$stmt = $conn->executeQuery(
    'SELECT bu.ID, bu.BUSINESS_NAME FROM business as bu WHERE bu.ID IN (?)',
    array($business),
    array(\Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
);

score 0 · Answer 2 · answered Jun 08 '23 at 21:51

I got the same error but due to an other reason: array parameter conversion only works with ? placeholder but fails with named one (at least with DBAL version 2.13).

The original query was something like this

/** @var \Doctrine\DBAL\Connection $conn */
$stmt = $conn->executeQuery(
    'SELECT * FROM foo WHERE id IN (:ids)',  // ❌
    ['ids' => [3, 5, 17, 257, 65537]],
    [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY],
);

and it fails with the very same error « Warning: Array to string conversion ».

But it works with ? placeholder (with DBAL version 2.13):

/** @var \Doctrine\DBAL\Connection $conn */
$stmt = $conn->executeQuery(
    'SELECT * FROM foo WHERE id IN (?)',  // ✅
    [[3, 5, 17, 257, 65537]],
    [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY],
);

Why Doctrine Param_str_array conversion is not working as expected?

2 Answers2