tDBOutput using UUID as a key for UPDATE

Five Stars

tDBOutput using UUID as a key for UPDATE

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.


Accepted Solutions
Seventeen Stars

Re: tDBOutput using UUID as a key for UPDATE

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.uuid_value.png

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

View solution in original post


All Replies
Moderator

Re: tDBOutput using UUID as a key for UPDATE

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()
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: tDBOutput using UUID as a key for UPDATE

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:

 

2019-07-03_17-07-57.png

 

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.

 

 

Moderator

Re: tDBOutput using UUID as a key for UPDATE

Hello,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: tDBOutput using UUID as a key for UPDATE

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.uuid_value.png

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

View solution in original post

Five Stars

Re: tDBOutput using UUID as a key for UPDATE

Thank you @jlolling !!!

 

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:

 

2019-07-06_9-17-18.png

 

 

2019-07-06_8-33-11.png

 

 

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:

 

2019-07-06_8-34-37.png

 

 

 

 

 

 

 

Seven Stars

Re: tDBOutput using UUID as a key for UPDATE

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 !

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Studio Improvements for API Services

Take a look at the Talend Studio improvements for API Services

Watch Now