tDBOutput using UUID as a key for UPDATE

[Anonymous]

Hi,

I'm facing an issue when trying to UPDATE records in a postgres table using a UUID field as the key for the update. I've tried feeding a string to the component, and I've also tried using an Object, having a tJavaRow set it to be a java.util.UUID. Here are the errors:

If row key (UUID) is set as an String and passed to tDBOutput:

Batch entry 0 UPDATE schema.table SET "field_name" = valueHere WHERE "id" = 4d7b30d6-8d59-4cf2-9a0f-feee68659779 was aborted.  Call getNextException to see the cause.

caused by: ERROR: operator does not exist: uuid = character varying

If row key (UUID) is set as an Object and a true UUID is passed to tDBOutput:

Exception in component tDBOutput_1 (Update_PG_Records)

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.UUID. Use setObject() with an explicit Types value to specify the type to use.

      at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1740)

Let me know if I'm missing something. Seems that the DB Type setting on the component is doing little to nothing.

Best,

Glen.

[Anonymous]

Hello,

Refer to https://stackoverflow.com/questions/17969431/postgres-uuid-jdbc-not-working

You pass a uuid String to the field. which should work with UUID type. Please try to convert the UUID string to a UUID type.

java.util.UUID.fromString()

[Anonymous]

Thank you very much for the reply @xdshi !

Unfortunately I had already gone down that path getting the second error I previously posted:

Exception in component tDBOutput_1 (Update_PG_Records)

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.UUID. Use setObject() with an explicit Types value to specify the type to use.

      at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1740)

I took some time to google that error and it is related to the PreparedStatement java class and the java.sql.Types explicit definition in the setObject method. Looking at the code instead of the designer I realized the issue was here:

Then I researched the Talend source code and I found the line of code that is causing this exception:

https://github.com/Talend/tdi-studio-se/blob/592be6abb185e95f88837022234eff83290b5d22/main/plugins/o...

I'm not sure why adding java.sql.Types.OTHER to the setObject is not counting as an explicit Types value but it would be my first guess. Let me know if I'm wrong in any of the previous statements.

Best,

Glen.

[Anonymous]

Hello,

Could you clarify in which Talend version/edition you are?

Best regards

Sabrina

[Anonymous · Accepted solution]

The problem is indeed PostgreSQL need a cast for object typed values because it is not clear what type the value actually is.

There is a trick to do so.

You send the data as UUID or also possible as String in a schema column typed as Object (as you already do) and add the cast to the SQL generation.

This can be done in the Advanced Settings of the component.

This way you simply replace the automatically generated placeholder SQL code for the uuid typed column with a placeholder containing a cast.

[Anonymous]

Thank you @lli !!!

I had not tried the casting in the replacement that way. It still failed when passing as an UUID object but worked like a charm feeding it as a string. The component displays the warning as if there were data type inconsistencies:

but the casting worked 100% and now I can update the table based on the primary key which is a UUID.

This is what the component looks like for reference:

[rmartin2]

Hello everyone !

I'm digging this thread from his grave since I have a similar issue but with slightly different setup.

Instead of having a Postgre "classic", I have a Postgre on Google Cloud Platorm in target.

So I'm connecting using a JDBC connector, and the Postgre driver. 

I'm having the same need : update a table with it's UUID.

None of the previous solution worked...

Here's a list of what I tried :

• jlolling solution with cast : I have a "search parameter 2 issue"
• building a string with the clause " my_id::text='my_row.id' " : nothing happens
• building a string with the clause " my_id='my_row.id'::uuid " : nothing happens
• plain text uuid : nothing happens
• using conversion with UUID functions : no change (even failing)
• parameter in the connection string : it break the connection because it's JDBC and not supported

Is someone able to update even a single row with a JDBC PostgreSQL target ? after 2 days of tricking Talend into some weird stuff, I've not managed to get it working, neither my colleague...

I read addiotional parameters in 7.2 for JDBC Postgre connection. May it resolve my issue ?

Note : If I take the request and put it in pgAdmin, it works like a charm, even with text, no need for uuid type cast

Thank you in advance for your help !