Problem binding TIMESTAMP

[ptamburro]

Problem using a CURRENT_TIMESTAMP() spanner function.

Example. INSERT INTO providers (provider_id, created, device_id, document_number, document_type_id, name, notification, phone, updated, last_access, deleted, token_fcm) VALUES('000000', CURRENT_DATE(), 'not-device-id', null, null, 'provider-promocion', false, null, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), false, 'not-token');

this query produces a SQL Error: Internal jdbc driver error SQL Error: Internal jdbc driver error SQL Error: Internal jdbc driver error SQL Error: Internal jdbc driver error Internal jdbc driver error Incomplete binding for column created Incomplete binding for column created

[olavloite]

The problem is that the driver is trying to convert the entire insert statement into a Cloud Spanner mutation without a roundtrip to the database. This is not possible because of the CURRENT_DATE() and CURRENT_TIMESTAMP() calls. Try this instead:

INSERT INTO providers
(provider_id, created, device_id, document_number, document_type_id, name, notification, phone, updated, last_access, deleted, token_fcm)
SELECT '000000', CURRENT_DATE(), 'not-device-id', null, null, 'provider-promocion', false, null, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), false, 'not-token'
;

This will force the driver to do a roundtrip to the server and fetch the values for CURRENT_TIMESTAMP.

[olavloite]

I have added a more descriptive error message for the next version, but you need to change the code into using a INSERT INTO ... SELECT kind of statement, or you could use the Cloud Spanner feature of commit timestamp columns: https://cloud.google.com/spanner/docs/commit-timestamp#overview

Have a look at this test class for an example on how to use this with the JDBC driver: https://github.com/olavloite/spanner-jdbc/blob/master/src/test/java/nl/topicus/jdbc/test/integration/specific/CommitTimestampIT.java

Also, have you considered creating a test account for my Spanner Emulator: https://github.com/olavloite/spanner-emulator-tester. It is still in beta testing, so you should expect some issues with it, but it makes it a lot easier to create (integration) tests for your Cloud Spanner code as it is a lot faster in creating and dropping tables than a real Spanner instance.

[olavloite]

A more descriptive error message has been added for the next version.

[olavloite]

Fix (more descriptive error message) released in version 1.1.1.