SNOW-906944: Implement PreparedStatement.setArray

[mftruso]

What is the current behavior?

Not implemented, not documented. Arrays must be converted to a String and then parsed back into an Array in Snowflake.

String query = "call some_procedure(strtok_to_array(?, ','))"
 
List args = ["a", "b", "c"]
PreparedStatement ps = conn.prepareStatement(query)
ps.setString(0, args.join(','))

What is the desired behavior?

Have direct support for Arrays by implementing setArray

String query = "call some_procedure(?)"
 
List args = ["a", "b", "c"]
PreparedStatement ps = conn.prepareStatement(query)
ps.setArray(0, args)

How would this improve snowflake-jdbc?

Parity with java.sql.PreparedStatement. Tighter integration with snowflake types. Less String manipulation.

References, Other Background

A previous issue was created for this but never acknowledged: #382

[sfc-gh-spanaite]

@sfc-gh-anugupta FYI

[sfc-gh-dszmolka]

hi and apologies it took so long - it looks like the functionality was implemented as part of structured types support , which change was just released yesterday with v3.16.0

[nikammerlaan]

Hey @sfc-gh-dszmolka, thank you for implementing! Unfortunately, I haven't been able to work out the intended way to use this. I created an Array instance from the connection using createArrayOf, but receive an opaque error from the server: Expression type does not match column data type, expecting ARRAY but got VARCHAR(1) for column {my column name} Additionally, I tried using setObject with a normal Java array, but did not have any success. Could you clarify how this is meant to be used?

[sfc-gh-dszmolka]

hi @nikammerlaan double checked with the team implementing the feature and per their feedback, should work if the Structured Types (public preview) feature is enabled on the server-side. Considering its documentation, it says it should be already available for all accounts.

An example which has been tested and worked:

        SnowflakePreparedStatementV1 stmt =
            (SnowflakePreparedStatementV1)
                connection.prepareStatement(
                    "INSERT INTO array_of_integers (arrayInt) SELECT ?;"); ) {

      statement.execute(" CREATE OR REPLACE TABLE array_of_integers(arrayInt ARRAY(INTEGER))");

      Array array = connection.createArrayOf("INTEGER", new Integer[] {1, 2, 3});
      stmt.setArray(1, array);
      stmt.executeUpdate();

can you please test it and see how it works for you?
If you still have any errors, I advise to open a new issue and not use a closed one (which is resolved since the ask was to implement setArray which is available and implemented). Thank you in advance !