Fóruns

Início » Liferay Portal » English » 3. Development

Visualização combinada Visão plana Exibição em árvore
Tópicos [ Anterior | Próximo ]
toggle
Pete Helgren
How to debug HQL syntax error
9 de Abril de 2013 14:37
Resposta

Pete Helgren

Ranking: Regular Member

Mensagens: 126

Data de entrada: 7 de Abril de 2011

Mensagens recentes

I have a service builder method that runs a query like this:

ClassGeolocationPersistenceImpl persistence = (ClassGeolocationPersistenceImpl) classGeolocationPersistence;
Session session = persistence.openSession();
try {
Query query = session.createQuery(sqlString);
locations = query.list();
.....

The SQL syntax I am using in the string is:

SELECT * FROM ClassGeolocation WHERE ((HC_Latitude * 0.0174532925) >= 0.4827596525676906 AND (HC_Latitude*0.0174532925) <= 0.5455440462509528) AND ((HC_Longitude*0.0174532925) >= -1.7556479311452713 AND (HC_Longitude*0.0174532925) <= -1.6835370398002323) AND acos(sin(0.5141518494093217) * sin((HC_Latitude*0.0174532925)) + cos(0.5141518494093217) * cos((HC_Latitude*0.0174532925)) * cos((HC_Longitude*0.0174532925) - -1.7195924854727518)) <= 0.031392196841631075;

I can run this SQL statement successfully using SQuirreL so I know the the SQL syntax is OK but I don't know if the HQL syntax is OK and the stack trace isn't nice enough to give me an offset or any indication of where the problem occurs or what the resultant HQL that is being processed looks like.

How does one debug the HQL? Is there a way to trace the statement. The error thrown is:
ERROR [JDBCExceptionReporter:101] Incorrect syntax near ')'.

Since there are multiple ")"'s in the statement the error isn't helpful.

Suggestions? (I looked for something like an HQL "lint" editor but didn't find one, or any tools to debug the statement). FWIW I am running LR 6.0.6 Glassfish bundle on Windows 7 developing in MyEclipse IDE
Pete Helgren
RE: How to debug HQL syntax error
19 de Abril de 2013 08:15
Resposta

Pete Helgren

Ranking: Regular Member

Mensagens: 126

Data de entrada: 7 de Abril de 2011

Mensagens recentes

I was never able to figure out what Hibernate didn't like about that syntax. Totally baffled.... and I was never able to figure out how to see the HQL that was being submitted to Hibernate for processing so I gave up with using the the createQuery method and went instead with the createSQLQuery approach. That worked, BUT the side effect is that query.list(); returns an array of objects for each record returned so it is a collection of arrays of objects. That means that you will need to iterate like so:

while (i.hasNext())
...
Object[] myObject = (Object[]) i.next();

then walk though the array and cast each object into the correct type and use the setters on your SB pojo to populate the object. It is tedious, but it works.

Not sure what about the query syntax Hibernate was cranky about....
David H Nebinger
RE: How to debug HQL syntax error
19 de Abril de 2013 09:42
Resposta

David H Nebinger

Ranking: Liferay Legend

Mensagens: 7252

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

Pete Helgren:
That means that you will need to iterate like so:

while (i.hasNext())
...
Object[] myObject = (Object[]) i.next();

then walk though the array and cast each object into the correct type and use the setters on your SB pojo to populate the object. It is tedious, but it works.


No, what it means is you should be passing in a ResultTransformer instance to handle the marshalling.
Pete Helgren
RE: How to debug HQL syntax error
19 de Abril de 2013 10:42
Resposta

Pete Helgren

Ranking: Regular Member

Mensagens: 126

Data de entrada: 7 de Abril de 2011

Mensagens recentes

Sigh .... so much to learn about LR...

Thanks for the pointer! I wish I had known that ahead of time (30 columns of mapping is a hassle)....
David H Nebinger
RE: How to debug HQL syntax error
19 de Abril de 2013 10:56
Resposta

David H Nebinger

Ranking: Liferay Legend

Mensagens: 7252

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

ResultTransformer will still do the same 30 column transforms, but it's a cleaner way of handling the marshalling...