[resolved] Insert string from XML to PostgreSQL table

One Star

[resolved] Insert string from XML to PostgreSQL table

Dear all,
I am trying to fetch data from a rss feed and to store it in a PostgreSQL database.
Thanks to Shong's answer on my previous request (http://www.talendforge.org/forum/viewtopic.php?id=17386) I could start moving forward.
I face now a new issue: the xml file is downloaded, "seen" by the tFileInputXML component, mapped by the tMap, but is not inserted into my database. I suspect that the problem comes from the fact that string values are transmitted to the database without quotations marks. Here is what I get after running the job:
 connecting to socket on port 3675
connected
Batch entry 0 INSERT INTO "fetch_gdacs"."gdacs_fetch_eq" ("guid","title","description","image","link","gdas_alertLevel","asgard_alertLevel","runDate","source","magnitude","depth","latitude","longitude","population_100km","iso3") VALUES (EQ_111293,Green earthquake alert Indonesia(M=6) potentially affecting <10000 people.,On 2011-08-22 20:12:20, an earthquake of magnitude 6 and depth 31.3km occurred in Indonesia. This earthquake potentially affected <10000 people.
,http://dmarcgis.jrc.it/getmaprest.asp?mapservice=GDACS/gdacsAlertsActiveBG&bboxSR=&layers=show:4,10,11,12,13&layerdefs=4:IDDup=111293;11:NAME='Indonesia'&imageSR=&format=png&transparent=false&dpi=&bbox=88.9852,-10.4095,118.9852,-2.4095&size=200,100&filename=gdacs/eq/eq111293_1.png&f=cache,http://w3.gdacs.org/reports.asp?ID=111293,Green,Green,Mon, 22 Aug 2011 22:26 UTC,NEIC,6.0,31.3,-6.4095,NULL,7049,IDN) was aborted. Call getNextException to see the cause.
disconnected

If I try this exact SQL statement in PSQL, it will of course not succeed either...
Thus my question: how can I ensure that my text fields (title, for example) are passed as "Green earthquake alert..." rather than just Green earthquake alert... And, of course, I need to be sure that the quotation marks within a text will be escaped...
Thanks a lot in advance!
Stephane

Accepted Solutions
One Star

Re: [resolved] Insert string from XML to PostgreSQL table

Just to let you know that it finally works... I have no idea what I have changed though... Just started again from scratch and it's now fine.
Thanks a lot for your help!
PS: will mark this post as Resolved, even though I don't know how this issue has been solved :/

All Replies
Community Manager

Re: [resolved] Insert string from XML to PostgreSQL table

Hi
I can' t reproduce the problem, I can insert the records into database without quotations marks. For testing, check the box 'die on error' on tPostgresOutput component, and show us the error message.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Insert string from XML to PostgreSQL table

Thanks for you answer.
still doesn't work here... Here is the error message, when Die on Error is checked:
 connecting to socket on port 3778
connected
Exception in component tPostgresqlOutput_2
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "fetch_gdacs"."gdacs_fetch_eq" ("guid","title","description","image","link","gdas_alertLevel","asgard_alertLevel","runDate","source","magnitude","depth","latitude","longitude","population_100km","iso3") VALUES (EQ_111293,Green earthquake alert Indonesia(M=6) potentially affecting <10000 people.,On 2011-08-22 20:12:20, an earthquake of magnitude 6 and depth 31.3km occurred in Indonesia. This earthquake potentially affected <10000 people.
,http://dmarcgis.jrc.it/getmaprest.asp?mapservice=GDACS/gdacsAlertsActiveBG&bboxSR=&layers=show:4,10,11,12,13&layerdefs=4:IDDup=111293;11:NAME='Indonesia'&imageSR=&format=png&transparent=false&dpi=&bbox=88.9852,-10.4095,118.9852,-2.4095&size=200,100&filename=gdacs/eq/eq111293_1.png&f=cache,http://w3.gdacs.org/reports.asp?ID=111293,Green,Green,Mon, 22 Aug 2011 22:26 UTC,NEIC,6.0,31.3,-6.4095,NULL,7049,IDN) was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537)
at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:403)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)
at talenddemosjava.test_xml_0_1.test_xml.tFileInputXML_2Process(test_xml.java:2321)
at talenddemosjava.test_xml_0_1.test_xml.tFileFetch_2Process(test_xml.java:428)
at talenddemosjava.test_xml_0_1.test_xml.runJobInTOS(test_xml.java:2561)
at talenddemosjava.test_xml_0_1.test_xml.main(test_xml.java:2425)
disconnected
Job test_xml ended at 14:50 24/08/2011.

And this is the SQL code to create the table where the new records must be inserted:
CREATE TABLE fetch_gdacs.gdacs_fetch_eq
(
guid character varying,
title character varying,
description character varying,
image_link character varying,
gdas_alertlevel character varying(15),
asgard_alertlevel character varying(15),
rundate character varying(50),
source character varying,
magnitude numeric,
depth numeric,
latitude numeric,
longitude numeric,
pop_100km integer,
iso3166_3 character varying(3)
)

Any idea?
One Star

Re: [resolved] Insert string from XML to PostgreSQL table

Just to let you know that it finally works... I have no idea what I have changed though... Just started again from scratch and it's now fine.
Thanks a lot for your help!
PS: will mark this post as Resolved, even though I don't know how this issue has been solved :/