SQL Error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Error

Adrian Crum
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?

Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

David E Jones

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) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

Adrian Crum
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?
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

Anil Patel
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?
> >>
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

David E Jones

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?
>> >>
>> >
>>


smime.p7s (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

Anil Patel
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?
> >> >>
> >> >
> >>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL Error

David E Jones

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?
>> >> >>
>> >> >
>> >>
>>
>>
>>


smime.p7s (3K) Download Attachment