View Entity Behaviour

[Rishi Solanki]

Hello All,
While working on one view entity creation, I faced one problem and thought
to share with you all.
I used OR as entity alias for OrderRole, and while fetching the data the
entity throws exception of incorrect SQL.

On research I found that while creating SQL query sql executer treat OR as
OR operator and misinterpret the query. So I would suggest never ever use
any SQL keyword for entity alias in view entity like OR, AND or any other
while creating abbreviations for entity alias.

Now I would like to ask to community, do we need to handle this?

If yes then how, in code or in best practice document. Please advise.

Best Regards,
--
Rishi Solanki
Sr Manager, Enterprise Software Development
*HotWax Systems*
*Enterprise open source experts*
cell: [phone]
http://www.hotwaxsystems.com

[Paul Foxworthy-2]

Hi Rishi,

I think we can fix the problem in the SQL generation and not worry about it
anywhere else. Just wrap alias names in double quotes - see
https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers
 .

For MySQL and Microsoft SQL Server, there are configuration options to get
double quotes to work as per the ISO SQL standard. We could either document
that that's necessary, or use the native characters instead of double
quotes for those databases (backquotes for MySQL, square brackets for
MSSQL).

I've had a quick look and I think the changes might need to be in
GenericDAO. The point is once we get this right you can use any alias name
without needing to know the complete list of SQL keywords, and nowhere else
in OFBiz needs to worry about the issue.

What do you think?

Thanks

Paul Foxworthy

On Sat, 27 Jul 2019 at 23:56, Rishi Solanki <[hidden email]> wrote:

> Hello All,
> While working on one view entity creation, I faced one problem and thought
> to share with you all.
> I used OR as entity alias for OrderRole, and while fetching the data the
> entity throws exception of incorrect SQL.
>
> On research I found that while creating SQL query sql executer treat OR as
> OR operator and misinterpret the query. So I would suggest never ever use
> any SQL keyword for entity alias in view entity like OR, AND or any other
> while creating abbreviations for entity alias.
>
> Now I would like to ask to community, do we need to handle this?
>
> If yes then how, in code or in best practice document. Please advise.
>
> Best Regards,
> --
> Rishi Solanki
> Sr Manager, Enterprise Software Development
> *HotWax Systems*
> *Enterprise open source experts*
> cell: [phone]
> http://www.hotwaxsystems.com
>

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[Rishi Solanki]

Paul,
Thanks for your input, I see delimited can be use for column names as well.
Are you proposing to limit it for entity alias name to wrap in the code as
I have reported it for that only. I'll convert a query and wait for input
from others.

At first glance it looks good to me and could not think of any side effect.
Thanks!

Best Regards,
--
Rishi Solanki
Sr Manager, Enterprise Software Development
*HotWax Systems*
*Enterprise open source experts*
cell: [phone]
http://www.hotwaxsystems.com


On Sun, Jul 28, 2019 at 7:14 AM Paul Foxworthy <[hidden email]>
wrote:

> Hi Rishi,
>
> I think we can fix the problem in the SQL generation and not worry about it
> anywhere else. Just wrap alias names in double quotes - see
>
> https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers
>  .
>
> For MySQL and Microsoft SQL Server, there are configuration options to get
> double quotes to work as per the ISO SQL standard. We could either document
> that that's necessary, or use the native characters instead of double
> quotes for those databases (backquotes for MySQL, square brackets for
> MSSQL).
>
> I've had a quick look and I think the changes might need to be in
> GenericDAO. The point is once we get this right you can use any alias name
> without needing to know the complete list of SQL keywords, and nowhere else
> in OFBiz needs to worry about the issue.
>
> What do you think?
>
> Thanks
>
> Paul Foxworthy
>
> On Sat, 27 Jul 2019 at 23:56, Rishi Solanki <[hidden email]>
> wrote:
>
> > Hello All,
> > While working on one view entity creation, I faced one problem and
> thought
> > to share with you all.
> > I used OR as entity alias for OrderRole, and while fetching the data the
> > entity throws exception of incorrect SQL.
> >
> > On research I found that while creating SQL query sql executer treat OR
> as
> > OR operator and misinterpret the query. So I would suggest never ever use
> > any SQL keyword for entity alias in view entity like OR, AND or any other
> > while creating abbreviations for entity alias.
> >
> > Now I would like to ask to community, do we need to handle this?
> >
> > If yes then how, in code or in best practice document. Please advise.
> >
> > Best Regards,
> > --
> > Rishi Solanki
> > Sr Manager, Enterprise Software Development
> > *HotWax Systems*
> > *Enterprise open source experts*
> > cell: [phone]
> > http://www.hotwaxsystems.com
> >
>
>
> --
> Coherent Software Australia Pty Ltd
> PO Box 2773
> Cheltenham Vic 3192
> Australia
>
> Phone: [phone]
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>

[Paul Foxworthy-2]

On Mon, 29 Jul 2019 at 23:51, Rishi Solanki <[hidden email]> wrote:


> Thanks for your input, I see delimited can be use for column names as well.
> Are you proposing to limit it for entity alias name to wrap in the code as
> I have reported it for that only. I'll convert a query and wait for input
> from others.
>

Hi Rishi,

There is less risk we'll see a problem with column names. A column name
will be longer and more descriptive and less likely to collide with an SQL
keyword. Aliases are more the danger, as you've found.

However, if we do this at all we should do it for column names as well so a
collision can never happen. That's better than hoping that anyone adding a
new column knows what they're doing. Instead of a low risk of collision, we
can be certain there's *no* risk.

Cheers

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]