topic Re: [resolved] Insert string from XML to PostgreSQL table in Talend Studio

[resolved] Insert string from XML to PostgreSQL table

Anonymous — Sat, 16 Nov 2024 12:43:24 GMT

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 ( https://community.talend.com/t5/Design-and-Development/resolved-Rss-feed-into-PostgreSQL-database/td-p/100686) 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

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

Anonymous — Wed, 24 Aug 2011 09:56:30 GMT

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

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

Anonymous — Wed, 24 Aug 2011 10:54:46 GMT

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?

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

Anonymous — Wed, 24 Aug 2011 12:38:16 GMT

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 😕