SQL Error
Locked
SQL Error
 
|
Re: SQL Error
|
|
This is related to the MySQL issue that was reported recently. There was a bug in the entity engine causing certain settings on the datasource element in the entityengine.xml file to be ignored. The setting in the advantage config, and various others, that was being ignored is the "alias-view-columns" attribute. That was set to true and it looks like Advantage (like many other databases) does NOT support aliased view columns, ie aliases for fields in joins. To fix this just change alias-view-columns=true to alias-view- columns=false. This is now changed in SVN. -David On Apr 18, 2007, at 3:10 PM, Adrian Crum wrote: > I'm trying to update my local copy of OFBiz to the latest SVN. Last > version used was back in 2005. Now I'm getting SQL exceptions: > > 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 PARTY_NOTE PN INNER JOIN NOTE_DATA ND ON PN.NOTE_ID = > ND.NOTE_ID WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC > ([Extended Systems][Advantage SQL Engine]Column not found: > TARGET_PARTY_ID -- Location of error in the SQL statement is: 259) > > Any idea what changed or what could be wrong? > |
smime.p7s (3K)
Re: SQL Error
|
|
Re: SQL Error
|
|
Re: SQL Error
|
|
It looks like the entityengine.xml file OOTB does not specify a alias- view-columns, so it would default to true. Are you saying that adding alias-view-columns=false does not fix this problem? Are you sure it was set and the server restarted? The SQL listed below is the type of SQL that would be generated if it was set to true. Namely the FIXED_ASSET_ID in the WHERE clause is not prefixed with "FAM." which it would be if that was set to false. -David On Apr 18, 2007, at 3:49 PM, Anil Patel wrote: > I am getting following error. Same code works fine on a snapshot of > code > that was put in production about month back. I am not sure if this > Problem > also has to do with what Adrian reported. I am still getting this > error even > after David's recent fix. > > This is happening on MS SQL Server. > > org.ofbiz.entity.GenericDataSourceException: SQL Exception while > executing > the following:SELECT FAM. > FIXED_ASSET_ID AS FIXED_ASSET_ID, FAM.SCHEDULE_WORK_EFFORT_ID AS > SCHEDULE_WORK_EFFORT_ID, FAM.INTERV > AL_QUANTITY AS INTERVAL_QUANTITY, WE.WORK_EFFORT_ID AS WORK_EFFORT_ID, > WE.ACTUAL_COMPLETION_DATE AS > ACTUAL_COMPLETION_DATE FROM dbo.FIXED_ASSET_MAINT FAM INNER JOIN > dbo.WORK_EFFORT WE ON FAM.SCHEDULE_ > WORK_EFFORT_ID = WE.WORK_EFFORT_ID WHERE (FIXED_ASSET_ID = ?) ORDER BY > ACTUAL_COMPLETION_DATE DESC ( > Ambiguous column name 'FIXED_ASSET_ID'.) > at org.ofbiz.entity.jdbc.SQLProcessor.executeQuery > (SQLProcessor.java > :399) > > Any tips > > Regards > Anil > > > > On 4/18/07, Adrian Crum <[hidden email]> wrote: >> >> David, >> >> That fixed it. Thank you very much for the quick reply! >> >> >> David E. Jones wrote: >> >> > >> > This is related to the MySQL issue that was reported recently. >> > >> > There was a bug in the entity engine causing certain settings on >> the >> > datasource element in the entityengine.xml file to be ignored. The >> > setting in the advantage config, and various others, that was being >> > ignored is the "alias-view-columns" attribute. That was set to >> true and >> > it looks like Advantage (like many other databases) does NOT >> support >> > aliased view columns, ie aliases for fields in joins. >> > >> > To fix this just change alias-view-columns=true to alias-view- >> > columns=false. This is now changed in SVN. >> > >> > -David >> > >> > >> > On Apr 18, 2007, at 3:10 PM, Adrian Crum wrote: >> > >> >> I'm trying to update my local copy of OFBiz to the latest SVN. >> Last >> >> version used was back in 2005. Now I'm getting SQL exceptions: >> >> >> >> 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 >> >> PARTY_NOTE PN INNER JOIN NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID >> >> WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC >> ([Extended >> >> Systems][Advantage SQL Engine]Column not found: >> TARGET_PARTY_ID -- >> >> Location of error in the SQL statement is: 259) >> >> >> >> Any idea what changed or what could be wrong? >> >> >> > >> |
Re: SQL Error
|
|
Re: SQL Error
|
|
Okay, that's in SVN. Wow, maybe we should change the default instead of setting each one to false... I guess we'll see. -David On Apr 18, 2007, at 4:08 PM, Anil Patel wrote: > Yes, this fixed it. I did not look diff of your commit close > enough. If you > can please add following line to sql server connection settings. > > alias-view-columns="false" > > > regards > > Anil > > On 4/18/07, David E. Jones <[hidden email]> wrote: >> >> >> It looks like the entityengine.xml file OOTB does not specify a >> alias- >> view-columns, so it would default to true. >> >> Are you saying that adding alias-view-columns=false does not fix this >> problem? >> >> Are you sure it was set and the server restarted? The SQL listed >> below is the type of SQL that would be generated if it was set to >> true. Namely the FIXED_ASSET_ID in the WHERE clause is not prefixed >> with "FAM." which it would be if that was set to false. >> >> -David >> >> >> On Apr 18, 2007, at 3:49 PM, Anil Patel wrote: >> >> > I am getting following error. Same code works fine on a snapshot of >> > code >> > that was put in production about month back. I am not sure if this >> > Problem >> > also has to do with what Adrian reported. I am still getting this >> > error even >> > after David's recent fix. >> > >> > This is happening on MS SQL Server. >> > >> > org.ofbiz.entity.GenericDataSourceException: SQL Exception while >> > executing >> > the following:SELECT FAM. >> > FIXED_ASSET_ID AS FIXED_ASSET_ID, FAM.SCHEDULE_WORK_EFFORT_ID AS >> > SCHEDULE_WORK_EFFORT_ID, FAM.INTERV >> > AL_QUANTITY AS INTERVAL_QUANTITY, WE.WORK_EFFORT_ID AS >> WORK_EFFORT_ID, >> > WE.ACTUAL_COMPLETION_DATE AS >> > ACTUAL_COMPLETION_DATE FROM dbo.FIXED_ASSET_MAINT FAM INNER JOIN >> > dbo.WORK_EFFORT WE ON FAM.SCHEDULE_ >> > WORK_EFFORT_ID = WE.WORK_EFFORT_ID WHERE (FIXED_ASSET_ID = ?) >> ORDER BY >> > ACTUAL_COMPLETION_DATE DESC ( >> > Ambiguous column name 'FIXED_ASSET_ID'.) >> > at org.ofbiz.entity.jdbc.SQLProcessor.executeQuery >> > (SQLProcessor.java >> > :399) >> > >> > Any tips >> > >> > Regards >> > Anil >> > >> > >> > >> > On 4/18/07, Adrian Crum <[hidden email]> wrote: >> >> >> >> David, >> >> >> >> That fixed it. Thank you very much for the quick reply! >> >> >> >> >> >> David E. Jones wrote: >> >> >> >> > >> >> > This is related to the MySQL issue that was reported recently. >> >> > >> >> > There was a bug in the entity engine causing certain settings on >> >> the >> >> > datasource element in the entityengine.xml file to be >> ignored. The >> >> > setting in the advantage config, and various others, that was >> being >> >> > ignored is the "alias-view-columns" attribute. That was set to >> >> true and >> >> > it looks like Advantage (like many other databases) does NOT >> >> support >> >> > aliased view columns, ie aliases for fields in joins. >> >> > >> >> > To fix this just change alias-view-columns=true to alias-view- >> >> > columns=false. This is now changed in SVN. >> >> > >> >> > -David >> >> > >> >> > >> >> > On Apr 18, 2007, at 3:10 PM, Adrian Crum wrote: >> >> > >> >> >> I'm trying to update my local copy of OFBiz to the latest SVN. >> >> Last >> >> >> version used was back in 2005. Now I'm getting SQL exceptions: >> >> >> >> >> >> 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 >> >> >> PARTY_NOTE PN INNER JOIN NOTE_DATA ND ON PN.NOTE_ID = >> ND.NOTE_ID >> >> >> WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC >> >> ([Extended >> >> >> Systems][Advantage SQL Engine]Column not found: >> >> TARGET_PARTY_ID -- >> >> >> Location of error in the SQL statement is: 259) >> >> >> >> >> >> Any idea what changed or what could be wrong? >> >> >> >> >> > >> >> >> >> >> |
| Free forum by Nabble | Edit this page |