Talend community forum / Insert in postgresql not correctly formatted

Bertlit · 2012-03-19 16:49:26

hello,

I receive the following message when inserting data from a tMap component to outputPostGreSql
L'élément du batch 0 INSERT INTO "nom"."formatmessage" ("entetemessage","nommessage","nb_param","sm_nomspecification") VALUES (8CF00400,Electronic Engine Controller 1,5,J1939) a été annulé. Appeler getNextException pour en connaître la cause.
[statistics] disconnected

I've check on pgAdmin : pg would like something looking like :
INSERT INTO "nom"."variable"
("fm_entetemessage","nom","domainededefinition","unite","taillebrute","coefficient","offset")
VALUES ('8CF00400','engine retarder torque mode','dtEnum3Bit','',4,1.0,0)

I would even prefer if the 'taillebrute' '' would go NULL but that's no the most important.

Thanks

pedro · 2012-03-20 03:51:31

Hi

Could you show me a screenshot of your job?
I guess you are using tPostgresqlRow.
Why don't you use tPostgresqlOutput instead?

Regards,
Pedro

Bertlit · 2012-03-20 15:13:50

here is a shot
https://plus.google.com/photos/111890200716020940710/albums/5721980263078737777?authkey=CN6m8rrm8JK0jAE

but i do use an output component.
I believe the problem comes from the values not being between brackets?
I have from talend :

Code:

INSERT INTO "expe"."sessionenregistrement" ("idsessionenregistrement","me_idmachineexpe","debutenregistrement","tracegps","pathdonneessources","pathvideo1","pathvideo2","pathvideo3","pathvideo4") VALUES (0,18,2011-09-10 05:53:05.914000 +02:00:00,NULL,P:\Mes Documents\DataEffibox\2011-Sep-10 05-53-05.914546-UTC,NULL,NULL,NULL,NULL)

when with pgadmin i replace values like that :

Code:

VALUES (0,18,'2011-09-10 05:53:05.914000 +02:00:00',NULL,'P:\Mes Documents\DataEffibox\2011-Sep-10 05-53-05.914546-UTC',NULL,NULL,NULL,NULL)

it does work
I must be missing some configuration parameter but I have no clue where I should look

Last edited by Bertlit (2012-03-20 16:05:28)

Matouk · 2012-05-09 10:44:27

Thanks for other responses,
I desabled " USE the Batch Size" of tpostgresqlOutput in advanced setting and I run the job, then I got the errors message that indicate " value too long for charctere varing (20)- ie type varchar" and I increse the size of type varchar to 100 and then the problem is go out!!!!. I created my table postgesql and inert into.....
Best regards.

Matouk · 2012-05-31 14:26:05

Hi every one,
I am very stoped at now, I work with TOS for Big Data, posgtresql, when I read from hadoop (hdfs) or Hive , I want to insert into table postgresql (that I create in Talend but I did nont works) but this error below appears and no creation of table or insertion of data are made!!!??, and when I tried this by trowgenerator it works verry well<<, please, what is the problem???
I tested this whith other version (TOS v 5.1, TOS_ESB 5.0 ,TOS_DI v5.0.....) but it is the same, it dosn't work and the error is :
Exception in component tPostgresqlOutput_1
java.lang.NullPointerException

what is the problem please?
regards.

Matouk · 2012-06-08 10:28:50

Hello every one
I managed to debug what I was blocked on talend at insert in postgresql (do not put the component and the component tpostgresqlconnection, in the component tpostgresqloutput do not put schema of the database''public''-in my case, then control the commit with an addition of tpostgresqlcommit). and that's it!!
regrads

Opus · 2012-10-26 04:33:08

I have had this problem trying to insert data into a Postgresql database.

If you use a tPostGresqlConnection component to connect to the database and specify that component in a tPostgresqlOutput component using the 'Use an existing connection' option then you get a JavaNullPointer exception.

Walking through the code in debug mode you get to the point where the SQL insert statement is formed and the following line attempts to prepare the query via the tPostGresqlConnection connection variable. This variable has null despite the component having genrated a OnComponentOK condition of OK.

If you un-click the 'Use an existing connection' option and define the connection in the tPostgresqlOutput component then the data is inserted into the table successfully, however, the canvas does show a red error box around the tPostgresqlOutput even though there is no error reported in the console log or marked in the java code.

Can someone look into this because I'm about to do some development against a Postgresql database and I'm not keen on not being to use the tPostGresqlConnection component...

Thanks in advance

pfa.tunisiana · 2013-03-07 23:52:47

hello,
i need help with shapefile and talend, i want to insert shapefile data in postgresql db

adjidji · 2013-05-16 18:28:26

Hi,
I face the same problem when inserting data into a postgresql database.
The fact is when designing export, the generating SQL code for insertion put
table and column names into quote ".

Does it exist any way to modify manually the generating code ?

Announcement

Unanswered posts

#1 2012-03-19 16:49:26

Insert in postgresql not correctly formatted

#2 2012-03-20 03:51:31

Re: Insert in postgresql not correctly formatted

#3 2012-03-20 15:13:50

Re: Insert in postgresql not correctly formatted

Code:

Code:

#4 2012-05-09 10:44:27

Re: Insert in postgresql not correctly formatted

#5 2012-05-31 14:26:05

Re: Insert in postgresql not correctly formatted

#6 2012-06-08 10:28:50

Re: Insert in postgresql not correctly formatted

#7 2012-10-26 04:33:08

Re: Insert in postgresql not correctly formatted

#8 2013-03-07 23:52:47

Re: Insert in postgresql not correctly formatted

#9 2013-05-16 18:28:26

Re: Insert in postgresql not correctly formatted

Board footer