search

telefonicaid / fiware-cygnus

A connector in charge of persisting context data sources into other third-party databases and storage systems, creating a historical view of the context
https://fiware-cygnus.rtfd.io/
GNU Affero General Public License v3.0
64 stars 104 forks source link

PreparedStatement add objects misbehaviour. #1959

Open IvanHdzC opened 4 years ago

IvanHdzC commented 4 years ago

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 commented 4 years ago

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

AlvaroVega commented 4 years ago

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

IvanHdzC commented 4 years ago

@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 commented 4 years ago

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 commented 3 years ago

PreparedStatement is still in use in this loop: https://github.com/telefonicaid/fiware-cygnus/blob/29abcc598942f993feef957a21c07899037ad023/cygnus-common/src/main/java/com/telefonica/iot/cygnus/backends/sql/SQLBackendImpl.java#L631-L638

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).