topic Re: Postgres Select from one Inserto to Other Table (Exception) in Talend Studio

Postgres Select from one Inserto to Other Table (Exception)

Anonymous — Wed, 27 Jun 2012 04:58:48 GMT

Hi friends, please I did a lot of studies and searchs here in the forum and over all internet and could not solve this task.
I need to select from one table in one database to insert in another table in other database, both tables with the same structure.
Concerning the select things are doing right but once for insert in another one i have this message:

 connecting to socket on port 3851

 connected

Batch entry 0 INSERT INTO "res_partner" ("id","create_uid","create_date","write_date","write_uid","comment","ean13","color","date","active","lang","customer","credit_limit","user_id","name","title","company_id","website","parent_id","employee","supplier","ref","vat","opt_out","last_reconciliation_date","debit_limit","addr_fs_code","cnpj_cpf","inscr_mun","suframa","tipo_pessoa","inscr_est","legal_name","partner_fiscal_type_id","section_id") VALUES (481,40,2012-06-18 12:05:05.083000 -03:00:00,NULL,NULL,NULL,NULL,0,NULL,1,en_US,1,NULL,NULL,Recanto Brasil,NULL,1,NULL,NULL,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,J,NULL,NULL,NULL,NULL) was aborted.  Call getNextException to see the cause.

 disconnected

Below some screen:

Thanks for all help and guidance!

Re: Postgres Select from one Inserto to Other Table (Exception)

Anonymous — Wed, 27 Jun 2012 06:44:12 GMT

Hi
Are you familiar with how to do Java debug in Eclipse IDE?
If so, you'd better debug this Talend job in TOS to get the root reason from getNextException.
In fact, the root reason is usually that you didn't set the correct length for one or more columns in output table.
If you are still confused about your issue, I will try to tell you how to debug in TOS.
Regards,
Pedro

Re: Postgres Select from one Inserto to Other Table (Exception)

Anonymous — Thu, 28 Jun 2012 03:14:29 GMT

Hi, Pedro!

Glad on chat with you again! So, buddy.. the result is, I found some exceptions on nzjdbc.jar not installed and as required (try to download, but i couldn`t) and for final result the question over that exception resides on:

1) String data types, must be enclousured between '' (single quotes);
2) Date types and Boolean types also must be send into INSERT instruction between '' (single quotes);

How do I discorer that? I take the output and run against the Postgres console, using your hint over Java Debug where the field name was being rejected. So i analyse that and found the issue.

The question now is, may could you give some guidance to fix this? It means, how can I instruct the INSERT command to add '' (single quotes) on Boolean, String and Date data type?

Sample code:

Native Talend Output (do not works):

INSERT INTO TABLE("name","date","receive_newsletter") values(Sandro,2012-06-29 03:00:00,1)

Functional Talend Output (it works):

INSERT INTO TABLE("name","date","receive_newsletter") values('Sandro','2012-06-29 03:00:00','1')

Best regards and thank you so much once again!

Sandro Regis - Projetaty Soluções Open Source