Home » Database » DB » Bug #679

[2004-02-04 12:37 UTC] jmourot at sdis69 dot fr

Description:
------------
when the query as got alias in the select clause and the alias contains
spaces , the functions removes all quotes and then oracle(8i/9i) returns an
error

something like "select idcol as "Columm Number1" is rewriten as Select
Column Number 1 by the function and then produces an error

Reproduce code:
---------------
A simple workaround i've found is to add " around the field name in
oci8.php/modifyLimitQuery
$fields = implode('","', $cols);
replaced by
$fields ='"'.implode('","', $cols). '"';

Expected result:
----------------
SELECT field1, "field number 2" FROM (SELECT rownum as linenum,field1, "field number 2" FROM (select dbfield1 as "field1", dbfield2 as "field number2" from sometable ) WHERE rownum <= 9) WHERE linenum >= 1

Actual result:
--------------
SELECT field1, field number 2 FROM (SELECT rownum as linenum,field1, field number 2 FROM (select dbfield1 as "field1", dbfield2 as "field number2" from sometable ) WHERE rownum <= 9) WHERE linenum >= 1

[2004-02-09 09:24 UTC] jmourot at sdis69 dot fr

Thanks for the quick fix but it seems there is a regression :
if a "classic" field without space is used with an alias (like select fielda as "Field 1" it produces an error)

Adding quotes for all aliases seems to solve the problem

Hope this helps

SELECT "Date de Repas", "Centre de Commande", HID_CENTRECOMM, HID_CENTRERESTAU, "Lieu de Restauration", Etat, Nb, IDENTETECOMMANDE FROM (SELECT rownum as linenum, "Date de Repas", "Centre de Commande", HID_CENTRECOMM, HID_CENTRERESTAU, "Lieu de Restauration", Etat, Nb, IDENTETECOMMANDE FROM (select DTCOMMANDE as "Date de Repas",lbcentrecomm as "Centre de Commande" ,idcentrecomm as hid_centrecomm,idcentrerestau as hid_centrerestau, lbcentrerestau as "Lieu de Restauration", Etat as "Etat" ,nombre_ligne as "Nb",identetecommande AS identetecommande from v_gpr_listecommandes WHERE ( (idcentrerestau in ('10','11','23','6') or idcentrecomm in ('10','11','23','6')) AND DTCOMMANDE between TO_DATE('19/02/2004','DD/MM/YYYY') and TO_DATE('20/03/2004','DD/MM/YYYY') ) ORDER BY dtcommande ) WHERE rownum <= 9) WHERE linenum >= 1
Notice: PEARSQLDataListSource::do_query() - query failed : DB Error: no such field in e:\htdocs\sdis\fwk\html\phphtmllib\widgets\data_list\PEARSQLDataListSource.inc on line 98

[2004-02-10 07:56 UTC] jmourot at sdis69 dot fr

To put it in a nutshell it seems thats all fields aliases needs quotes or the query fails
If the primary alias name original query contains quotes,all references to that alias must contain quotes too :

Original query : select field1 as "Field number 1",field2 as "numfield" from
footable

This query altered by modifylimitquery (fails) :
SELECT "Field number 1",numfield
FROM (SELECT rownum as linenum, "Field number 1",numfield
FROM (select field1 as "Field number 1",field2 as "numfield" from
footable) WHERE rownum <= 9) WHERE linenum >= 1

this one would be OK :

SELECT "Field number 1","numfield"
FROM (SELECT rownum as linenum, "Field number 1","numfield"
FROM (select field1 as "Field number 1",field2 as "numfield" from
footable) WHERE rownum <= 9) WHERE linenum >= 1

Hope this helps
and thanxs for the package

Regards.

[2004-02-10 09:31 UTC] jmourot at sdis69 dot fr

Hello i think we are going in a wrong way : the problem is when alias names contains " " : I know using "" is useless here for the field <tzar> but it could be
So i took your piece of code and tested it :
$query = 'SELECT Cf as "char field", bar as tzar
FROM FOO'; is OK
$query = 'SELECT Cf as "char field", bar as "tzar"
FROM FOO';

fails after beeing modified by modifylimitquery (that's the probleme i was still talking about in the last post)

We are under Oracle9i Release 9.2.0.1.0 - Production / JServer Release 9.2.0.1.0 - Production on Win2K

Regards

[2004-02-11 08:57 UTC] jmourot at sdis69 dot fr

Thanks for the great job you do !