[TYPO3-dev] DBAL Usuage

[belbono]

Hi Martin,


bad news brackets doesn't work.
If I give something like

'FROM PUBLICATIONS INNER JOIN ARTICLE ON (PUBLICATIONS.UID_ = ARTICLE.PUBLICATIONS_UID) '

to DBAL I get an Error saying that there is no handler found and no join 
clause and other things...

So I think DBAL cannot handle table attributs with given table name as 
prefix.
Other example:

SELECT PUBLICATIONS.UID_,PUBLICATIONS.TITLE,PUBLICATIONS.RATING,PUBLICATIONS.COMMENT_

will be converted into

SELECT "PUBLICATIONS"."UID_", "PUBLICATIONS"."TITLE", "PUBLICATIONS"."RATING", "PUBLICATIONS"."COMMENT_"

DBAL quotes the table name and that makes wrong sql.
I would call this behavior a bug in DBAL, because I don't see any way 
how this can be solved through changing my input data.

If I´m wrong about this, please correct me.


Alex

Martin Kutschker wrote:
> belbono schrieb:
>   
>> Hi,
>>
>> i´m having some problems with DBAL. Its makeing quotes where I don't 
>> want them and this chrashes the wohle query.
>>
>> 1st problem.
>> $res = 
>> $GLOBALS['TYPO3_DB']->exec_SELECTquery('MAX(UID)',strtoupper($table),'','','',''); 
>>
>> As you can see I want the maximum UID from the $table.
>>     
>
> I wouldn't do the strtoupper. If Oracle needs uppercase table names then 
> DBAL should do the conversion automatically. Use table and column names 
> as they are defined.
>
> MAX() is a common function so in theory DBAL should know it and support 
> it correctly, ie do the quoting for you.
>
> If it doesn't try:
>
> $GLOBALS['TYPO3_DB']->exec_query("SELECT max(uid) FROM $table");
>
> Maybe this works better.
>
>   
>> 2nd problem
>> Thats a bit harder I think
>>
>> For a search function I´m building up query strings before I use them 
>> with $GLOBALS['TYPO3_DB']->exec_SELECTquery()
>>
>> I´m using several INNER JOIN statements which are causing trouble with 
>> DBAL - Example:
>>
>> $from= 'TAB1 INNER JOIN '.$tabname.' ON TAB1.UID = '.$tabname.'.TAB1_UID ';
>>
>> I need to write the tablename before the attributes to avoid ambiguous 
>> columns !
>>
>> After this I´m calling 
>> $GLOBALS['TYPO3_DB']->exec_SELECTquery($select,$from,$where,'','','');
>>
>> Under MySQL there is no trouble and I get a from part in my statement 
>> like this:
>> TAB1 INNER JOIN TAB2 ON TAB1.UID = TAB2.TAB1_UID
>>
>> When using the extension under Oracle it makes something weird like:
>>
>> TAB1 INNER JOIN TAB2 ON "TAB1.UID = "TAB2.TAB1_UID"
>>
>> At first I tried to quote the UID by hand - but this made it just worse.
>> ...JOIN TAB2 ON "TAB1."UID" = TAB2.........
>>
>> My question: Is it generally possible to build such statements in a 
>> proper way so that MySQL and Oracle can handle them? - and if yes whats 
>> wrong here ?
>>     
>
> Try using barackets around the ON part of the JOIN:
>
> TAB1 INNER JOIN TAB2 ON (TAB1.UID = TAB2.TAB1_UID)
>
> Also try spelling the tables names as found in the ext_tables.sql files.
>
> Masi
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>
>