Support for multiple occurrences of named parameterized columns in queries

[varontron]

INSERT into TAB1 (col1, col2) select ('val1', ?n) from TAB2 where col2 = ?n;

In this query, col2 must be passed twice in the JSONParams DATA object array, but YADA stomps on the first value because it uses a HashMap:

j=[{"qname":"APP Query","DATA":[{"col2":123,"col2":456}]}]

[varontron]

As expected, the cause of this is that stleary/JSON-java doesn't support duplicate keys, nor should it (though this is a matter of opinion.) There are probably a few options:

value as json array

This syntax is for passing multiple values to a single occurrence of a column in a query, e.g., for an IN clause

j=["qname":"APP Query","DATA":[{"col2":[123,456]}]}]

This could work if the query is interrogated and no IN clause is present, but it might break if one of the occurrences of the column name was referenced in an IN clause. However, it might still work if the nested value was also an array:

/*
SQL:
INSERT into YADA_TEST (col1, col2) select ('val1',?i) where col2 in (?i)
*/

j=["qname":"APP Query","DATA":[{"col2":[123,[456,789]]}]}]

So maybe that is the answer; that nested arrays even for single values portend to the Service layer that there will be multiple occurrences of the column? Yes, otherwise YADA can't discern the intent.

Is an array required for every occurrance?

/* Standard IN clause syntax, 1 occurrence */
j=["qname":"APP Query","DATA":[{"col2":[123,456]}]}]

/* No IN clause, 2 occurrences, without the 2nd value in an array, it looks like IN-clause syntax */
j=["qname":"APP Query","DATA":[{"col2":[123,[456]]}]}]

/* Second occurrence is referenced by IN clause */
j=["qname":"APP Query","DATA":[{"col2":[123,[456,789]]}]}]

Does it actually matter if the nested array is present if there isn't an IN clause? Actually, no, because IN clause recognition is based on parsing, not parameter values or parameter-value syntax.

So here's the breakdown:

/* one occurrence, one value */
j=["qname":"APP Query","DATA":[{"col2":123}]}]

/* one occurrence, one value as string */
j=["qname":"APP Query","DATA":[{"col2":"123"}]}]

/* one occurrence, IN-clause, multiple values, array syntax */
j=["qname":"APP Query","DATA":[{"col2":[123,456]}]}]

/* one occurrence, IN-clause, multiple values, string syntax */
j=["qname":"APP Query","DATA":[{"col2":"123,456"}]}]

/* two occurrences, one value each, array syntax */
j=["qname":"APP Query","DATA":[{"col2":[123,456]}]}]

/* two occurrences, one referenced by IN clause, array syntax */
j=["qname":"APP Query","DATA":[{"col2":[123,[456,789]]}]}]

/* two occurrences, one referenced by IN clause, array syntax 
  nested array at index 0 is inconsequential, but should be supported
  to facilitate programmatic DATA array creation. 
*/
j=["qname":"APP Query","DATA":[{"col2":[[123],[456,789]]}]}]

/* two occurrences, one referenced by IN clause, mixed syntax 
Will this be possible? The query manager will have to recognize the
presence of the IN clause pertaining only to the index containing the
delimited string. Hmmm....  Is this going to be required for the other
syntaxes as well? Possibly.
*/
j=["qname":"APP Query","DATA":[{"col2":[123,"456,789"]}]}]

value as delimited string

This syntax is also for passing multiple values to a single occurrence of a column in a query, e.g., for an IN clause

j=["qname":"APP Query","DATA":[{"col2":"123,456"}]}]

value as nested object

j=["qname":"APP Query","DATA":[{"col2":{1:123,2:456}]}]

This is sensible and clear, and probably easier to code, but it irks me to use index values as keys.

Pros

1. Unambiguous
2. Intuitive
3. Concise
4. Non-conflicting with existing syntax

Cons

1. Irksome index values as keys

Well, I'll probably end up supporting both nested arrays and objects.

[christopheleroy]

All the proposed solutions are 'married to the fact' that this is a matter of positional parameter-ing (it is a really world, here). You need to know the position of which parameter in the query etc.
the YADA_1, YADA_2, ... trick already works.
can you mix name params and positional params at the moment?
e.g

INSERT into TAB1(col1,col2) select ('abc', ?n) from TAB2 where col2 = ?n and col3= ?v

DATA: [ {YADA_1:100, YADA_2:200, col3:'yes'}, {YADA_1: 101, YADA_2:200, col3: 'no'}]

I think:
DATA: [ {"col2:1",100, "col2:2", 200, col3: 'yes'}, {"col2:1",100", "col2:2": 201, col3: 'no'}]
is cute too...
a general support for "paramname:paramposition" would be cute, but would have to be thoroughly documented because there is an easy way to be confused too...