How to use Queries that do not start with "SELECT" but return a data set?


ebenzacar@...
 
Edited

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:

WITH 
 tab1 AS (SELECT 1 col1),
 tab2 AS (SELECT 2 col2)
SELECT
tab1.col1
   ,tab2.col2
FROM tab1
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 SELECTBULK_UPDATEBULK_DELETEBULK_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?

Thanks,

Eric


Andy
 

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(...)".