UUID datatype for PostgreSQL

[riesvantwisk]

 Hey All,

 

I added the UUID datatype to mapping_Postgres.xml by adding this line:

 

<dbType type="UUID" ignoreLen="true" ignorePre="true"   />

 

However what I am looking for is that JasperETL generates a prepared statement something in the line of :

 

java.sql.PreparedStatement pstmtInsert_tPostgresqlOutput_1 = conn_tPostgresqlOutput_1.prepareStatement("INSERT INTO ""

+ tableName_tPostgresqlOutput_1 + "" ("year","group","sub_group","num","image","descr","inst","uuid","lang_id") VALUES (?,?,?,?,?,?,?,?::uuid,?)");

 

As you can see I need to cast a String to a UUID type, How can I set this up??

 

regards,

Ries van Twisk

 

[riesvantwisk]

 To answer my own question here is my temporal sulation:

 

 

CREATE OR REPLACE FUNCTION public.equaluuidtext(a uuid, b text)

  RETURNS boolean AS

$BODY$

        DECLARE

    output boolean;

BEGIN

SELECT a=b::uuid into output;

return output;

END

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

 

CREATE OPERATOR = (PROCEDURE = public.equaluuidtext,  LEFTARG = uuid,  RIGHTARG = text);

[riesvantwisk]

 Alright,

 

I found a better way to do this, there are a couple of steps to take:

 

1) find the file with the name : db_output_bulk.skeleton

2) Modify the function getSqlStmt into the one below:

        public String getSqlStmt() {
         // RVT Add cast if needed for special types that is not available in JDBC
String cast="";        
if (this.column.getType() != null) {
if (this.column.getType().equals("UUID")) {
cast = "::uuid";
}
}    
            return this.sqlStmt + cast;
        }

3) replace the file mapping_Postgres.xml with the one below attached
 

4) Fine the directory JasperETL-macosx-carbon.app/Contents/MacOS/workspace/.JETEmitters/runtime/org/talend/designer/codegen/translators/database

and remove the postgresql directory within (it contains a lot of class files)

 

Now restart JasperETL and UUID can be selected and used.

 

Ries

PS: Forget about my above post... that was plain wrong