Hi,
I am trying to transform mssql database table to postgre. While transforming i am getting following error. After a long search on google as well I couldnt find the solution there.
Here is the error code that I am getting.
I am using talend open studio v5.3.1
Can anybody help??
Thanks in advance.
Starting job ApplicationBehaviorIncidents at 15:07 22/07/2013.
connecting to socket on port 3568
connected
Exception in component talendStats_DB
org.postgresql.util.PSQLException: ERROR: zero-length delimited identifier at or near """"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:328)
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.talendStats_STATSProcess(ApplicationBehaviorIncidents.java:3556)
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.runJobInTOS(ApplicationBehaviorIncidents.java:4907)
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.main(ApplicationBehaviorIncidents.java:4762)
Exception in component talendStats_DB
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getTables(AbstractJdbc2DatabaseMetaData.java:2062)
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.talendStats_STATSProcess(ApplicationBehaviorIncidents.java:3535)
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.runJobInTOS(ApplicationBehaviorIncidents.java:4945)
Batch entry 0 INSERT INTO "public"."ApplicationBehaviorThreatIncidents" ("COMPUTER_ID","INCIDENT_ID","DETECTION_SCORE","WHITELIST_REASON","ALERTDATETIME","CONFIDENCE","RISK_LEVEL","URL","APP_NAME","APP_VERSION","APP_TYPE","APP_HASH","HASH_TYPE","COMPANY_NAME","REPEAT_COUNT","FILEPATH","DESCRIPTION","ACTUALACTION_IDX","REQUESTEDACTION_IDX","USER_NAME","RECORD_INSERT_TIME") VALUES (9507E394-0AD8-0D0C-0068-68CC15A5F2B9,9826DD8C0AD80D0C01EDDEB1FD9AE537,3,0,2012-07-11 07:31:07.000000 +05:30:00,10,4,,socar.exe,,0,D100D03C272E777CAB6FF9F4CE65BE59ABB1BFD4543021BA0E80110C98BD7D3D,2,,1,c:\users\administrator\desktop\socar\socar.exe,"",1,1,Administrator,1341991938620) was aborted. Call getNextException to see the cause.
842 milliseconds
disconnected
at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.main(ApplicationBehaviorIncidents.java:4762)
Job ApplicationBehaviorIncidents ended at 15:07 22/07/2013.
Hi, Could you paste your sql query into forum? Is your work flow tMssqlinput-->tpostgreSqloutput? Trouble screenshot will be preferred. Best regards Sabrina
Yes. The query flow is right.
Can't figure out how to upload the trouble screenshot.
Anyways I have a ms sql database table which I want to transform into postgre.
I have all the connection properties in place. Its working fine.
But the entity is not getting transformed into postgresql.
My Query code for tMssqlinput:-
" SELECT CONVERT(uniqueidentifier,LEFT(, 😎 + '-' +RIGHT(LEFT(, 12), 4) + '-' +RIGHT(LEFT(, 16), 4) + '-' +RIGHT(LEFT(, 20), 4) + '-' +RIGHT(, 12)) AS , . AS , , , . AS , , , , , , , , , , AS , , , , , , . AS FROM . WITH (NOLOCK) LEFT JOIN . WITH (NOLOCK) ON . = . LEFT JOIN . WITH (NOLOCK) ON . = ., . WITH (NOLOCK) WHERE . = 'Heuristic Scan' AND . != 9 AND . = . AND . >= 1239408000000 ORDER BY . ASC "
and the error code generated is
Starting job ApplicationBehaviorIncidents at 15:07 22/07/2013.
connecting to socket on port 3568 connected Exception in component talendStats_DB org.postgresql.util.PSQLException: ERROR: zero-length delimited identifier at or near """" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:328) at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.talendStats_STATSProcess(ApplicationBehaviorIncidents.java:3556) at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.runJobInTOS(ApplicationBehaviorIncidents.java:4907) at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.main(ApplicationBehaviorIncidents.java:4762) Exception in component talendStats_DB org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235) at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getTables(AbstractJdbc2DatabaseMetaData.java:2062) at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.talendStats_STATSProcess(ApplicationBehaviorIncidents.java:3535) at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.runJobInTOS(ApplicationBehaviorIncidents.java:4945) Batch entry 0 INSERT INTO "public"."ApplicationBehaviorThreatIncidents" ("COMPUTER_ID","INCIDENT_ID","DETECTION_SCORE","WHITELIST_REASON","ALERTDATETIME","CONFIDENCE","RISK_LEVEL","URL","APP_NAME","APP_VERSION","APP_TYPE","APP_HASH","HASH_TYPE","COMPANY_NAME","REPEAT_COUNT","FILEPATH","DESCRIPTION","ACTUALACTION_IDX","REQUESTEDACTION_IDX","USER_NAME","RECORD_INSERT_TIME") VALUES (9507E394-0AD8-0D0C-0068-68CC15A5F2B9,9826DD8C0AD80D0C01EDDEB1FD9AE537,3,0,2012-07-11 07:31:07.000000 +05:30:00,10,4,,socar.exe,,0,D100D03C272E777CAB6FF9F4CE65BE59ABB1BFD4543021BA0E80110C98BD7D3D,2,,1,c:\users\administrator\desktop\socar\socar.exe,"",1,1,Administrator,1341991938620) was aborted. Call getNextException to see the cause. 842 milliseconds disconnected at demo1.applicationbehaviorincidents_0_1.ApplicationBehaviorIncidents.main(ApplicationBehaviorIncidents.java:4762) Job ApplicationBehaviorIncidents ended at 15:07 22/07/2013.
bonjour , je suis debutant sous TOS, j'essaye de charger une table posgresqlinput vers une table agreagt dans le meme sgbd, et pour chaque champ agrégé je dois faire une requete dans la table source posgresqlInpu, mais quand j 'excute le job je vois que ya rien qui est t exris au niveau de la source, j'ai vraiment besoin d'aide pour avancer dans mon projet .
voila l'erreur que je reçois a chaque éxecution
Exception in component tPostgresqlInput_4
org.postgresql.util.PSQLException: Mauvaise valeur pour le type timestamp : 100.00
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:258)
at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:333)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:539)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2471)
at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.tPostgresqlInput_4Process(DTM_ZEN_agregat_Solde.java:2251)
at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.runJobInTOS(DTM_ZEN_agregat_Solde.java:8003)
at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.main(DTM_ZEN_agregat_Solde.java:7682)
Caused by: java.lang.NumberFormatException: Expected time to be colon-separated, got '.'
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:167)
... 6 more
bonjour , je suis debutant sous TOS, j'essaye de charger une table posgresqlinput vers une table agreagt dans le meme sgbd, et pour chaque champ agrégé je dois faire une requete dans la table source posgresqlInpu, mais quand j 'excute le job je vois que ya rien qui est t exris au niveau de la source, j'ai vraiment besoin d'aide pour avancer dans mon projet . voila l'erreur que je reçois a chaque éxecution Exception in component tPostgresqlInput_4 org.postgresql.util.PSQLException: Mauvaise valeur pour le type timestamp : 100.00 at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:258) at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:333) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:539) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2471) at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.tPostgresqlInput_4Process(DTM_ZEN_agregat_Solde.java:2251) at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.runJobInTOS(DTM_ZEN_agregat_Solde.java:8003) at datamanagement.dtm_zen_agregat_solde_0_1.DTM_ZEN_agregat_Solde.main(DTM_ZEN_agregat_Solde.java:7682) Caused by: java.lang.NumberFormatException: Expected time to be colon-separated, got '.' at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:167) ... 6 more
This is an international forum and English is the language we use. Posting in English will allow you to get more visibility and more help. Thanks for your understanding!
Hello,
I have th same problem with my project. I used a tPostegresqlInput component with a query and a tmap component before load data in a new table.
All date (source and destination) are string format.
When I run my job , i have the following error :
Exception in component tPostgresqlInput_1 org.postgresql.util.PSQLException: Bad value for the timestamp : 2014 at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:246) at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:321) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:438) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2249) at routines.system.JDBCUtil.getDate(JDBCUtil.java:61) at banque.fait_sourcing_client_0_1.FAIT_SOURCING_CLIENT.tPostgresqlInput_1Process(FAIT_SOURCING_CLIENT.java:1633) at banque.fait_sourcing_client_0_1.FAIT_SOURCING_CLIENT.tPostgresqlConnection_1Process(FAIT_SOURCING_CLIENT.java:577) at banque.fait_sourcing_client_0_1.FAIT_SOURCING_CLIENT.runJobInTOS(FAIT_SOURCING_CLIENT.java:2536) at banque.fait_sourcing_client_0_1.FAIT_SOURCING_CLIENT.main(FAIT_SOURCING_CLIENT.java:2266) Caused by: java.lang.NumberFormatException: Expected time to be colon-separated, got '|'
I don't understand why.
My SQL query :
"select distinct \"ID_APPORTEUR\", \"LBL_NIVEAU1_APPORTEUR\", \"LBL_NIVEAU2_APPORTEUR\", \"LBL_NIVEAU3_APPORTEUR\", bibdev.\"TIERS\".\"ID_TIERS\", \"ID_NUM_TIERS\", \"CAT_TIERS\", \"DATE_CREATION_TIERS\", \"SEMAINE\", \"MOIS\", \"TRIMESTRE\", \"ANNEE\" from bibdev.\"TIERS\" left join (select \"ID_TIERS\",count(*) as nbId from bibdev.\"TIERS\" left join bibdev.\"COMPTE\" on \"ID_TIERS\"=\"ID_TIERS_PRINCIPALE\" and trim(\"ID_NUM_CPT\") in (select trim(substr(\"APOOPEREF\",4)) from bibdev.\"FAIT_APPORTEUR_CLIENT\") left join bibdev.\"FAIT_APPORTEUR_CLIENT\" A on trim(\"APOOPEREF\") = trim(\"ID_NUM_TIERS\") or trim(substr(\"APOOPEREF\",4)) = trim(\"ID_NUM_CPT\") group by \"ID_TIERS\") Z on z.\"ID_TIERS\"=bibdev.\"TIERS\".\"ID_TIERS\" left join bibdev.\"COMPTE\" on bibdev.\"TIERS\".\"ID_TIERS\"=\"ID_TIERS_PRINCIPALE\" and trim(\"ID_NUM_CPT\") in (select trim(substr(\"APOOPEREF\",4)) from bibdev.\"FAIT_APPORTEUR_CLIENT\") left join bibdev.\"FAIT_APPORTEUR_CLIENT\" A on trim(\"APOOPEREF\") = trim(\"ID_NUM_TIERS\") or trim(substr(\"APOOPEREF\",4)) = trim(\"ID_NUM_CPT\") left join bibdev.\"FAIT_APPORTEUR_CLIENT\" B on trim(B.\"APOOPEREF\") = trim(\"ID_NUM_TIERS\") right join bibdev.\"APPORTEUR\" on (case when A.\"APOOPEAPP\" is null then 0 when nbId>1 then B.\"APOOPEAPP\" else A.\"APOOPEAPP\" end )=\"CODE_APPORTEUR\" left join bibdev.\"PERIODE\" on \"DATE_JOUR\"=\"DATE_CREATION_TIERS\" where bibdev.\"TIERS\".\"ID_TIERS\"!='10000' and (\"LBL_NIVEAU1_APPORTEUR\" not like 'D.R%' or \"LBL_NIVEAU1_APPORTEUR\" is null) ORDER BY \"ID_TIERS\""
