Date
1 - 2 of 2
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: 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 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? Thanks, Eric
|
|
|
|
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(...)".
|
|
|