jdbc: Data Type Conversion

[nicktorwald]

To be more compatible with JDBC 4.2 spec we need to provide implicit type conversions when setting parameters or retrieving values at minimum according to Appendix B.

This issue affects the following API:

• PreparedStatement.setXXX family of methods
• ResultSet.getXXX family of methods.
• DataBaseMetaData.supportsConvert

[nicktorwald]

Related case:

prep = conn.prepareStatement("INSERT INTO test(id, val) VALUES (?, ?)");

HashMap<String, String> map = new HashMap<>();
map.put("foo", "bar");

prep.setInt(1, 1);
prep.setObject(2, map);
prep.execute();

will cause

java.sql.SQLException: Failed to execute SQL: INSERT INTO test(id, val) VALUES (?, ?), params: [2, {foo=bar}]

	at org.tarantool.jdbc.SQLConnection.executeWithNetworkTimeout(SQLConnection.java:557)
	at org.tarantool.jdbc.SQLConnection.execute(SQLConnection.java:531)
	at org.tarantool.jdbc.SQLStatement.executeInternal(SQLStatement.java:437)
	at org.tarantool.jdbc.SQLPreparedStatement.execute(SQLPreparedStatement.java:256)
    ...
Caused by: org.tarantool.TarantoolException: Parameter 'foo' was not found in the statement
	at org.tarantool.TarantoolBase.serverError(TarantoolBase.java:32)
	at org.tarantool.TarantoolClientImpl.complete(TarantoolClientImpl.java:490)
	at org.tarantool.TarantoolClientImpl.readThread(TarantoolClientImpl.java:435)
	at org.tarantool.TarantoolClientImpl.lambda$startThreads$1(TarantoolClientImpl.java:215)
	at java.base/java.lang.Thread.run(Thread.java:834)

or if a map is empty or has more than one entry:

java.sql.SQLException: Failed to execute SQL: INSERT INTO test(id, val) VALUES (?, ?), params: [2, {}]

	at org.tarantool.jdbc.SQLConnection.executeWithNetworkTimeout(SQLConnection.java:557)
	at org.tarantool.jdbc.SQLConnection.execute(SQLConnection.java:531)
	at org.tarantool.jdbc.SQLStatement.executeInternal(SQLStatement.java:437)
	at org.tarantool.jdbc.SQLPreparedStatement.execute(SQLPreparedStatement.java:256)
	...
    
Caused by: org.tarantool.TarantoolException: Invalid MsgPack - SQL bind parameter
	at org.tarantool.TarantoolBase.serverError(TarantoolBase.java:32)
	at org.tarantool.TarantoolClientImpl.complete(TarantoolClientImpl.java:490)
	at org.tarantool.TarantoolClientImpl.readThread(TarantoolClientImpl.java:435)
	at org.tarantool.TarantoolClientImpl.lambda$startThreads$1(TarantoolClientImpl.java:215)
	at java.base/java.lang.Thread.run(Thread.java:834)

Because the driver passes a map parameter "as-is" :

tarantool-java/src/main/java/org/tarantool/jdbc/SQLPreparedStatement.java

Lines 416 to 426 in 9392133

	private Object[] toParametersList(Map<Integer, Object> parameters) throws SQLException {
	Object[] objects = new Object[parameters.size()];
	for (int i = 1; i <= parameters.size(); i++) {
	if (parameters.containsKey(i)) {
	objects[i - 1] = parameters.get(i);
	} else {
	throw new SQLException("Parameter " + i + " is missing");
	}
	}
	return objects;
	}

and the iproto expects the following format for bindings:

A parameter can be ordinal or named. An ordinal parameter is encoded as a message pack scalar value (MP_UINT, INT, DOUBLE, FLOAT, STR, BIN, EXT, NIL). A named parameter is encoded as a map with one string key – its name. For bindings syntax, see https://sqlite.org/lang_expr.html#varparam

Examples:

[1, 2, {'name': 300}] = MP_ARRAY[ MP_UINT, MP_UINT, MP_MAP{ MP_STR : MP_UINT } ]

Thus, an ordinal parameter of Map type is serialized as a map and recognized as a named parameter by the server according to the iproto SQL binary protocol.