First background.
I am coding desktop application with Java as Eclipse RCP (efxclipse). My configuration:
Database table example:
CREATE TABLE MYTABLE (
ID CHAR(16) CHARACTER SET OCTETS COLLATE OCTETS NOT NULL CONSTRAINT PK_MY_INDEX PRIMARY KEY,
DATA VARCHAR(100)
);
HikariCP setup as
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(100);
config.setDataSourceClassName("org.firebirdsql.pool.FBSimpleDataSource");
config.addDataSourceProperty("databaseName", cfg.getDbConnection());
config.addDataSourceProperty("user", cfg.getDbUser());
config.addDataSourceProperty("password", cfg.getDbPassword());
config.addDataSourceProperty("encoding", "UTF8");
//config.addDataSourceProperty("nonStandardProperty", "?octetsAsBytes=true?");
config.setMaximumPoolSize(cfg.getDbPoolSizeMax());
HikariDataSource ds = new HikariDataSource(config);
ds.setConnectionTimeout(5000);
// I use UUID as primary keys and for get row I use code like
Connection con = ds.getConnection();
query = "SELECT DATA FROM MYTABLE WHERE ID=char_to_uuid(?)";
PreparedStatement p = con.prepareStatement(query);
p.setString(1, id);
where is "id" is string with value like '57F2B8C7-E1D8-4B61-9086-C66D1794F2D9'
Until last week I used Firebird 2.5.2xxx and Jaybird 2.2.8 on my computer and there are absolutely no problems with this setup. Then I have upgraded my firebird installation to version 2.5.5.26952 and problem appeared.
The problem:
After upgrading to Firebird 2.5.5 code part where I fetch row for known "id" throws exception
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544606. expression evaluation not supported
Human readable UUID argument for CHAR_TO_UUID must be of exact length 36
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:782) ~[na:na]
at org.firebirdsql.jdbc.AbstractPreparedStatement.executeQuery(AbstractPreparedStatement.java:177) ~[na:na]
at com.zaxxer.hikari.proxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:52) ~[na:na]
at com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.executeQuery(HikariPreparedStatementProxy.java) ~[na:na]
...
Caused by: org.firebirdsql.gds.GDSException: expression evaluation not supported
Human readable UUID argument for CHAR_TO_UUID must be of exact length 36
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2092) ~[na:na]
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2042) ~[na:na]
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1149) ~[na:na]
at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:232) ~[na:na]
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:774) ~[na:na]
Value of "id" is string at 36 char length (I checked and it worked until last week). I have switched to Jaybird 2.2.9 problem still here. As far as I understand this is related to something like sending parameters value as UTF8 string (length x 4) so I have altered my code looks like below:
p.setObject(1, id.getBytes(StandardCharsets.US_ASCII));
Again, no result, same issue.
Also this may be something related with Jaybird connection parameter octetsAsBytes but I could not manage to apply it right (through HikariCP). I have tried
config.addDataSourceProperty("nonStandardProperty", "?octetsAsBytes=true?");
at HikariCP setup but result did not change.
Thanks in advance.
I can reproduce this in Firebird 2.5.4, 2.5.5, and a recent snapshot of Firebird 3 (2.5.3 and earlier seem to be unaffected). I have been able to reproduce this and the problem seems to be in Firebird itself.
As commented earlier, a workaround is to cast the parameter to an explicit character set:
char_to_uuid(cast(? as char(36) character set utf8))
I have created a ticket in the Firebird tracker to report this: CORE-5062, and it will be fixed in Firebird 2.5.6 and Firebird 3 RC2