Bug #2319

joinAdd() SQL code doesn't run under Oracle 9.1

Details

Submitted	2004-09-13 09:54 UTC
From	cordero at di dot unito dot it
Assigned	alan_k
Status	Closed
Package	DB_DataObject
PHP Version	5.0.1
OS	Solaris / Oracle 9.1
Roadmaps	(Not assigned)

Comments

[2004-09-13 09:54 UTC] cordero at di dot unito dot it

Description:
------------
It seems that SQL code generated by joinAdd() function does not work under Oracle, which returns ORA-00905: missing keyword

Reproduce code:
---------------
/* DataObjects involved */
$stud_obj = new DbClass_T_ECDL_STUDENTE_diplomati();
$skill_obj = new DbClass_ECDL_SKILLSCARD_Diplomati();
/* Building query */
/* NOTE: foreign key ECDL_SKILLSCARD_DIPLOMATI.matricolastudenti matches primary key T_ECDL_STUDENTE_diplomati.matricola */
$stud_obj -> joinAdd($skill_obj);
/*columns selection */
$stud_obj -> selectAdd();
$stud_obj -> selectAdd("nome,cognome,numero,tipo,descrizione_facolta,descrizione_corso_di_studi,matricola, TO_CHAR(data_nascita,'dd/mm/yyyy'), TO_CHAR(dataEmissione,'dd/mm/yyyy'),valida");
/*WHERE clauses */
$stud_obj -> whereAdd("matricola LIKE '".$stud_obj -> escape($data['matricola'])."%'");

Expected result:
----------------
Expected SQL code under Oracle:

SELECT nome,cognome,numero,tipo,descrizione_facolta,descrizione_corso_di_studi,matricola, TO_CHAR(data_nascita,'dd/mm/yyyy'), TO_CHAR(dataEmissione,'dd/mm/yyyy'),valida
FROM T_ECDL_STUDENTE_DIPLOMATI
INNER JOIN ECDL_SKILLSCARD_DIPLOMATI ON ECDL_SKILLSCARD_DIPLOMATI.MATRICOLASTUDENTI=T_ECDL_STUDENTE_DIPLOMATI.MATRICOLA
WHERE matricola LIKE '%'

Actual result:
--------------
Output from DB_DataObjecst with debuglevel(5):
DbClass_T_ECDL_STUDENTE_DIPLOMATI: QUERY: SELECT nome,cognome,numero,tipo,descrizione_facolta,descrizione_corso_di_studi,matricola, TO_CHAR(data_nascita,'dd/mm/yyyy'), TO_CHAR(dataEmissione,'dd/mm/yyyy'),valida FROM T_ECDL_STUDENTE_DIPLOMATI INNER JOIN ECDL_SKILLSCARD_DIPLOMATI AS ECDL_SKILLSCARD_DIPLOMATI ON ECDL_SKILLSCARD_DIPLOMATI.MATRICOLASTUDENTI=T_ECDL_STUDENTE_DIPLOMATI.MATRICOLA WHERE matricola LIKE '%'
DbClass_T_ECDL_STUDENTE_DIPLOMATI: Query Error: [DB_Error: message="DB Error: unknown error" code=-1 mode=return level=notice prefix="" info="SELECT nome,cognome,numero,tipo,descrizione_facolta,descrizione_corso_di_studi,matricola, TO_CHAR(data_nascita,'dd/mm/yyyy'), TO_CHAR(dataEmissione,'dd/mm/yyyy'),valida FROM T_ECDL_STUDENTE_DIPLOMATI INNER JOIN ECDL_SKILLSCARD_DIPLOMATI AS ECDL_SKILLSCARD_DIPLOMATI ON ECDL_SKILLSCARD_DIPLOMATI.MATRICOLASTUDENTI=T_ECDL_STUDENTE_DIPLOMATI.MATRICOLA WHERE matricola LIKE '%' [nativecode=ORA-00905: missing keyword]"]

Running SQL generated under SQL Worksheet (Oracle Client), it returns back the same error, hilighting the AS clause. It seems that under Oracle SQL you can't use AS clause like that.

[2004-09-29 08:15 UTC] cordero at di dot unito dot it

Tried the CVS fix and it worked! Thans a lot!