Handling case of field names in multiple db's

[mflanagan]

(Bottom line: I think what I'm looking for is an easy way of changing the case of key values in an array.)

I've got code that I'm trying to make agnostic about the underlying database system I'm using. That is, I want the code to work whether the underlying db is MS SQL, MySQL, Oracle, etc. I'm using the PEAR DB package, and it's great for what I'm trying to do...except in one area. I get the returned row fields in an associative array, where the element key is the field name and the element value is the field value. The problem is that MS SQL returns field names in mixed case (actually, it returns the field names in the case the user used to define the field names in MS SQL in the first place), while Oracle returns the field names all caps. So, if I have the same db on MS SQL and on Oracle, I can't easily access the field values.

For example:

$row = $result->fetch_row(DB_FETCHMODE_ASSOC);

Now, if I print_r($row), I see something like:

Array (
   [My_Id] = 1;
   [Name] = "Fred Flintstone";
)

If I run this under an Oracle db, I might get this, instead:

Array (
   [MY_ID] = 1;
   [NAME] = "Fred Flintstone";
)

So my code cannot say something such as:

$id = $row["My_Id"];

That would work for the MS SQL version, but not for the Oracle version. I certainly don't want to say something like:

if ($IsMsSql) $id = $row["My_Id"];
else $id = $row["MY_ID"];

I really don't have control of how the users define the case of their field names, otherwise I could tell them to upper-case them all the time. But that might not work, if a different set of db routines that DB uses returns field names in all lower case.

I could do the following, but I'm not sure I want to take the efficiency hit (maybe it isn't so bad; I don't know):

foreach ($row as $key => $value) {
   $row [strtoupper($key)] = $value;
   unset($row[$key]);
}

Any ideas? Thanks.

Michael

[mflanagan]

I've come upon constants, so I can do something like:

$define("USE_MSSQL", TRUE);

if (defined("USE_MSSQL") && USE_MSSQL) {
   define("MY_ID", "My_Id");
   define("NAME", "Name");
else if (defined("USE_ORACLE") && USE_ORACLE) {
   define("MY_ID", "MY_ID");
   define("NAME", "NAME");
}
else {
   die ("You must define a database type");
}

And then, to use it:

$id = $row[MY_ID];

Okay, that works. Am I missing an easier way to do this?

Mike