class FileMetadata
{
Uuid id
Str basename
Str path
Int size
DateTime modified
Str sha1
...
The table exists in a PostgreSQL database. The table was created with:
CREATE TABLE filehash.file_metadata (
file_id UUID CONSTRAINT pk_file PRIMARY KEY,
dir_id UUID ,
file_name varchar(300) NOT NULL,
file_ext varchar(100) ,
file_path varchar(1000) NOT NULL,
file_dir varchar(1400) ,
file_size bigint NOT NULL,
file_modified timestamptz NOT NULL,
file_sha1 char(64) NOT NULL
)
When I try to run the prepared statement, it gives me this error message:
sql::SqlErr: Param name='file_id' class='fan.sys.Uuid'; Can't infer the SQL type to use for an instance of fan.sys.Uuid. Use setObject() with an explicit Types value to specify the type to use.
I just can't find documentation about the setObject() method it refers to. Where is it and how to use it? Also, what is preventing the UUID type to be inferred, please?
Full Stack Trace:
TEST FAILED
sql::SqlErr: Param name='file_id' class='fan.sys.Uuid'; Can't infer the SQL type to use for an instance of fan.sys.Uuid. Use setObject() with an explicit Types value to specify the type to use.
sql::StatementPeer.setParameters (StatementPeer.java:326)
sql::StatementPeer.execute (StatementPeer.java:218)
sql::Statement.execute (Statement.fan)
FileTracker2::FileMetadataDao.save (FileMetadataDao.fan:22)
FileTracker2::TestFileMetadata.testDao (TestFileMetadata.fan:17)
java.lang.reflect.Method.invoke (Method.java:597)
fan.sys.Method.invoke (Method.java:559)
fan.sys.Method$MethodFunc.callList (Method.java:204)
fan.sys.Method.callList (Method.java:138)
fanx.tools.Fant.runTest (Fant.java:191)
fanx.tools.Fant.test (Fant.java:110)
fanx.tools.Fant.test (Fant.java:32)
fanx.tools.Fant.run (Fant.java:284)
fanx.tools.Fant.main (Fant.java:327)
Cause:
sys::Err: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of fan.sys.Uuid. Use setObject() with an explicit Types value to specify the type to use.
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject (AbstractJdbc2Statement.java:1917)
org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject (AbstractJdbc3gStatement.java:36)
sql::StatementPeer.setParameters (StatementPeer.java:322)
sql::StatementPeer.execute (StatementPeer.java:218)
sql::Statement.execute (Statement.fan)
FileTracker2::FileMetadataDao.save (FileMetadataDao.fan:22)
FileTracker2::TestFileMetadata.testDao (TestFileMetadata.fan:17)
java.lang.reflect.Method.invoke (Method.java:597)
fan.sys.Method.invoke (Method.java:559)
fan.sys.Method$MethodFunc.callList (Method.java:204)
fan.sys.Method.callList (Method.java:138)
fanx.tools.Fant.runTest (Fant.java:191)
fanx.tools.Fant.test (Fant.java:110)
fanx.tools.Fant.test (Fant.java:32)
fanx.tools.Fant.run (Fant.java:284)
fanx.tools.Fant.main (Fant.java:327)
SlimerDudeWed 2 Sep 2015
Hi LightDye, the sql pod can't handle UUIDs - the SQL/Fantom Type Mapping docs only mention Bool, Buf, Date, DateTime, Decimal, Float, Int, Str & Time.
As for the mystery setObject() method, well that's a message from the Postgres driver, nothing to do with Fantom.
LightDyeWed 2 Sep 2015
Hi SlimerDude, thanks for pointing that out. The setObject method is part of Java's JDBC API:
Although UUID is known to Fantom, Java and PostgreSQL, the type get lost in translation. This blog post also explains what can be done (from Java, though)
The problem is that such method isn't (directly) accessible from Fantom's sql API, so it seems that I'll have to use a no-so-neat solution.
brianWed 2 Sep 2015
I think the fundamental problem is that JDBC doesn't actually support UUID as a type.
To enhance sql to support that would require fixing SqlUtil.java metohds fanToSqlObj, sqlToFanType, and toObj.
It looks like fixing fanToSqlObj to map fan.sys.Uuid to java.util.UUID might work in that direction?
But I am not sure how to go the other way, because JDBC won't reflect that object using the JDBC type constants.
LightDyeWed 2 Sep 2015
Hi Brian, thanks for replying. When I run these 2 lines:
[d443fa00-80a7-4ba2-a745-11883ed4eeb4]
WARNING: Cannot map uuid to Fan type
[bbf07121-39eb-4b23-ab11-3d14835af645]
Please note how appending "::text" allows me to retrieve the UUID generated by PostgreSQL as text rather than UUID and then Fantom didn't give me a warning.
On the second line of code I was experimenting with SQL Escape Sequences for JDBC and I still got the value, with a warning though because the database actually returned a UUID type this time. Either way I can get UUID values and handle them in Fantom no matter if I got them as text or as UUID, for example this works fine
record := db.sql("SELECT {fn uuid_generate_v4()}").query
echo(Uuid.fromStr(record[0].toStr))
The issue seems to be only when doing INSERTs but according to the post that I linked before, it would be just a matter of:
This code is simpler than you might expect. No need to transform the UUID as textual hex representation on the Java side. No need to cast the value on the Postgres side. By calling the ".setObject" method, and passing an actual UUID value, it all works.
But haven't checked out Fantom's code to try that.
LightDyeTue 8 Sep 2015
Just in case someone else come across this topic, I'd like to mention that one possibly good option is to have the UUID generated by the database if possible, not by Fantom. This can be easily done by defining a default value of the UUID column, like in the auto column in this table create statement:
CREATE TABLE test_uuid (
id UUID CONSTRAINT pk_id PRIMARY KEY,
auto UUID default uuid_generate_v4(),
created TIMESTAMPTZ default CURRENT_TIMESTAMP
);
The uuid_generate_v4() function from PostgreSQL can also be used in the INSERT statement, but I was doing it wrong by trying to use the JDBC escaping syntax for functions, like this:
...
db.sql("INSERT INTO test_uuid VALUES ({fn uuid_generate_v4()})").execute
...
Turns out that I was over-complicating things and the escaping wasn't needed at all. The correct form would be just:
...
db.sql("INSERT INTO test_uuid VALUES (uuid_generate_v4())").execute
...
I haven't looked at passing the Uuid instance all the way from Fantom through JDBC down to Postgres.
LightDye Wed 2 Sep 2015
Say I have this Fantom code:
addFile := db.sql( """insert into file_metadata (file_id, file_name, file_path, file_size, file_modified, file_sha1) values (@file_id, @file_name, @file_path, @file_size, @file_modified, @file_sha1) """).prepare addFile.execute(["file_id":meta.id, "file_name":meta.basename, "file_path":meta.path, "file_size":meta.size, "file_modified":meta.modified, "file_sha1":meta.sha1])The "meta" object is an instance of
class FileMetadata { Uuid id Str basename Str path Int size DateTime modified Str sha1 ...The table exists in a PostgreSQL database. The table was created with:
When I try to run the prepared statement, it gives me this error message:
I just can't find documentation about the setObject() method it refers to. Where is it and how to use it? Also, what is preventing the UUID type to be inferred, please?
Full Stack Trace:
Cause:
SlimerDude Wed 2 Sep 2015
Hi LightDye, the sql pod can't handle UUIDs - the SQL/Fantom Type Mapping docs only mention
Bool,Buf,Date,DateTime,Decimal,Float,Int,Str&Time.As for the mystery
setObject()method, well that's a message from the Postgres driver, nothing to do with Fantom.LightDye Wed 2 Sep 2015
Hi SlimerDude, thanks for pointing that out. The setObject method is part of Java's JDBC API:
Java PreparedStatement interface - setObject
Although UUID is known to Fantom, Java and PostgreSQL, the type get lost in translation. This blog post also explains what can be done (from Java, though)
The problem is that such method isn't (directly) accessible from Fantom's sql API, so it seems that I'll have to use a no-so-neat solution.
brian Wed 2 Sep 2015
I think the fundamental problem is that JDBC doesn't actually support UUID as a type.
To enhance sql to support that would require fixing SqlUtil.java metohds fanToSqlObj, sqlToFanType, and toObj.
It looks like fixing fanToSqlObj to map fan.sys.Uuid to java.util.UUID might work in that direction?
But I am not sure how to go the other way, because JDBC won't reflect that object using the JDBC type constants.
LightDye Wed 2 Sep 2015
Hi Brian, thanks for replying. When I run these 2 lines:
echo(db.sql("SELECT uuid_generate_v4()::text").query) echo(db.sql("SELECT {fn uuid_generate_v4()}").query)I get good results:
Please note how appending "::text" allows me to retrieve the UUID generated by PostgreSQL as text rather than UUID and then Fantom didn't give me a warning.
On the second line of code I was experimenting with SQL Escape Sequences for JDBC and I still got the value, with a warning though because the database actually returned a UUID type this time. Either way I can get UUID values and handle them in Fantom no matter if I got them as text or as UUID, for example this works fine
record := db.sql("SELECT {fn uuid_generate_v4()}").query echo(Uuid.fromStr(record[0].toStr))The issue seems to be only when doing INSERTs but according to the post that I linked before, it would be just a matter of:
But haven't checked out Fantom's code to try that.
LightDye Tue 8 Sep 2015
Just in case someone else come across this topic, I'd like to mention that one possibly good option is to have the UUID generated by the database if possible, not by Fantom. This can be easily done by defining a default value of the UUID column, like in the auto column in this table create statement:
The uuid_generate_v4() function from PostgreSQL can also be used in the INSERT statement, but I was doing it wrong by trying to use the JDBC escaping syntax for functions, like this:
... db.sql("INSERT INTO test_uuid VALUES ({fn uuid_generate_v4()})").execute ...Turns out that I was over-complicating things and the escaping wasn't needed at all. The correct form would be just:
... db.sql("INSERT INTO test_uuid VALUES (uuid_generate_v4())").execute ...I haven't looked at passing the Uuid instance all the way from Fantom through JDBC down to Postgres.