MySQL vs Firebird: accessing query columns in PHP with PDO

Question

From what I understood, using PDO should be the same result regardless of what DB I am using. I tested this in the following code, which I have connected to two separate DB's.

$sth = $pdo->query('SELECT * FROM posts');
while($result = $sth->fetch(PDO::FETCH_BOTH)){
    echo $result[1] . '<br>';
};

MySQL DB:

$dsn = 'mysql:host=' . $server . ';dbname=' . $dbname;
$pdo = new PDO($dsn, $username, $password);

Firebird DB:

$dsn = "firebird:dbname=" . $server . ":" . $dbname;
$pdo = new PDO($dsn, $username, $password);

The MySQL connection worked fine, while the Firebird connection worked only with numbered Arrays - FETCH_NUM, and FETCH_BOTH when using index positions. Is this how its supposed to be or am I doing something wrong with my Firebird connection? I will need to work with the Firebird DB in the future, so this really frustrates me. Thank you for all comments.

Maybe this helps: https://db-engines.com/en/system/Firebird%3BMySQL — Ingus, Jan 14 '19 at 11:50
You only show the code that works. Show verbatim the code that does not work and with verbatim error it generates. https://www.chiark.greenend.org.uk/~sgtatham/bugs.html — Arioch 'The, Jan 14 '19 at 12:16
Also check this: https://stackoverflow.com/questions/5428262/php-pdo-get-the-columns-name-of-a-table - maybe you just specify unexpected names ? — Arioch 'The, Jan 14 '19 at 12:19
FETCH_ASSOC works great also with Firebird. Do a var_dump of your fetch results and analyze it. Maybe field char case is different, and you need to access it in a right way, or normalize them using PDO::ATTR_CASE => PDO::CASE_LOWER. — Marcodor, Jan 14 '19 at 15:56
Sorry for not displaying the error message. When used with ASSOC, it shows: Notice: Undefined index: title. When with OBJ - Notice: Undefined property: stdClass::$title. I tried to play with charsets, but that didnt change anything. — Joroe, Jan 15 '19 at 07:09
var_dump of $result with FETCH_ASSOC shows pretty much the same thing with MySQL and Firebird. Its just that Firebird, for some reason, doesnt want to read anything else but indexes when trying to access the individual data from these fetched arrays. — Joroe, Jan 15 '19 at 07:20
@Joroe, have you tried `$result['TITLE']` ? If yes, and does not work, post here full var_dump($result) — Marcodor, Jan 15 '19 at 21:00
@Marcodor it worked! Thank you for your help. Didnt occur to me that FB required it all in uppercase. — Joroe, Jan 16 '19 at 08:01
Okay, glad you made it to work :) to get field names lower-cased, read one more time my previous comment. — Marcodor, Jan 16 '19 at 10:17
@Marcodor yea the CASE_LOWER flew completely over my head. Whe I saw your comment I tried playing with some setAttribute methods, but this didnt even occur to me. Sorry for being this clueless... — Joroe, Jan 16 '19 at 10:31

Marcodor · Accepted Answer · 2019-01-16T11:50:59.993

In Firebird, by default, mainly from historical reasons, unless you use double quotes on object names (field names, tables, etc...), they are uppercase-d and stored internally in uppercase.

Accordingly, the column names you receive in result set are in uppercase, so you should address them in upper-case like $row['FIELD_NAME'].

Alternatively, in PHP, PDO driver have a special flag used on connection, PDO::ATTR_CASE => PDO::CASE_LOWER/NATURAL/UPPER that adjust the needed case internally for you.

For example:

$source = $d['kind'].':'.'dbname='.$d['host'].':'.$d['base'].';charset='.$d['charset'];
$options = $d['options'] + [
    \PDO::ATTR_CASE => \PDO::CASE_LOWER,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
    ];
$connection = new \PDO($source, $d['user'], $d['password'], $options);

Firebird is not alone doing this. Oracle also stores by default metadata in uppercase. Some DBMSes have options, others in lower-case by default.

It is not just historical reasons, the behavior for unquoted identifiers (and the expectation they are stored uppercase in metadata) derives from the ISO-9075 SQL standard (case-normal form in section 5.2 of ISO-9075-2, specifically syntax rules 24 and 25 in ISO-9075-2:2016). — Mark Rotteveel, Jan 16 '19 at 18:10
First SQL standard appeared in '87, about 10 years later than first Oracle release. InterBase, parent of Firebird, have also roots in '84. dBase even earlier. All of them used caps for object names. The reasons was to distinguish keywords on monochrome/uni-font displays without any code highlight and low computer power at that times, too costly to make internal storage case insensitive. — Marcodor, Jan 17 '19 at 10:16

MySQL vs Firebird: accessing query columns in PHP with PDO

1 Answers1