Bug #6795

portability problem with case and alias

Details

Request #6795	portability problem with case and alias
Submitted	2006-02-16 14:03 UTC
From	felix_ant at gva dot es
Assigned	danielc
Status	Wont fix
Package	DB
PHP Version	4.3.9
OS	Linux
Roadmaps	(Not assigned)

Comments

[2006-02-16 14:03 UTC] felix_ant at gva dot es

Description:
------------
Hello!

We are developing an application that uses PEAR::DB. We are testing the portability to different Databases. We are testing with Oracle and PostgreSQL. We have found a problem: Oracle returns the result of a query with the column names in upper case and PostgreSQL with lower case except the columns that have column alias.

What we want is the returned result to be always in uppercase or in lowercase (whatever the DB were) but always respecting the column alias case mode if this alias exists.

Are there any type of portability option that can solve our problem?

Test script:
---------------
/*OPTION 1*/
$select = "select cpro,cmun as \"codigoMunicipio\", dmun as \"descMunicipio\" from tcom_municipios";
$origen = DB::connect($dsn);
$res = $origen->getAll($select,DB_FETCHMODE_ASSOC);

/*OPTION 2*/
$select = "select cpro,cmun as \"codigoMunicipio\", dmun as \"descMunicipio\" from tcom_municipios";
$origen = DB::connect($dsn);
$origen->setOption('portability', DB_PORTABILITY_LOWERCASE);
$res = $origen->getAll($select,DB_FETCHMODE_ASSOC);

Expected result:
----------------
Result:
In PostgreSQL:
Array ( [cpro] => 46 [codigoMunicipio] => 903 [descMunicipio] => SAN ANTONIO)
In Oracle:
Array ( [cpro] => 46 [codigoMunicipio] => 903 [descMunicipio] => SAN ANTONIO)

Actual result:
--------------
/*OPTION 1*/
In PostgreSQL:
Array ( [cpro] => 46 [codigoMunicipio] => 903 [descMunicipio] => SAN ANTONIO)
In Oracle:
Array ( [CPRO] => 46 [codigoMunicipio] => 903 [descMunicipio] => SAN ANTONIO)
The result is not correct in Oracle because our application was waiting columns named cpro, codigoMunicipio and descMunicipio.

/*OPTION 2*/
In PostgreSQL:
Array ( [cpro] => 46 [codigomunicipio] => 903 [descmunicipio] => SAN ANTONIO)
In Oracle:
Array ( [cpro] => 46 [codigomunicipio] => 903 [descmunicipio] => SAN ANTONIO)
The result is not correct because our application was waiting columns named cpro, codigoMunicipio and descMunicipio.

[2006-02-17 09:19 UTC] felix_ant at gva dot es

Hello!

First, thanks for your time.

About the question I sent you yesterday. We are programming a Framework for developping corporative applications. As I told you we are using PEAR:DB for accesing to all different kind of databases that we have.

So, reading your answer I undestand that if we want an application with PEAR:DB that works with both DB Oracle or Postgres we only have two options:

1) Fixing the portability to lowercase and make our applications expecting to receive variables only in lowercase
2) Not choosing any option of portability and using always alias in every field of the query.

Is that correct?

If so, it is not very appropiate for our applications.This would limit the use of our framework a lot because we would have to say to our programmers that they can only expect column names in lowercase (now we permit them to use what they want).

We are using now the second option but we do not like it because it is only a recommendation to the programmers and we don´t parsing the queries to check that all the columns have alias.

Do we have any other options?