Binding lists/arrays with carray extension

[lasselindqvist]

I wonder if it would be possible to compile the SQLite library with carray extension (https://sqlite.org/carray.html) and then support prepared statements with list/array parameters.

[lasselindqvist]

Relevant setArray call: https://github.com/xerial/sqlite-jdbc/blob/3d04d7df0c89240add2c92189adb30b6cb7e6ae0/src/main/java/org/sqlite/jdbc3/JDBC3PreparedStatement.java#L406 which is currently unused.

[lasselindqvist]

Would need a native binding similar to https://github.com/xerial/sqlite-jdbc/blob/3d04d7df0c89240add2c92189adb30b6cb7e6ae0/src/main/java/org/sqlite/core/NativeDB.java#L223 or https://github.com/xerial/sqlite-jdbc/blob/3d04d7df0c89240add2c92189adb30b6cb7e6ae0/src/main/java/org/sqlite/core/NativeDB.java#L236

and the actual binding like

https://github.com/xerial/sqlite-jdbc/blob/3d04d7df0c89240add2c92189adb30b6cb7e6ae0/src/main/java/org/sqlite/core/NativeDB.c#L1076

[gotson]

If my understanding is correct, this is used to perform IN conditions without having to use a temporary table to store the values?

[gotson]

I'm wondering what's the interest to support this in Java. The idea behind seems to be that you can directly point to an array you already have in C, thus avoid copying values.

In the case of java the copy would need to happen anyway to move data from the heap to native memory.

[lasselindqvist]

The array copying is not really the problem I had, just that any IN-clauses need to manually crafted now and prepared statements cannot be used. This project could use the carray stuff or not, but would be good if setArray was implemented, even if it did not use prepared statements internally, but would generate SQL statements with hardcoded list of values. Now the string "IN(1, 2, 3, 4)" part must be generated by the caller, instead of passing int-array or any collection of ints.

[gotson]

Echoing my comment on #415:

I checked on this today, and found out a few things:

• by default SQLite does not support arrays
• support is only available via a loadable extension carray
• the carray extension only supports those types:
  • 'int32'
  • 'int64'
  • 'double'
  • 'char*'

We would also need to implement support for java.sql.Array.

From what i could find, the only databases supporting this are:

• PostgreSQL
• HSQLDB
• H2