Export data from a SQL file to a postgresql database

[Anonymous]

Hello,
I have a problem ..
I have got a SQL file wich contains some queries in order to insert data in a database (this file is obtained by a dump from phpMyAdmin). Those data come from MySQL database and I have to integrate them to my Postgresql database..
I dont know which component I can use to do that : I have the "tposgresqlrow" but I don't know how to use it.
I'm new in Talend, so if someone can help me ...
Sorry for my English witch it would be very bad..
Thanks

[Anonymous]

I guess you mean with the word query also insert statements.
You can use the component tSQLScriptParser + tPostgresqlRow to do this job.
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=1,1,1
tSQLScriptParser -- iterate --> tPostgresqlRow: use as SQL code only this: ((String) globalMap.get("tSQLScriptParser_1_STATEMENT_SQL"))
This component can read the script from a file or from the input field.
Please keep in mind, the component parse the whole script at once and keeps all statements in the memory.
If you have a dump, this could contains millions of insert statements. To avoid memory space problems I recommend splitting a huge dump file into smaller files with about 1000 statements per file. You should create a job processing one file and another job iterating through all files and calling the one-file-job.

[Anonymous]

Thanks for the quick response!
But I don't have the component tSQLScriptParser in my Palette...
I'm working with Talend Open Studio for Data Integration version 5.3.1

[Anonymous]

Sorry I didn't have understand that the component tSQLScriptParser was a custom component..
I install this component like it's described here: https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
When I intent to use this component, I have an error : "Module cimt.talendcomp.dbtools-1.0.jar required"
So I follow the instructions in this page to install a custom component : Installing a custom component :

If you get a missing jars error, when using the component in a Job, try the following procedure:
Install the component again (without uninstalling the component installed previously).
Delete the file <Talend Studio installation dir>\configuration\ComponentCache.javacache and restart Talend Studio.

But I still have the same error message...
Someone has encountered the same problem for installing a custom component ??
Thanks

[Anonymous]

Finally I succeed to install the JARs file correctly using the Modules "View" and importing the jar file manually..
I don't have the error message "Module cimt.talendcomp.dbtools-1.0.jar required" and when I run the job I have got the exit code 0.
Nevertheless, the job doesn't write in my database and I don't have error messages???
I don't understand why...

[Anonymous]

Hello,
I don't have succeeded to use the tSQLScriptParser component..
When I run my Job in Talend, Idon't have any error messages but any data is inserted in my database..
You can see my Job and the result after running my job in the attachments..
Thanks for the help..

[Anonymous]

Did you set in the Query of the tPostgresqlRow: ((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL"))
This return value contains the current statement.
Please check if the tSQLScriptParser has the number 1, otherwise you have to change the number in the term above.
((String)globalMap.get("tSQLScriptParser_2_STATEMENT_SQL"))
The best way is to drag & drop this return value from the Outline view (open the node of the parser component)

[_AnonymousUser]

Hello
Do you know if it's possible to use this compenent and set some DBMS_MVIEW.REFRESH requests ?
I'd like to use script to refresh automatically some materialized views.
I tried also to use DROP VIEW and CREATE MATERIALIZED VIEW commands (with EXECUTE IMMEDIATE prefix) but it fails too...
Any idea on how to do this ?
thanks