OFBiz SQL Processor questions

[Cameron Smith-6]

Hi, in the last few weeks I have come across a couple
of issues with the SQL Processor generating incorrect
SQL.  I am using MySQL 5.0, but I stress that these
issues would also cause problems in the two other
RDBMS I am familiar with (Oracle and PostgreSQL).

I have dug around this part of the Entity Engine code
and I believe I could write a fix, but first I wanted
to check that I am not missing something or using the
EE in an incorrect way.

=== Issue 1 - BETWEEN operator ===
EntityOperator.BETWEEN generates incorrect SQL.  For
instance, if I use something like (Java 1.5):

 List criteria = new LinkedList();
 criteria.add(1.0); criteria.add(10.0);
 new EntityExpr("amount", EntityOperator.BETWEEN,
criteria);

 The EntityExpr, if used in a delegator query, will
generate the following SQL:
 ... BETWEEN (1.0, 10.0)

However this syntax is incorrect in the following
RDBMS:
MySQL 5.0:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
PostgreSQL 8.2:
http://www.postgresql.org/docs/8.2/static/functions-comparison.html
Oracle 9i: (couldn't be bothered to fight my way
through Oracle's online docs but I did check my ref.
book here and my own recollection of 8 years
programming with Oracle).

Has anyone used EntityOperator.BETWEEN successfully?

=== Issue 2 - Column aliasing ===
I only noticed this issue when I synced our local,
customized OFBiz (based on r502669) with the 4.0
branch (r545673).  But I believe that the issue always
existed, and it is just that the alterations to some
of the accounting View Entities in r545673, brought it
to light.

When the EE SQL Processor does a query which View
Entities, it aliases each component table of the
Entity to a short name within the query, and prefixes
all column references in the SELECT clause with that
short name.  This is correct.  However it does NOT
prefix columns in the WHERE clause with that short
name.  Or at least, I cannot see how to make it do
this.   Because of this, MySQL at least rejects the
query because some of the column names in the WHERE
clause are ambiguous.

Here is a real example of a query which worked
perfectly in r502669, but breaks in r545673, because
of the extra columns added into the
AcctgTransAndEntries View Entity.  Once again, I
stress that r545673 is not the culprit - the SQL
Processor appears to have always had this limitation,
it just does not show itself regularly!

Here is the code to build up the query:
public List<GenericValue>
findPostedTransactionsLike(String orgId, String txId)
throws GenericEntityException
   {
      List criteria = exprEqualsAll("isPosted", "Y");
      criteria.add(new EntityExpr("acctgTransId",
EntityOperator.LIKE, txId + "%"));
      List orderBy = UtilMisc.toList("acctgTransId",
"acctgTransEntrySeqId");
      return
_delegator.findByAnd("AcctgTransAndEntries", criteria,
orderBy);
   }

And here is the generated SQL and MySQL's error
message.  The generated SQL would work perfectly if
the columns in the WHERE clause were prefixed with the
respective alias names:

SELECT ATR.IS_POSTED AS IS_POSTED,
ATR.GL_FISCAL_TYPE_ID AS GL_FISCAL_TYPE_ID,
ATR.ACCTG_TRANS_TYPE_ID AS ACCTG_TRANS_TYPE_ID,
ATR.TRANSACTION_DATE AS TRANSACTION_DATE,
ATR.DESCRIPTION AS TRANS_DESCRIPTION, ATR.INVOICE_ID
AS INVOICE_ID, ATR.PAYMENT_ID AS PAYMENT_ID,
ATR.SHIPMENT_ID AS SHIPMENT_ID, ATR.RECEIPT_ID AS
RECEIPT_ID, ATR.INVENTORY_ITEM_ID AS
INVENTORY_ITEM_ID, ATR.WORK_EFFORT_ID AS
WORK_EFFORT_ID, ATR.PHYSICAL_INVENTORY_ID AS
PHYSICAL_INVENTORY_ID, ATR.VOUCHER_REF AS VOUCHER_REF,
ATR.GL_JOURNAL_ID AS GL_JOURNAL_ID, ATE.ACCTG_TRANS_ID
AS ACCTG_TRANS_ID, ATE.ACCTG_TRANS_ENTRY_SEQ_ID AS
ACCTG_TRANS_ENTRY_SEQ_ID, ATE.GL_ACCOUNT_ID AS
GL_ACCOUNT_ID, ATE.PRODUCT_ID AS PRODUCT_ID,
ATE.DEBIT_CREDIT_FLAG AS DEBIT_CREDIT_FLAG, ATE.AMOUNT
AS AMOUNT, ATE.CURRENCY_UOM_ID AS CURRENCY_UOM_ID,
ATE.ORGANIZATION_PARTY_ID AS ORGANIZATION_PARTY_ID,
GLA.GL_ACCOUNT_TYPE_ID AS GL_ACCOUNT_TYPE_ID,
GLAC.GL_ACCOUNT_CLASS_ID AS GL_ACCOUNT_CLASS_ID,
ATE.PARTY_ID AS PARTY_ID, ATE.RECONCILE_STATUS_ID AS
RECONCILE_STATUS_ID, ATE.ACCTG_TRANS_ENTRY_TYPE_ID AS
ACCTG_TRANS_ENTRY_TYPE_ID, ATE.DESCRIPTION AS
DESCRIPTION FROM ACCTG_TRANS ATR INNER JOIN
ACCTG_TRANS_ENTRY ATE ON ATR.ACCTG_TRANS_ID =
ATE.ACCTG_TRANS_ID INNER JOIN GL_ACCOUNT GLA ON
ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID INNER JOIN
GL_ACCOUNT_CLASS GLAC ON GLA.GL_ACCOUNT_CLASS_ID =
GLAC.GL_ACCOUNT_CLASS_ID WHERE (IS_POSTED = ? AND
ACCTG_TRANS_ID LIKE ?) ORDER BY ACCTG_TRANS_ID ASC,
ACCTG_TRANS_ENTRY_SEQ_ID ASC (Column 'ACCTG_TRANS_ID'
in where clause is ambiguous)

========== end of message ==============









      ___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/ 

[Rodrigo Lima-2]

Use BETWEEN and more new EntityCondition AND to values.
Query ie: fieldName BETWEEN 10 AND 20.





2007/6/30, Cameron Smith <[hidden email]>:

> Hi, in the last few weeks I have come across a couple
> of issues with the SQL Processor generating incorrect
> SQL.  I am using MySQL 5.0, but I stress that these
> issues would also cause problems in the two other
> RDBMS I am familiar with (Oracle and PostgreSQL).
>
> I have dug around this part of the Entity Engine code
> and I believe I could write a fix, but first I wanted
> to check that I am not missing something or using the
> EE in an incorrect way.
>
> === Issue 1 - BETWEEN operator ===
> EntityOperator.BETWEEN generates incorrect SQL.  For
> instance, if I use something like (Java 1.5):
>
>  List criteria = new LinkedList();
>  criteria.add(1.0); criteria.add(10.0);
>  new EntityExpr("amount", EntityOperator.BETWEEN,
> criteria);
>
>  The EntityExpr, if used in a delegator query, will
> generate the following SQL:
>  ... BETWEEN (1.0, 10.0)
>
> However this syntax is incorrect in the following
> RDBMS:
> MySQL 5.0:
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
> PostgreSQL 8.2:
> http://www.postgresql.org/docs/8.2/static/functions-comparison.html
> Oracle 9i: (couldn't be bothered to fight my way
> through Oracle's online docs but I did check my ref.
> book here and my own recollection of 8 years
> programming with Oracle).
>
> Has anyone used EntityOperator.BETWEEN successfully?
>
> === Issue 2 - Column aliasing ===
> I only noticed this issue when I synced our local,
> customized OFBiz (based on r502669) with the 4.0
> branch (r545673).  But I believe that the issue always
> existed, and it is just that the alterations to some
> of the accounting View Entities in r545673, brought it
> to light.
>
> When the EE SQL Processor does a query which View
> Entities, it aliases each component table of the
> Entity to a short name within the query, and prefixes
> all column references in the SELECT clause with that
> short name.  This is correct.  However it does NOT
> prefix columns in the WHERE clause with that short
> name.  Or at least, I cannot see how to make it do
> this.   Because of this, MySQL at least rejects the
> query because some of the column names in the WHERE
> clause are ambiguous.
>
> Here is a real example of a query which worked
> perfectly in r502669, but breaks in r545673, because
> of the extra columns added into the
> AcctgTransAndEntries View Entity.  Once again, I
> stress that r545673 is not the culprit - the SQL
> Processor appears to have always had this limitation,
> it just does not show itself regularly!
>
> Here is the code to build up the query:
> public List<GenericValue>
> findPostedTransactionsLike(String orgId, String txId)
> throws GenericEntityException
>   {
>      List criteria = exprEqualsAll("isPosted", "Y");
>      criteria.add(new EntityExpr("acctgTransId",
> EntityOperator.LIKE, txId + "%"));
>      List orderBy = UtilMisc.toList("acctgTransId",
> "acctgTransEntrySeqId");
>      return
> _delegator.findByAnd("AcctgTransAndEntries", criteria,
> orderBy);
>   }
>
> And here is the generated SQL and MySQL's error
> message.  The generated SQL would work perfectly if
> the columns in the WHERE clause were prefixed with the
> respective alias names:
>
> SELECT ATR.IS_POSTED AS IS_POSTED,
> ATR.GL_FISCAL_TYPE_ID AS GL_FISCAL_TYPE_ID,
> ATR.ACCTG_TRANS_TYPE_ID AS ACCTG_TRANS_TYPE_ID,
> ATR.TRANSACTION_DATE AS TRANSACTION_DATE,
> ATR.DESCRIPTION AS TRANS_DESCRIPTION, ATR.INVOICE_ID
> AS INVOICE_ID, ATR.PAYMENT_ID AS PAYMENT_ID,
> ATR.SHIPMENT_ID AS SHIPMENT_ID, ATR.RECEIPT_ID AS
> RECEIPT_ID, ATR.INVENTORY_ITEM_ID AS
> INVENTORY_ITEM_ID, ATR.WORK_EFFORT_ID AS
> WORK_EFFORT_ID, ATR.PHYSICAL_INVENTORY_ID AS
> PHYSICAL_INVENTORY_ID, ATR.VOUCHER_REF AS VOUCHER_REF,
> ATR.GL_JOURNAL_ID AS GL_JOURNAL_ID, ATE.ACCTG_TRANS_ID
> AS ACCTG_TRANS_ID, ATE.ACCTG_TRANS_ENTRY_SEQ_ID AS
> ACCTG_TRANS_ENTRY_SEQ_ID, ATE.GL_ACCOUNT_ID AS
> GL_ACCOUNT_ID, ATE.PRODUCT_ID AS PRODUCT_ID,
> ATE.DEBIT_CREDIT_FLAG AS DEBIT_CREDIT_FLAG, ATE.AMOUNT
> AS AMOUNT, ATE.CURRENCY_UOM_ID AS CURRENCY_UOM_ID,
> ATE.ORGANIZATION_PARTY_ID AS ORGANIZATION_PARTY_ID,
> GLA.GL_ACCOUNT_TYPE_ID AS GL_ACCOUNT_TYPE_ID,
> GLAC.GL_ACCOUNT_CLASS_ID AS GL_ACCOUNT_CLASS_ID,
> ATE.PARTY_ID AS PARTY_ID, ATE.RECONCILE_STATUS_ID AS
> RECONCILE_STATUS_ID, ATE.ACCTG_TRANS_ENTRY_TYPE_ID AS
> ACCTG_TRANS_ENTRY_TYPE_ID, ATE.DESCRIPTION AS
> DESCRIPTION FROM ACCTG_TRANS ATR INNER JOIN
> ACCTG_TRANS_ENTRY ATE ON ATR.ACCTG_TRANS_ID =
> ATE.ACCTG_TRANS_ID INNER JOIN GL_ACCOUNT GLA ON
> ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID INNER JOIN
> GL_ACCOUNT_CLASS GLAC ON GLA.GL_ACCOUNT_CLASS_ID =
> GLAC.GL_ACCOUNT_CLASS_ID WHERE (IS_POSTED = ? AND
> ACCTG_TRANS_ID LIKE ?) ORDER BY ACCTG_TRANS_ID ASC,
> ACCTG_TRANS_ENTRY_SEQ_ID ASC (Column 'ACCTG_TRANS_ID'
> in where clause is ambiguous)
>
> ========== end of message ==============
>
>
>
>
>
>
>
>
>
>      ___________________________________________________________
> Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
> now.
> http://uk.answers.yahoo.com/
>

[Cameron Smith-6]

You mean like this?

List criteria = new LinkedList();
criteria.add(1.0); criteria.add(EntityOperator.AND);
criteria.add(10.0);
new EntityExpr("amount", EntityOperator.BETWEEN,
criteria);

I cannot do this because EntityExpr (see about line
64) insists on having 0 or 2 criteria, not 3.

If you have actually got this working, would you mind
sending me a code snippet as an example?  Agradecia
muito.

cheers,
cameron

> Use BETWEEN and more new EntityCondition AND to
> values.
> Query ie: fieldName BETWEEN 10 AND 20.
>
>
>
>
>
> 2007/6/30, Cameron Smith
> <[hidden email]>:
> > Hi, in the last few weeks I have come across a
> couple
> > of issues with the SQL Processor generating
> incorrect
> > SQL.  I am using MySQL 5.0, but I stress that
> these
> > issues would also cause problems in the two other
> > RDBMS I am familiar with (Oracle and PostgreSQL).
> >
> > I have dug around this part of the Entity Engine
> code
> > and I believe I could write a fix, but first I
> wanted
> > to check that I am not missing something or using
> the
> > EE in an incorrect way.
> >
> > === Issue 1 - BETWEEN operator ===
> > EntityOperator.BETWEEN generates incorrect SQL.
> For
> > instance, if I use something like (Java 1.5):
> >
> >  List criteria = new LinkedList();
> >  criteria.add(1.0); criteria.add(10.0);
> >  new EntityExpr("amount", EntityOperator.BETWEEN,
> > criteria);
> >
> >  The EntityExpr, if used in a delegator query,
> will
> > generate the following SQL:
> >  ... BETWEEN (1.0, 10.0)
> >
> > However this syntax is incorrect in the following
> > RDBMS:
> > MySQL 5.0:
> >
>

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
> > PostgreSQL 8.2:
> >
>
http://www.postgresql.org/docs/8.2/static/functions-comparison.html

> > Oracle 9i: (couldn't be bothered to fight my way
> > through Oracle's online docs but I did check my
> ref.
> > book here and my own recollection of 8 years
> > programming with Oracle).
> >
> > Has anyone used EntityOperator.BETWEEN
> successfully?
> >
> > === Issue 2 - Column aliasing ===
> > I only noticed this issue when I synced our local,
> > customized OFBiz (based on r502669) with the 4.0
> > branch (r545673).  But I believe that the issue
> always
> > existed, and it is just that the alterations to
> some
> > of the accounting View Entities in r545673,
> brought it
> > to light.
> >
> > When the EE SQL Processor does a query which View
> > Entities, it aliases each component table of the
> > Entity to a short name within the query, and
> prefixes
> > all column references in the SELECT clause with
> that
> > short name.  This is correct.  However it does NOT
> > prefix columns in the WHERE clause with that short
> > name.  Or at least, I cannot see how to make it do
> > this.   Because of this, MySQL at least rejects
> the
> > query because some of the column names in the
> WHERE
> > clause are ambiguous.
> >
> > Here is a real example of a query which worked
> > perfectly in r502669, but breaks in r545673,
> because
> > of the extra columns added into the
> > AcctgTransAndEntries View Entity.  Once again, I
> > stress that r545673 is not the culprit - the SQL
> > Processor appears to have always had this
> limitation,
> > it just does not show itself regularly!
> >
> > Here is the code to build up the query:
> > public List<GenericValue>
> > findPostedTransactionsLike(String orgId, String
> txId)
> > throws GenericEntityException
> >   {
> >      List criteria = exprEqualsAll("isPosted",
> "Y");
> >      criteria.add(new EntityExpr("acctgTransId",
> > EntityOperator.LIKE, txId + "%"));
> >      List orderBy =
> UtilMisc.toList("acctgTransId",
> > "acctgTransEntrySeqId");
> >      return
> > _delegator.findByAnd("AcctgTransAndEntries",
> criteria,
> > orderBy);
> >   }
> >
> > And here is the generated SQL and MySQL's error
> > message.  The generated SQL would work perfectly
> if
> > the columns in the WHERE clause were prefixed with
> the
> > respective alias names:
> >
> > SELECT ATR.IS_POSTED AS IS_POSTED,
> > ATR.GL_FISCAL_TYPE_ID AS GL_FISCAL_TYPE_ID,
> > ATR.ACCTG_TRANS_TYPE_ID AS ACCTG_TRANS_TYPE_ID,
> > ATR.TRANSACTION_DATE AS TRANSACTION_DATE,
> > ATR.DESCRIPTION AS TRANS_DESCRIPTION,
> ATR.INVOICE_ID
> > AS INVOICE_ID, ATR.PAYMENT_ID AS PAYMENT_ID,
> > ATR.SHIPMENT_ID AS SHIPMENT_ID, ATR.RECEIPT_ID AS
> > RECEIPT_ID, ATR.INVENTORY_ITEM_ID AS
> > INVENTORY_ITEM_ID, ATR.WORK_EFFORT_ID AS
> > WORK_EFFORT_ID, ATR.PHYSICAL_INVENTORY_ID AS
> > PHYSICAL_INVENTORY_ID, ATR.VOUCHER_REF AS
> VOUCHER_REF,
> > ATR.GL_JOURNAL_ID AS GL_JOURNAL_ID,
> ATE.ACCTG_TRANS_ID
> > AS ACCTG_TRANS_ID, ATE.ACCTG_TRANS_ENTRY_SEQ_ID AS
> > ACCTG_TRANS_ENTRY_SEQ_ID, ATE.GL_ACCOUNT_ID AS
> > GL_ACCOUNT_ID, ATE.PRODUCT_ID AS PRODUCT_ID,
> > ATE.DEBIT_CREDIT_FLAG AS DEBIT_CREDIT_FLAG,
> ATE.AMOUNT
> > AS AMOUNT, ATE.CURRENCY_UOM_ID AS CURRENCY_UOM_ID,
> > ATE.ORGANIZATION_PARTY_ID AS
> ORGANIZATION_PARTY_ID,
> > GLA.GL_ACCOUNT_TYPE_ID AS GL_ACCOUNT_TYPE_ID,
> > GLAC.GL_ACCOUNT_CLASS_ID AS GL_ACCOUNT_CLASS_ID,
> > ATE.PARTY_ID AS PARTY_ID, ATE.RECONCILE_STATUS_ID
> AS
> > RECONCILE_STATUS_ID, ATE.ACCTG_TRANS_ENTRY_TYPE_ID
> AS
> > ACCTG_TRANS_ENTRY_TYPE_ID, ATE.DESCRIPTION AS
> > DESCRIPTION FROM ACCTG_TRANS ATR INNER JOIN
> > ACCTG_TRANS_ENTRY ATE ON ATR.ACCTG_TRANS_ID =
> > ATE.ACCTG_TRANS_ID INNER JOIN GL_ACCOUNT GLA ON
> > ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID INNER JOIN
> > GL_ACCOUNT_CLASS GLAC ON GLA.GL_ACCOUNT_CLASS_ID =
> > GLAC.GL_ACCOUNT_CLASS_ID WHERE (IS_POSTED = ? AND
> > ACCTG_TRANS_ID LIKE ?) ORDER BY ACCTG_TRANS_ID
> ASC,
> > ACCTG_TRANS_ENTRY_SEQ_ID ASC (Column
> 'ACCTG_TRANS_ID'
> > in where clause is ambiguous)
> >
> > ========== end of message ==============
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >    
>

___________________________________________________________
> > Yahoo! Answers - Got a question? Someone out there
> knows the answer. Try it
> > now.
> > http://uk.answers.yahoo.com/
> >
>
>



       
       
               
___________________________________________________________
New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes.
http://uk.rd.yahoo.com/evt=44106/*http://mail.yahoo.net/uk 

[Shi Jinghai]

> List criteria = new LinkedList();
> criteria.add(1.0); criteria.add(EntityOperator.AND);
> criteria.add(10.0);
> new EntityExpr("amount", EntityOperator.BETWEEN,
> criteria);
criteria.add( new EntityExpr(amount,
EntityOperator.GREATER_THAN_EQUAL_TO, 1.0 );
criteria.add( new EntityExpr(amount, EntityOperator.LESS_THAN_EQUAL_TO,
10.0 );
EntityCondition queryConditionsList = new EntityConditionList(criteria,
EntityOperator.AND);

Shi Jinghai/Bejing Langhua Ltd.

[Cameron Smith-6]

Dear Shi Jinghai, your solution is exactly what we
already did to resolve this problem.  We just created
a helper method which built up this "between-like
structure".  It has been working fine for several
months.

The reason I wrote to the ML, is that if
EntityCondition.BETWEEN does not actually work, we
should remove or correct it.  Not just have a
workaround!!

Can any of the OFBiz core team shed some light on
this?

cameron


      ___________________________________________________________
Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for
your free account today http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html