You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Michael Irving <mi...@keynetx.com> on 2007/05/29 18:27:37 UTC
Errors: findByAnd with Oracle DB
When using findByAnd against an entity-view with an Oracle backend, the
query fails.
Example:
delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "targetPartyId" ,
partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
OFBIz generates the following SQL:
SELECT PN.PARTY_ID AS TARGET_PARTY_ID,
ND.NOTE_ID AS NOTE_ID,
ND.NOTE_NAME AS NOTE_NAME,
ND.NOTE_INFO AS NOTE_INFO,
ND.NOTE_DATE_TIME AS NOTE_DATE_TIME,
ND.NOTE_PARTY AS NOTE_PARTY
FROM OFBIZ_NEW.PARTY_NOTE PN INNER JOIN OFBIZ_NEW.NOTE_DATA ND ON
PN.NOTE_ID = ND.NOTE_ID
WHERE (TARGET_PARTY_ID = ?)
ORDER BY NOTE_DATE_TIME DESC
Oracle does not know what TARGET_PARTY_ID is because the column does not
exist in any of the tables being queried.
Solution #1: OFBiz should Construct the query so Oracle treats the data
like a database view. By doing this, you can reference the column aliases:
SELECT *
FROM (SELECT pn.PARTY_ID as TARGET_PARTY_ID, nd.NOTE_ID AS NOTE_ID,
nd.NOTE_NAME AS NOTE_NAME
,nd.NOTE_DATE_TIME AS NOTE_DATE_TIME, nd.NOTE_PARTY AS
NOTE_PARTY
FROM OFBIZ_NEW.PARTY_NOTE PN, OFBIZ_NEW.NOTE_DATA ND
WHERE PN.NOTE_ID = ND.NOTE_ID)
WHERE target_party_id = ?
ORDER BY NOTE_DATE_TIME DESC
Solution #2: Use the real column name ("alias"."columnName") in findByAnd:
delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "pn.partyId" ,
partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
-----------------------------------------
Michael Irving
Keynetx, Inc. - Building Solutions for Success
Phone: (215) 310.1934
Mobile: (267)474.3564
Fax: (215) 529-5399
email: mirving@keynetx.com
* * * Visit Our Web Site: <http://www.keynetx.net/> http://www.keynetx.net
* * *
----------------------------------------------------------------------------
----
NOTICE: If received in error, please destroy and notify sender. Sender does
not waive confidentiality or privilege, and use is prohibited.
Re: Errors: findByAnd with Oracle DB
Posted by Jacques Le Roux <ja...@les7arts.com>.
Michael,
Why not create a Jira issue and patches (2 in you case I guess ?)
Thanks
Jacques
----- Message d'origine -----
De : "Michael Irving" <mi...@keynetx.com>
À : <de...@ofbiz.apache.org>
Envoyé : mardi 29 mai 2007 18:27
Objet : Errors: findByAnd with Oracle DB
> When using findByAnd against an entity-view with an Oracle backend,
the
> query fails.
>
>
>
> Example:
>
>
>
> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap (
"targetPartyId" ,
> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>
>
>
>
>
> OFBIz generates the following SQL:
>
>
>
> SELECT PN.PARTY_ID AS TARGET_PARTY_ID,
>
> ND.NOTE_ID AS NOTE_ID,
>
> ND.NOTE_NAME AS NOTE_NAME,
>
> ND.NOTE_INFO AS NOTE_INFO,
>
> ND.NOTE_DATE_TIME AS NOTE_DATE_TIME,
>
> ND.NOTE_PARTY AS NOTE_PARTY
>
> FROM OFBIZ_NEW.PARTY_NOTE PN INNER JOIN OFBIZ_NEW.NOTE_DATA ND ON
> PN.NOTE_ID = ND.NOTE_ID
>
> WHERE (TARGET_PARTY_ID = ?)
>
> ORDER BY NOTE_DATE_TIME DESC
>
>
>
> Oracle does not know what TARGET_PARTY_ID is because the column does
not
> exist in any of the tables being queried.
>
>
>
>
>
> Solution #1: OFBiz should Construct the query so Oracle treats the
data
> like a database view. By doing this, you can reference the column
aliases:
>
>
>
> SELECT *
>
> FROM (SELECT pn.PARTY_ID as TARGET_PARTY_ID, nd.NOTE_ID AS NOTE_ID,
> nd.NOTE_NAME AS NOTE_NAME
>
> ,nd.NOTE_DATE_TIME AS NOTE_DATE_TIME, nd.NOTE_PARTY AS
> NOTE_PARTY
>
> FROM OFBIZ_NEW.PARTY_NOTE PN, OFBIZ_NEW.NOTE_DATA ND
>
> WHERE PN.NOTE_ID = ND.NOTE_ID)
>
> WHERE target_party_id = ?
>
> ORDER BY NOTE_DATE_TIME DESC
>
>
>
>
>
> Solution #2: Use the real column name ("alias"."columnName") in
findByAnd:
>
>
>
> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap (
"pn.partyId" ,
> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>
>
>
>
>
> -----------------------------------------
>
> Michael Irving
>
> Keynetx, Inc. - Building Solutions for Success
>
> Phone: (215) 310.1934
>
> Mobile: (267)474.3564
>
> Fax: (215) 529-5399
>
> email: mirving@keynetx.com
>
>
>
> * * * Visit Our Web Site: <http://www.keynetx.net/>
http://www.keynetx.net
> * * *
>
>
>
> ----------------------------------------------------------------------
------
> ----
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does
> not waive confidentiality or privilege, and use is prohibited.
>
>
>
>
Re: Errors: findByAnd with Oracle DB
Posted by David E Jones <jo...@hotwaxmedia.com>.
Here it is again...
-David
David E Jones wrote:
>
> What does your datasource element look like in the entityengine.xml file?
>
> It sounds like your alias-view-columns may be incorrect, just try true
> instead of false or vice-versa.
>
> -David
>
>
> Michael Irving wrote:
>> When using findByAnd against an entity-view with an Oracle backend, the
>> query fails.
>>
>>
>>
>> Example:
>>
>>
>>
>> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap (
>> "targetPartyId" ,
>> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>>
>>
>>
>>
>>
>> OFBIz generates the following SQL:
>>
>>
>>
>> SELECT PN.PARTY_ID AS TARGET_PARTY_ID,
>>
>> ND.NOTE_ID AS NOTE_ID,
>>
>> ND.NOTE_NAME AS NOTE_NAME,
>>
>> ND.NOTE_INFO AS NOTE_INFO,
>>
>> ND.NOTE_DATE_TIME AS NOTE_DATE_TIME,
>>
>> ND.NOTE_PARTY AS NOTE_PARTY
>>
>> FROM OFBIZ_NEW.PARTY_NOTE PN INNER JOIN OFBIZ_NEW.NOTE_DATA ND ON
>> PN.NOTE_ID = ND.NOTE_ID
>>
>> WHERE (TARGET_PARTY_ID = ?)
>>
>> ORDER BY NOTE_DATE_TIME DESC
>>
>>
>>
>> Oracle does not know what TARGET_PARTY_ID is because the column does not
>> exist in any of the tables being queried.
>>
>>
>>
>>
>>
>> Solution #1: OFBiz should Construct the query so Oracle treats the data
>> like a database view. By doing this, you can reference the column
>> aliases:
>>
>>
>>
>> SELECT *
>>
>> FROM (SELECT pn.PARTY_ID as TARGET_PARTY_ID, nd.NOTE_ID AS NOTE_ID,
>> nd.NOTE_NAME AS NOTE_NAME
>>
>> ,nd.NOTE_DATE_TIME AS NOTE_DATE_TIME, nd.NOTE_PARTY AS
>> NOTE_PARTY
>>
>> FROM OFBIZ_NEW.PARTY_NOTE PN, OFBIZ_NEW.NOTE_DATA ND
>>
>> WHERE PN.NOTE_ID = ND.NOTE_ID)
>>
>> WHERE target_party_id = ?
>>
>> ORDER BY NOTE_DATE_TIME DESC
>>
>>
>>
>>
>>
>> Solution #2: Use the real column name ("alias"."columnName") in
>> findByAnd:
>>
>>
>>
>> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "pn.partyId" ,
>> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>>
>>
>>
>>
>>
>> -----------------------------------------
>>
>> Michael Irving
>>
>> Keynetx, Inc. - Building Solutions for Success
>>
>> Phone: (215) 310.1934
>>
>> Mobile: (267)474.3564
>>
>> Fax: (215) 529-5399
>>
>> email: mirving@keynetx.com
>>
>>
>>
>> * * * Visit Our Web Site: <http://www.keynetx.net/>
>> http://www.keynetx.net
>> * * *
>>
>>
>>
>> ----------------------------------------------------------------------------
>>
>> ----
>>
>> NOTICE: If received in error, please destroy and notify sender. Sender
>> does
>> not waive confidentiality or privilege, and use is prohibited.
>>
>>
>>
>>
>
Re: Errors: findByAnd with Oracle DB
Posted by David E Jones <jo...@hotwaxmedia.com>.
What does your datasource element look like in the entityengine.xml file?
It sounds like your alias-view-columns may be incorrect, just try true instead of false or vice-versa.
-David
Michael Irving wrote:
> When using findByAnd against an entity-view with an Oracle backend, the
> query fails.
>
>
>
> Example:
>
>
>
> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "targetPartyId" ,
> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>
>
>
>
>
> OFBIz generates the following SQL:
>
>
>
> SELECT PN.PARTY_ID AS TARGET_PARTY_ID,
>
> ND.NOTE_ID AS NOTE_ID,
>
> ND.NOTE_NAME AS NOTE_NAME,
>
> ND.NOTE_INFO AS NOTE_INFO,
>
> ND.NOTE_DATE_TIME AS NOTE_DATE_TIME,
>
> ND.NOTE_PARTY AS NOTE_PARTY
>
> FROM OFBIZ_NEW.PARTY_NOTE PN INNER JOIN OFBIZ_NEW.NOTE_DATA ND ON
> PN.NOTE_ID = ND.NOTE_ID
>
> WHERE (TARGET_PARTY_ID = ?)
>
> ORDER BY NOTE_DATE_TIME DESC
>
>
>
> Oracle does not know what TARGET_PARTY_ID is because the column does not
> exist in any of the tables being queried.
>
>
>
>
>
> Solution #1: OFBiz should Construct the query so Oracle treats the data
> like a database view. By doing this, you can reference the column aliases:
>
>
>
> SELECT *
>
> FROM (SELECT pn.PARTY_ID as TARGET_PARTY_ID, nd.NOTE_ID AS NOTE_ID,
> nd.NOTE_NAME AS NOTE_NAME
>
> ,nd.NOTE_DATE_TIME AS NOTE_DATE_TIME, nd.NOTE_PARTY AS
> NOTE_PARTY
>
> FROM OFBIZ_NEW.PARTY_NOTE PN, OFBIZ_NEW.NOTE_DATA ND
>
> WHERE PN.NOTE_ID = ND.NOTE_ID)
>
> WHERE target_party_id = ?
>
> ORDER BY NOTE_DATE_TIME DESC
>
>
>
>
>
> Solution #2: Use the real column name ("alias"."columnName") in findByAnd:
>
>
>
> delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "pn.partyId" ,
> partyId ) , UtilMisc .toList ( "-noteDateTime" ) )
>
>
>
>
>
> -----------------------------------------
>
> Michael Irving
>
> Keynetx, Inc. - Building Solutions for Success
>
> Phone: (215) 310.1934
>
> Mobile: (267)474.3564
>
> Fax: (215) 529-5399
>
> email: mirving@keynetx.com
>
>
>
> * * * Visit Our Web Site: <http://www.keynetx.net/> http://www.keynetx.net
> * * *
>
>
>
> ----------------------------------------------------------------------------
> ----
>
> NOTICE: If received in error, please destroy and notify sender. Sender does
> not waive confidentiality or privilege, and use is prohibited.
>
>
>
>