Enhance PreparedStatement for Parameter Simplification

[otaviojava]

In the Oracle NoSQL Database, when using a PreparedStatement, users must define the FieldValue for each parameter, necessitating knowledge of the specific type for each parameter in the query. For example:

var preparedStatement = prepRes.getPreparedStatement();
preparedStatement.setVariable(1, new StringValue("someText"));

This approach can become cumbersome and error-prone when dealing with more complex queries, as users need to translate their values to the appropriate FieldTypes.

Desired Behavior:

We suggest enhancing the PreparedStatement in Oracle NoSQL Database to simplify parameter binding by allowing users to set parameters using a generic method, such as setParameter(Object field). It would make the PreparedStatement more intuitive and user-friendly, similar to how it is implemented in other NoSQL databases like MongoDB, Cassandra, and Neo4J.

Use Case:

A more user-friendly PreparedStatement would make it easier for developers to work with Oracle NoSQL Database, especially for complex queries where the internal database field types are not immediately apparent or relevant to the user.

References:

• MongoDB Java Driver - Query Document
• DataStax Cassandra Java Driver - Prepared Statements
• Neo4J Cypher Manual - Parameters
• Neo4J Java Reference - Query Parameters

Proposed Implementation:

Introduce a new method, setParameter(Object field), in the PreparedStatement API, which would accept an object and internally handle the translation to the appropriate FieldType, similar to how other NoSQL databases simplify parameter binding.

Additional Information:

This enhancement would improve the usability of Oracle NoSQL Database and align it with industry standards for parameter binding in prepared statements. It would also reduce the cognitive load on developers by abstracting away the internal details of field types.

[gmfeinberg]

Hi Otavio,

Thanks for the suggestion. Perhaps I'm missing something but the only one of the products mentioned that has a way to set unindexed parameters looks like Cassandra.

Oracle NoSQL supports both named and positional bind parameters. Named are set using (name, value) pairs. Positional are bound using (index, value) pairs where the index is the position of the "?" bind parameter in the query.

It appears Cassandra allows you to do using something like (value1, value2, ... valueN) in a list where the index of the value is implied by the position in the bind call. That seems handy.

If any of the products mentioned have a way of inferring the variable from only the type of the parameter please point me to that. I don't see how it's possible in a general case or when "?" is used vs named/typed variables.

Again, thanks for the conversation!
George

[otaviojava]

Thank you, @gmfeinberg. My point is not the query itself but the parameter, not the query.

Please, check the API

The unique form to declare an API uses this FieldValue. So, I need to make sure the type.

While another APIs, I don't need it, for example, Cassandra

[gmfeinberg]

Hi @otaviojava !

First, thank you for pointing out that the NoSQL javadoc at the link you used is ancient. I've notified our folks about that. It's from 2020! The most recent and accurate javadoc is published on GitHub with each release.

Please look at the current methods on PreparedStatement

Positional variables have been supported for a while. First, note that positional variables only work with the '?' syntax in a query vs an explicitly declared variable with name and type. Type checking is done when the query is executed. The current runtime would not easily (if at all) support positional variable assignment for named variables without creating an incompatibility with today's behavior. It's not clear if Cassandra mixes named and positional vars.

While there is currently no direct equivalent to setVariable(Object... vars) we now have setVariable(int, FieldValue). The addition of something like setVariable(FieldValue...vars) would be quite simple and could also be implemented in a small wrapper on top of the existing positional API.

We could technically add setVariable(Object... vars) as long as the list could be used to create a supported FieldValue types (e.g. numeric, string, boolean, even map or array).

Which method(s) would be of most use for you?

[dario-vega]

@otaviojava

Let me provide an example because I need clarification on what you expect- the desired behavior.

The SqlBuilder class (custom class - not in the API) automatically generates my table database's SQL statement, prepares, caches, binds, and returns the result. Behind the scenes, I am using named instead of positional binding parameters for better understanding. It works in both cases.

SqlBuilder sb = new SqlBuilder();

Map<String, FieldValue> param3 = new HashMap<>();
param3.put("$id", 1L);
sb.executeQuery (serviceHandle, param3, "WHERE $u.id = $id");

Map<String, FieldValue> param4 = new HashMap<>();
param4.put("$id",  "1");
sb.executeQuery (serviceHandle, param4, "WHERE $u.id = $id");

In this case, my SqlBuilder internally handles the translation to the appropriate type. It generates the following query (see the DECLARE)

• The Long constant 1L becomes DECLARE $id LONG; select * from database $u WHERE $u.id = $id
• The String constant "1" becomes DECLARE $id STRING; select * from database $u WHERE $u.id = $id

Of course, the following request generates the same queries, but you don't want to use LongValue and StringValue but something generic based on Java Primitive Data Type as above.

Map<String, FieldValue> param1 = new HashMap<>();
param1.put("$id", new LongValue(1));
sb.executeQuery (serviceHandle, param1, "WHERE $u.id = $id");

Map<String, FieldValue> param2 = new HashMap<>();
param2.put("$id", new StringValue("1"));
sb.executeQuery (serviceHandle, param2, "WHERE $u.id = $id");

Question

So, for you, is Object a Java Primitive Data Type? Or what other translation are you expecting?

Additional Information:

In fact, to do it, I am executing

Map<String, FieldValue> param3 = new HashMap<>();
param3.put("$id", new MapValue().put("x", 1L).remove("x"));

it returns a LongValue (based on the type of what I have in the put)

Map<String, FieldValue> param4 = new HashMap<>();
param4.put("$id", new MapValue().put("x", "1").remove("x"));

it returns a StringValue (based on the type of what I have in the put)

So instead of

var preparedStatement = prepRes.getPreparedStatement();
preparedStatement.setVariable(1, new StringValue("someText"));

I am using MapValue to obtain a LongValue, StringValue , .... depending on the Java Primitive Data Type of my variable

var preparedStatement = prepRes.getPreparedStatement();
preparedStatement.setVariable(1, new MapValue().put("x", "someText").remove("x"));

@gmfeinberg
Do we have another way to do it without using MapValue.put().remove()? What are the effects side behind it? I don't think loss of precision is important for bind variables.

Documentation:

https://oracle.github.io/nosql-java-sdk/oracle/nosql/driver/values/MapValue.html

MapValue put(String name, boolean value)
MapValue put(String name, byte[] value)
MapValue put(String name, double value)
MapValue put(String name, int value)
MapValue put(String name, long value)
MapValue put(String name, String value)
MapValue put(String name, BigDecimal value)
MapValue put(String name, Timestamp value)
FieldValue remove(String name) - Removes the named field if it exists.

[otaviojava]

In this case, my SqlBuilder internally handles the translation to the appropriate type. It generates the following query (see the DECLARE)

An SQL builder like that would be amazing. Cassandra has one almost like that.

So, for you, is Object a Java Primitive Data Type? Or what other translation are you expecting?

For me, as a user, it is. I would expect you, the driver provider, to convert appropriately to a convenient type or throw an exception.

For example, given the same setParameter(Object parameter);

- preparedStatement.setVariable(1, "someText"); -> it will convert to StringValue
- preparedStatement.setVariable(1, 1L); -> it will convert to LongValue
- preparedStatement.setVariable(1, new String[]{"someText"}); -> it will convert to ArrayValue
- preparedStatement.setVariable(1, NutSupportedtyple); -> Exception

etc.

[dario-vega]

Thanks. So my understanding was good and thanks for the feedback about the SQL builder.
My SQL builder is something that I did for an example. It is not as sophisticated as provided in the link.

By the way, I did my first query using Jakarta NoSQL fluent API and Oracle NoSQL

System.out.println("The Dario's first query result: ");
template.select(Beer.class)
        .where("style").eq("Pilsner")
        .and("malt").eq("Victory")
        .orderBy("hop").desc()
         .stream().forEach(System.out::println);