You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Adrian Crum <ad...@hlmksw.com> on 2007/04/18 23:10:54 UTC
SQL Error
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
Posted by "David E. Jones" <jo...@hotwaxmedia.com>.
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 <jo...@hotwaxmedia.com> 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 <ad...@hlmksw.com> 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
Posted by Anil Patel <to...@gmail.com>.
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 <jo...@hotwaxmedia.com> 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 <ad...@hlmksw.com> 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
Posted by "David E. Jones" <jo...@hotwaxmedia.com>.
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 <ad...@hlmksw.com> 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
Posted by Anil Patel <to...@gmail.com>.
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 <ad...@hlmksw.com> 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
Posted by Adrian Crum <ad...@hlmksw.com>.
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
Posted by "David E. Jones" <jo...@hotwaxmedia.com>.
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?
>