MySQL to DB2 through ADOdb PHP

I'm trying to port a small PHP application to DB2 from MySQL. The application connects to a MySQL database through ADOdb. I was successful in connection to the DB2 database through ADOdb, but I wasn't so successful in executing the SQL queries. The queries needed to be modified to include quotation (" ") marks around table names to execute. Is their any workaround in ADOdb 开发者_如何学Pythonfor this? It's a bit tedious to modify each query (which actually defeats the purpose of using ADOdb in the first place).

Thanks!

In DB2, by default, schema, table and column names are not case sensitive. When you issue the statement:

create table myid.test (
   c1 int
   c2 int
);

DB2 folds the schema, table and column names into upper case. Therefore, if you look in the system catalog, you'll see that the table is called MYID.TEST and has columns C1 and C2.

DB2 folds all queries into upper case as well (by default). So, when you query this table, the following statements are identical:

select c1, c2 from myid.test

SELECT C1, c2 from MYID.TEST

SELECT c1, C2 from MyID.Test

However, DB2 can use case sensitive names: If you quote the schema/table/column names in the definition, then DB2 will use the exact strings:

   create table "MyID"."Test" (
      c1 int,
      "C2" int
   );

In this case, you'll see the mixed case schema/table/column names in the system catalog.

This has the unfortunate (and painful) side effect of REQUIRING that you quote your schema/table/column names in all of your queries, DML and DDL.

Using mixed case names is NOT best practice.

The best solution would be to re-create your tables without the case-sensitive names (i.e. don't put schema/table/column names in quotes.

This will eliminate your need to override everything with ADODB. It's possible that there is some workaround for ADODB, but the pain will still exist for anyone else.

It's a bit tedious to modify each query (which actually defeats the purpose of using ADOdb in the first place).

While it certainly may be tedious, you'll have to do it. JDBC provides the equivalent functionality in Java, and there you have to write SQL that is specific to your particular database. This is just how it works with database development. Unless you use some sort of abstraction layer like Hibernate (a Java ORM) to hide the specifics of the SQL from you, you'll have to tweak it to run on a different database.

Be glad that the only thing you've encountered so far is adding a few quotation marks. People frequently end up having to rewrite most of the query when converting a complex query from one server to another.