One Star

Error : getNextException()

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.
6 REPLIES
Moderator

Re: Error : getNextException()

Hi,
Could you paste your sql query into forum? Is your work flow tMssqlinput-->tpostgreSqloutput? Trouble screenshot will be preferred.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error : getNextException()

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(, 8)
+ '-' +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.
One Star

Re: Error : getNextException()

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
One Star

Re: Error : getNextException()

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

J'ai exactement le même problème. Help please
Moderator

Re: Error : getNextException()

Hi,

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!

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error : getNextException()

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\""


Best regards