Skip to content
Advertisement

How to make Oracle keep the case of identifiers as they appear in the query?

I want to use a php library that uses PDO. And I want to use an Oracle database with it.

The problem is that authors of that library use unquoted identifiers in their queries, e.g.:

$statement = $pdo->prepare('SELECT * from some_table where some_column = :some_column');
$statement->execute(['some_column' => 'some value']);
$result = $statement->fetch(PDO:FETCH_ASSOC);
return $result['some_other_column'];

In this case, Oracle converts the case of identifiers to UPPER, but authors assume the case is lower, therefore an undefined index php error occurs.

How do I bypass this?

UPD. I tried to use lower case when creating database objects, e.g.:

CREATE TABLE "some_table"
(
    "some_column"       VARCHAR(10),
    "some_other_column" VARCHAR(10)
);

– but in this case, Oracle raises an error when executing the SELECT statement above, because identifiers in it are converted to UPPER case, while object names are in lower case.

UPD 2.

I shouldn’t edit the source code of the third party library – have to configure somehow my environment and connection: Oracle, PHP, PDO.

Advertisement

Answer

I found a solution for my case, thanks to Your Common Sense‘s comment.

When initializing the library, I pass it a PDO object – I’ve configured it to always use lower case, by the following code:

$options = [
    PDO::ATTR_CASE => PDO::CASE_LOWER,
];
$connection = new PDO($dsn, $username, $password, $options);
init3rdPartyLibrary($connection);

Because of this, the keys of the $result variable returned by $statement->fetch() are now lowercase and therefore the library works as expected by its authors.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement