lamp2lapo

One of the major items to note when converting an application from using MySQL to Oracle is the way in which column names are handled. Generally MySQL returns column names lowercased and Oracle generally returns column names uppercased.

Notice the keyword in both cases being ‘generally’. As with most things to do with MySQL and Oracle everything is not always as easy as it seems. Take the following examples which shows the different behaviour on MySQL.

mysql> select * from test;
+------+-------+
| id   | thing |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |
|    5 | five  |
+------+-------+
5 rows in set (0.00 sec)

Column names are returned lowercased

mysql> select ID, thing from test;
+------+-------+
| ID   | thing |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |
|    5 | five  |
+------+-------+
5 rows in set (0.00 sec)

Column names returned as requested

mysql> select ID as iD, thing from test;
+------+-------+
| iD   | thing |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |
|    5 | five  |
+------+-------+
5 rows in set (0.00 sec)

Column names returned as requested

The behaviour in Oracle

SQL> select * from test;

        ID THING
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

Column names are uppercased

SQL> select ID, thing from test;

        ID THING
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

Column names are uppercased

SQL> select id as Id, thing as thing from test;

        ID THING
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

Column names are still uppercased

But hold on. What happens if I create my table a different way.

CREATE TABLE test2 (
    "Id"    number,
    "thing" varchar2(10)
)
/

SQL> select * from test2;

        Id thing
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

Mixed-case names are returned

SQL> select id, thing from test2;
select id, thing from test2
           *
ERROR at line 1:
ORA-00904: "THING": invalid identifier

Looks like problems

SQL> select id from test2;
select id from test2
       *
ERROR at line 1:
ORA-00904: "ID": invalid identifier

Oh no!

It looks like we have two options of how to use column names when porting applications to Oracle :-

1. Use uppercase names when using Oracle
2. Copy the returned result set to one using lowercase names
3. In times to come when PDO is more stable use:

   $dbh->setAttribute(PDO_ATTR_CASE, PDO_CASE_LOWER);

Below is an example of how to convert column names to lowercase

	$s = oci_parse($dbh, $query);
	if (oci_execute($s)) {
		$i = 0;
		$num_rows = 0;
		$new_row = array();
		while ($row = oci_fetch_object($s)) {
			$new_row = array();
			foreach ($row as $key => $value) {
				    $new_row[strtolower($key)] = $value;
			}
			$result[$num_rows] = (Object)$new_row;
		}
        oci_free_statement($s);
    } else {
		// If there is an error then take note of it..
		$se = oci_error($s);
		$ce = oci_error($this->dbh);
        // Record/display the error
    }