How to use Queries that do not start with "SELECT" but return a data set?
Since RDBMS datastores each have their own dialect, and since they have such a wide variety of random stuff you can put into an SQL statement, it is necessary to categorise statements and use the appropriate JDBC execute method. As a result, "non standard" stuff like your statement won't be caught.
If you want to make that linked example (the call to "getInternalQuery().setType(...)") accessible via a standard JDO method you could contribute a GitHub PullRequest that provides an "extension" for the query type and specify the "type" through that, and then call "query.addExtension(...)".
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?