[Documentation] Enhance Documentation for Querying Multiple Parameters in Java API

[otaviojava]

I use the Oracle NoSQL Database and perform queries with multiple parameters using the Java API. However, I have encountered some issues with the provided code snippets in the documentation.

Problem:
The Oracle NoSQL Database documentation suggests using the following code snippet to prepare and execute a query with multiple parameters:

var oracleQuery = "";
PrepareRequest prepReq = new PrepareRequest().setStatement(oracleQuery);
PrepareResult prepRes = serviceHandle.prepare(prepReq);
PreparedStatement preparedStatement = prepRes.getPreparedStatement();
for (Map.Entry<String, FieldValue> entry : oracleQuery.params().entrySet()) {
    preparedStatement.setVariable(entry.getKey(), entry.getValue());
}

When attempting to use this code with the following query:

DECLARE $age INTEGER, $type STRING;  select * from database WHERE database.entity= 'person' AND   database.content.age  > $age AND  database.content.type  = $type LIMIT 1

Issue 1:
The code throws an exception: java.lang.IllegalArgumentException: PREPARE: Illegal Argument: Error: at (1, 20) no viable alternative at input 'DECLARE$ageINTEGER,', at line 1:20.

Issue 2:
When I modify the query to:

DECLARE $age INTEGER; DECLARE $type STRING;  select * from database WHERE database.entity= 'person' AND   database.content.age  > $age AND  database.content.type  = $type LIMIT 1

The code throws another exception: java.lang.IllegalArgumentException: PREPARE: Illegal Argument: Error: at (1, 30) missing '(' at '$type', at line 1:30.

Request:
I request the Oracle NoSQL Database documentation team to provide more detailed and accurate examples of performing queries with multiple parameters using the Java API. Specifically addressing the issues mentioned above and ensuring that the code snippets align with the syntax and usage of the Oracle NoSQL Database.

Documentation Reference:
The reference used for this query is: Oracle NoSQL Database Documentation

The table structure

CREATE TABLE if not exists database (id STRING, entity STRING, content JSON, primary key (id))

I appreciate your assistance in improving the documentation to make it more user-friendly and accurate. Thank you!

[dario-vega]

Hi @otaviojava

When having multiple variables, you need to use ; instead of , to separate the variables, and only one DECLARE. It is documented here

So the good query is

DECLARE $age INTEGER; $type STRING;  select * from database WHERE database.entity= 'person' AND   database.content.age  > $age AND  database.content.type  = $type LIMIT 1

Otherwise, the goal of preparedStatement is to Parse one and execute many; I will provide you with a better example.

Concerning your request. I will create an internal ticket for an update of the doc. Provide examples with multiple parameters when using the SDK and not with only one parameter.

Dario

[dario-vega]

When retrieving data, I recommend to read this GitHub discussion: https://github.com/oracle/nosql-examples/discussions/50

[Vandana-Rajamani]

@otaviojava Thanks for providing the feedback to improve the documentation on examples of performing queries with multiple parameters using the Java API. We are already working on this - to revise and enhance all the existing examples for using different SDKs to do various database operations. We will be publishing the updated docs for this soon!

[dario-vega]

We also support positional binding and the following syntax if you prefer

String query = "select * from database WHERE database.entity= 'person' AND   database.content.age  > ? AND  database.content.type  = ? LIMIT 1";
PrepareRequest prepReq = new PrepareRequest()
    .setStatement(query);
PrepareResult prepRes = serviceHandle.prepare(prepReq);

prepRes.getPreparedStatement()
     .setVariable(1, new IntegerValue(0))
     .setVariable(2, new StringValue("X"));
QueryRequest queryRequest = new QueryRequest()
     .setPreparedStatement(prepRes);

I will provide more insights into the difference between the two possibilities later - trade-offs

[dario-vega]

Hi @otaviojava

An important extract from the documentation of PreparedStatement

A single instance of PreparedStatement is thread-safe if bind variables are not used. If bind variables are to be used and the statement shared among threads additional instances of PreparedStatement can be constructed using copyStatement().

    public void executeQuery( NoSQLHandle serviceHandle, Map<String, FieldValue> params, String sql) {

        PreparedStatement preparedStatement = ensurePrepared(serviceHandle, sql)
                .copyStatement();
        QueryRequest queryRequest = new QueryRequest()
                .setPreparedStatement(preparedStatement);

        params.forEach((k, v) -> preparedStatement.setVariable(k, v));

        try (QueryIterableResult results = serviceHandle.queryIterable(queryRequest)) {
                for (MapValue res : results) {
                    System.out.println (res);
                }
        }

        return ;
    }

ensurePrepared is storing the SQL already prepared in a Map<String, PreparedStatement> prepMap = new HashMap<>();

The idea is to parse only one time and execute multiple times.

    public PreparedStatement ensurePrepared(NoSQLHandle serviceHandle, String statement) {
        PreparedStatement preparedStatement = prepMap.get(statement);
        if (preparedStatement == null) {
            synchronized (prepMap) {
                preparedStatement = serviceHandle.prepare(
                    new PrepareRequest().setStatement(statement))
                    .getPreparedStatement();
                prepMap.put(statement, preparedStatement);
            }
        }
        return preparedStatement;
    }