Postgresql DB connection additional parameter problem

[Anonymous]

Hi All,
Please excuse me if I am being daft, but this is my first run with Talend and I can't seem to find the solution and have been trying to solve the issue for the last few hours to no avail and I have to amit I am getting a bit frustrated as I have alot more still to do.
Basically the crux of it is that I need to create a project utilising a Postgresql database that uses UUIDs for the primary keys, and I am having a problem with the tPostgresqlOutput throwing the following error:
org.postgresql.util.PSQLException: ERROR: column ... is of type uuid but expression is of type character varying
From debugging I noticed that it was because TOS generates a PreparedStatement with setString() for the UUID fields, so I altered the schema to use Object instead for these fields but then the server complains it can't infer the SQL type as the generated code uses the two parameter version of setObject() rather than the three parameter version supplying java.sql.Types.OTHER as the third parameter. As a quick test I altered the the relevant setObject methods to the three parameter version and the job ran fine. However as soon as I went back to designing the job any alterations overwrote my changes and I was back to square one.
To alleviate the problem I thought I set the UUID fields back to strings and use the Postgresql connection string parameter stringtype=unspecified as this normally works great; I also though this would save me some hassle with other jobs that use the connection as I didn't want to have to keep adjusting the code. However when I tried to adjust the DB connection the "additional parameters" field is greyed out (this seem to be the case for most databases?) and I couldn't see anywhere on the component itself to adjust the connection URL. As I though this could be a problem with 3.0.3 I upgraded to 3.0.4, but it has the same issue. As a final attempt I adjusted the URL in the generated code through the debugger, but then it wouldn't start at all complaining of connection problems; it seems that Talend didn't like any adjustments to the URL.
Can anyone give me some advice on how to proceed to solve this issue. I don't understand why the connection parameters are greyed out, as what happens if I needed to others such as ssl=true?
Any help appreciated...
Caza

[Anonymous]

Hello Caza
What's the data type of uuid field in table?
TOS is rather friendly and you don't need change the additional connection parameters. You only need to fill in the connection parameters and define the schema correspond with the table in DB.(About the data type mapping between Talend type and db type, you can go to Windows-->Preference-->Talend-->Metadata of TalendType and see mapping_Postgres.xml)

Best regards
shong

[Anonymous]

Hi Shong,
I've tried both String and Object for the data type of the UUID field in the table, but to no avail due to the code generated as explained in my previous post. This is why I wanted to add the stringtype=unspecified connection parameter as this normally resolves the problem for me. I did take a look at the mapping_Postgres.xml and did not see any reference to UUID, how could I adjust this file to alleviate my problem?
Using Object data type the code generated, for example is:
...
pstmt_tPostgresqlOutput_2.setObject(1, row4.id);
...
this causes postgres to complain that the SQL type cannot be inferred. However if I manually change it to
...
pstmt_tPostgresqlOutput_2.setObject(1,row4.id, Types.OTHER);
...
it works fine. Changing the data type to string causes the "ERROR: column 'id' is of type uuid but expression is of type character varying" for the obvious reason and is why I would need to set stringtype=unspecified.
As a side question if i can't add extra connection parameters to the URL how would I activate SSL for example?
Regards,
Caza

[Anonymous]

Hello Caza

I did take a look at the mapping_Postgres.xml and did not see any reference to UUID, how could I adjust this file to alleviate my problem?

Yes, you are right. Currently, there is not UUID data type referenced in the mapping file. You try to modify this file, add two lines:

<mapping>
	<dbms product="POSTGRESQL" id="postgres_id" label="Mapping Postgres"
		default="true">
		<dbTypes>
			<dbType type="ABSTIME" ignoreLen="true" ignorePre="true" />
			<dbType type="ACLITEM" ignoreLen="true" ignorePre="true" />
.
.
.
	<dbType type="_VARCHAR" ignoreLen="true" ignorePre="true" />
			<dbType type="_XID" ignoreLen="true" ignorePre="true" />
            <dbType type="_UUID" ignoreLen="true" ignorePre="true" />            //add this line
 		</dbTypes>

<language name="java">
			<talendToDbTypes><!-- Adviced mappings  -->
				<talendType type="id_List"/>
				<talendType type="id_Boolean">	
.
.
.<talendType type="id_Object">
					<dbType type="_POINT" />
					<dbType type="_POLYGON" />
.
.
.
	<dbType type="_PATH" />
					<dbType type="POINT"  />	  // delete 	default="true"
                    <dbType type="UUID" default="true" />	//add this line		
				</talendType>

Best regards
shong

[Anonymous]

Hi Shong,
I tried you adjustments but unfortunatly to no avail i still get the following error:
Exception in component tPostgresqlOutput_2
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 also tried adding
<dbType type="_VARCHAR" ignoreLen="true" ignorePre="true" />
<dbType type="_XID" ignoreLen="true" ignorePre="true" />
<dbType type="_UUID" ignoreLen="true" ignorePre="true" />
<dbType type="UUID" ignoreLen="true" ignorePre="true" /> //added this line
</dbTypes>
...
<talendType type="id_Object">
<dbType type="_PATH" />
<dbType type="POINT" />
<dbType type="_UUID" /> // added this line
<dbType type="UUID" default="true" />
</talendType>
but it still didn't work, is there anything further I can try as from the errors I am seeing it is not the mappings that is causing the error but the generated code of the setObject() not including Type.OTHER?
Regards,
Caza

[Anonymous]

Hi again,
I still haven't been able to resolve this problem, is there just a way to either edit the conneciton URL's additional parameters (probably the simplest way), edit the code generator so that it adds Type.OTHER, or stop Talend overwriting my edits to the generated java file i.e. by annotations? I have been trying to look into this myself but being a newbie it is difficult debugging a new tool that I haven't got to grips with, especially when I can't play with the technology as it won't play nice with the DB? If I can't solve it it looks as though I'll have to abandon Talend as a ETL tool and look elsewhere
I have to be honest in that I don't understand why the ability to edit the connection URL's additional parameters has been restricted for a PostgreSQL DB (i.e. greyed out) and not for DBs such as MySQL; for example there may come a point where I have to deal with secure data and would need to add "ssl=true" to the connection string, however as far as I can see it I would not be able to work with this data using Talend?
I have just noticed that there is a "General JDBC" option and I am going to see if I can get round the probelms that way as it seems I can set any Connection URL string...
Regards,
Caza

[Anonymous]

Hi all,
Just as an update I have managed to get it working using the "General JDBC" connection and adding "stringtype=unspecified" to the connection string. However you still need to use the Object type in the schema, because if you continue to use String as the Type you will have problems when a null encountered as Talend generates the following setNull(row,java.sql.Types.VARCHAR) which causes the "ERROR: column ... is of type uuid but expression is of type character varying" issue.
Regards,
Caza