PreparedStatement add objects misbehaviour.

[IvanHdzC]

When inserting JsonObjects as SQL objects into a PreparedStatement.

com.telefonica.iot.cygnus.backends.sql.SQLQueryUtils.addJsonValues

It's having trouble with some SQL objects (The ones identified are Timestamp with TZ and functions). The class apparently has no trouble but when connecting to the database the driver asks to casts objects to proper types.

[AlvaroVega]

related with https://github.com/telefonicaid/fiware-cygnus/pull/1960/files#diff-3a02eb677c6f317f1689e890ca3a6efae6983b4c4709f3d9539548e258e8d6b0R592

[AlvaroVega]

@IvanHdzC Currently timestamps are inserted as Strings or using objects? What is the best way?

[IvanHdzC]

@IvanHdzC Currently timestamps are inserted as Strings or using objects? What is the best way?

Actually that's the problem. I tried both (setObject and setString) but none of them worked.

There is an specific setTimestamp method, but we don't really know what kind of data is each one of the values to insert.

[AlvaroVega]

Ideally (jdbc native way) all work should be done by https://github.com/telefonicaid/fiware-cygnus/blob/master/cygnus-common/src/main/java/com/telefonica/iot/cygnus/backends/sql/SQLQueryUtils.java#L351
But currently cygnus is using old cygnus way of compose statement using strings.

[AlvaroVega]

PreparedStatement is still in use in this loop:

fiware-cygnus/cygnus-common/src/main/java/com/telefonica/iot/cygnus/backends/sql/SQLBackendImpl.java

Lines 631 to 638 in 29abcc5

	for (StringBuffer query : upsertQuerysList) {
	PreparedStatement upsertStatement;
	currentUpsertQuery = query.toString();
	upsertStatement = connection.prepareStatement(query.toString());
	// FIXME https://github.com/telefonicaid/fiware-cygnus/issues/1959
	upsertStatement.executeUpdate();
	upsertQuerys = upsertQuerys + " " + query;
	}

But is not really properly used, since each loop iteration is creating a new "preparedStatement" and the current iteration of loop is not using capabilities of a prepared statement (just change one o few value from created statement).