Re: re[eclipselink-users] served keywords

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Derek,

  Default installations of MySQL use a delimiter that is non-standard (not the 
same as the JDBC specification dictates)  The backtick (`) is the default delimiter.

  An EclipseLink Enhancement that takes this in to account is just a few days away.

  In the mean time your options are as follows:

1. use the backtick as your delimiter in your JPA metadata.  The only weakness 
of this solution is that when the fix above is complete, you will have to change 
back.
2. Apparently, it is possible to run MySQL in a mode where the double quote will 
work as the delimiter.  Use that mode

-Tom

Derek Knapp wrote:
> I'm using mysql, this is the complete query that is generated
>
> SELECT t1."to", t1.cc, t1.bcc, t1.reportid, t1.userid, t0.userid, 
> t0.firstname, t0.lastname, t0.phoneno, t0.cellno, t0.email, t0.password, 
> t0.managerid, t0.title, t0.disclaimer, t0.signaturestatus, 
> t0.signatureline, t0.country, t0.region, t0.regionid, t0.city, 
> t0.cityid, t0.office, t0.officeid, t0.team, t0.teamid, t0.adminlevel, 
> t0.companyid FROM users t0, reportdistribution t1 WHERE (((t1.reportid = 
> ?) AND (t1.userid = ?)) AND (t0.userid = t1.userid))
>
> I have tried manually running it in mysql, and i get the same error
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the 
> manual that corresponds to your MySQL server version for the right 
> syntax to use near '"to", t1.cc, t1.bcc, t1.reportid, t1.userid, 
> t0.userid, t0.firstname, t0.lastnam' at line 1
>
>
>
> Derek Knapp
> /Software Engineer/
> *itrac**MEDIA, Inc.*
> 1560 Queen Street East, Toronto, ON M4L 1E9  |   Tel: 416.364.9444   |   
> Fax:416.364.9589
>
> itrac_logo
>
>
> Tom Ware wrote:
> > Hi Derek,
> >
> >   What does the full SQL string you are getting look like?
> >
> >   What database are you on?
> >
> > -Tom
> >
> > Derek Knapp wrote:
> > > using the "\"to\"" worked well, and I was very happy, until i tried 
> > > joining this to another table
> > >
> > > which would cause it to generate sql similar to   select table."to" 
> > > from ....
> > >
> > > which is also invalid... so I'm trying to find more information on the
> > >
> > > "eclipselink.database.delimiters"="true"
> > >
> > > property, but google isn't doing me any good.. only thing I find is 
> > > my question
> > >
> > >
> > >
> > > Derek
> > >
> > >
> > > > To handle reserved words you need to quote them.  You can do this 
> > > > when you
> > > > define your mappings, just give the column name as "\"TO\"" instead 
> > > > of "to".
> > > >
> > > > EclipseLink 2.0 (1.2 as well) (JPA 2.0) support automatically quoting
> > > > columns as well.
> > > >
> > > > I think the property is,
> > > > "eclipselink.database.delimiters"="true"
> > > >
> > > >
> > > > Derek Knapp-3 wrote:
> > > > >
> > > > > I'm switching to exlipselink from toplink for some of the new 
> > > > features
> > > > > (mainly the batch and fetch query hints) however, I'm encountering 
> > > > one
> > > > > problem.. I have some entities with columns that have reserved 
> > > > names..
> > > > > for example, "to" and "from" (it is a table to store an email). The
> > > > > problem is with the following query.
> > > > >
> > > > > "select e from Email e where e.id = :id"
> > > > >
> > > > > in toplink, this wasn't a problem.. the query it would generate is
> > > > >
> > > > > "SELECT t0.id, t0.subject, t0.to, t0.from FROM email t0 where 
> > > > t0.id = 1"
> > > > >
> > > > > but in eclipselink, its not adding the t0. to the list of 
> > > > columns... > aka its generating the following sql
> > > > >
> > > > > "SELECT id, subject, to, from FROM email t0 where t0.id = 1"
> > > > >
> > > > > which is clearly invalid....
> > > > >
> > > > >
> > > > > is there any way in eclipselink to make it add the t0. ?  or is 
> > > > the only
> > > > > solution to rename the columns in the database (which would be a 
> > > > large
> > > > > amount of work :( )
> > > > >
> > > > >
> > > >
> > > >
> > > > -----
> > > > http://wiki.eclipse.org/User:James.sutherland.oracle.com James 
> > > > Sutherland
> > > > http://www.eclipse.org/eclipselink/
> > > > EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
> > > > TopLink
> > > > Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink ,
> > > > http://wiki.oracle.com/page/TopLink TopLink
> > > > Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 
> > > > TopLink ,
> > > > http://www.nabble.com/EclipseLink-f26430.html EclipseLink
> > > > Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
> > > _______________________________________________
> > > eclipselink-users mailing list
> > > eclipselink-users@xxxxxxxxxxx
> > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> > _______________________________________________
> > eclipselink-users mailing list
> > eclipselink-users@xxxxxxxxxxx
> > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users