OQL query in java code

0
my applicaiton is existing code running on postgres. But now migrated to SQL server. There are database queries in  java actions using Core.retrieveOQLDataTable method. But there were errors on executing these queries, i assumed that is because of change in database, so syntax will be changed. But even simplest query wont run. Below is example. So if want to write OQL query in .java file using Core.retrieveOQLDataTable method, what will be the syntax? i keep getting parse errors in the code. "select Alias.ColName as ColName + " FROM \"Module$EntityName\" as Alias where Alias.Colname= 'value'"; I have tried Module.EntityName a well.
asked
3 answers
3

OQL in Mendix is a bit tricky, since it does not seem to have got a lot of attention since Mendix 3.x or so. The data set editor validation is far from giving you errorless OQL.

I usually use this pattern:

SELECT A1.attribute1 AS atr1 , A2.attribute2 AS atr2

FROM module.entity1 as A1
JOIN A1/module.association/module.entity2 as A2

WHERE
A1.attribute = 'string'

It is also best to use the proxies in Java, e.g

module.proxies.entity1.entityName

This asserts that you get naming errors at compile time and not at runtime.

... and as Rom said: be carful with alias names.

answered
1

First line of your oql query seems to be missing closing quotes. What are the error messages you get?

Maybe this blogpost has some useful info on executing (oql) queries from java: https://github.com/ako/QueryApiBlogPost

answered
0

Although not specifically mentioned in the documentation, I would always avoid using Alias as an alias-name: Alias is a reserved word in certain SQL implementation (not in Postgresql 9.4, according to the documentation), but it's not entirely clear what Mendix does in this case.

Furthermore, if you're worried about the syntax, you should create a data set in your modeler and write your OQL query there: if you have a syntactically valid OQL statement there, simply put it between quotes in Java and it should execute. You can also benefit from auto complete features when writing OQL in a data set.

answered