SNOW-1568475: SNOWFLAKE.CORTEX.COMPLETE(VARCHAR, ARRAY, OBJECT) do not seem to be supported via PreparedStatement

[bobpaulin]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?

   snowflake-jdbc-thin-3.16.1.jar

2. What operating system and processor architecture are you using?
   MacOS Arm64

3. What version of Java are you using?
   Java 21

4. What did you do?

I've setup the following prepared statement to call into the Complete function with multiple messages and options. However it doesn't appear to support JDBC ARRAY types with JSON data inside as the complete function requires. I've tried to work around this by passing the prompt parsed with an ARRAY type but this fails on the server side with an error stating ARRAY unsupported.

final String response;
final String statementSql = "SELECT SNOWFLAKE.CORTEX.COMPLETE(?, ?, ?) as response;",
try (final Connection connection = connectionService.getConnection();
                final PreparedStatement statement = connection.prepareStatement(statementSql)) {

            statement.setString(1, "snowflake-arctic");
            statement.setObject(2, "[{'role':'user', 'content': 'how does a snowflake get its unique pattern?'}]", Types.ARRAY);
            statement.setObject(3, {'guardrails': true}, Types.JAVA_OBJECT);
            statement.execute();

            final String promptResponseString;
            try (final ResultSet rs = statement.getResultSet()) {
                if (rs.next()) {
                    promptResponseString = rs.getString(1);
                } else {
                    throw new IOException("No Cortex Complete Results Found.");
                }
            }
        } catch (final SQLException | IOException e) {
            getLogger().error("Failed to generate prompt response from model [{}]", modelName, e);
        }

Other things I've tried:
Extend the SQLData object to create a data object to produce the cortex option object (3rd parameter)
Using createArrayOf however only simple types seem to be supported.

snowflake-jdbc/src/main/java/net/snowflake/client/jdbc/SnowflakeConnectionV1.java

Line 714 in 58054be

public Array createArrayOf(String typeName, Object[] elements) throws SQLException {

5. What did you expect to see?
   Prompt responses from the LLM
   What should have happened and what happened instead?
   I'm getting errors returned from snowflake stating the type I'm using are not supported.

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

7. What is your Snowflake account identifier, if any? (Optional)

[bobpaulin]

Hi I did figure out a work around to this issue using the PARSE_JSON function

SELECT SNOWFLAKE.CORTEX.COMPLETE(?, PARSE_JSON(?), PARSE_JSON(?)) as response;

Seems to work with 3 setString calls on the prepared statement.

Calls 2 and 3 require the String to be a parsed JSON array and JSON Object respectively.