How to use Queries that do not start with "SELECT" but return a data set?
My DBA has prepared some custom SQL queries that he does not want to put into StoredProcs. So I want to execute them via a normal Query.executeResultList(). However, I noticed that if the query does not start with "SELECT", then DN only returns a boolean "TRUE".
For instance, the query looks something like the following:
tab1 AS (SELECT 1 col1),
tab2 AS (SELECT 2 col2)
JOIN tab2 ON tab1.col1 <> tab2.col2
From a quick look at the code, I see that the `org.datanucleus.store.query.Query#executeQuery` first parses the SQL Query to identify if it is a SELECT, BULK_UPDATE, BULK_DELETE, BULK_INSERT or OTHER. I presume a fix would be to update the `org.datanucleus.store.rdbms.query.SQLQuery` parser to somehow detect this, but it seems like that could be a little difficult to parse appropriately (many edge cases).
Alternatively, is there another JDO way to execute the query and get the result set? I noticed there is an example here (https://www.datanucleus.org/products/accessplatform/jdo/query.html#stored_procedures_as_sql), but that uses internal/DataNucleus libs/dependencies. Is there anyway while remaining implementation agnostic?