[Solved] Automatically import data from external database

[Marlowe]

hi,
i'm working on windows (xp)
i would love to automatically fill up a specific table of my hsqldb database (i use base as the front end)
with data coming from an informix database (which i succesfully connected with base already, so i can run sql commands).
this and other similar to this are tasks i'd love my computer could do by itself every night so i can work on that in the morning.
i heard about scheduled tasks with windows batch files but i don't know if it's the right way and i don't know how to start:
can anybody give me a precious advice?
thank you in advance

[Villeroy]

There should be no need to copy data from one database into another database. The reason why one uses a database is to avoid duplicate instances of the same data. Being able to query your Informix backend you should be able to retrieve any kind of data you need.

[Marlowe]

my very particular and specific situation require exactly what i asked: automatically query informix then fill up hsqldb with those data. thank you anyway!

[Villeroy]

Let Informix export csv text. Run a cron script to concatenate the text with the previous text. Link a HSQL text table to the main text table.

[Sliderule]

Marlowe:

Nice to see you again.

Yes, there are ways to do this automatically, outside of OpenOffice / LibreOffice with your database back-end setups. I asked you, when you and I spoke via Skype, if what you describe is needed, and, you told me NO.

In short, since you have available, CONNECTIONS to two external databases, ( NOT the Embedded database engine ), Informix and HSQL database, it can easily be down.

Please, let's work together at your convenience again ( let me know by PM - Private Message when we can do this again ), and, I will describe / help you accomplish the task. You can afterwards, describe to folks here how it works.

Sliderule

[Villeroy]

He is not using embedded HSQL? Then you don't even need any office suite. Just write a script to export csv from Informix and to insert from the linked csv in HSQL. Add the script to your scheduling tool.

[Marlowe]

hi guys
at the moment i can export a text file from informix with tha data i need.
actually, even if my connection with informix is fine, i couldn't do it from my computer, but i had to work on our server and make it write the text file in a shared folder.
now
step 1: i need to know the precide syntax hsqldb want to import data from a text file
step 2: schedule this export operation on the server
step 3: learn how to import data from a text file in hsqldb
step 4: schedule this import operation on my computer
i think i can make it with your precious help and my effort

[Villeroy]

You have the full HSQLDB documentation in PDF and HTML on your machine and there has been written quite a lot in this forum's tutorial section.
The concept is to create an ordinary database table including the TEXT keyword:

Code: Select all

CREATE TEXT TABLE "MyTextTable"(<ordinary field declarations>)

and then tell the database about the text file to fill the text table with content:

Code: Select all

SET TABLE "MyTextTable SOURCE "somefile.csv;option1=this;option2=that;option3=23"

Dates need to be ISO dates, decimals need to be point decimals otherwise you need to declare the respective columns as text and handle the type conversion later.

[Marlowe]

ok i'll work on that, thank you very much!!!

[Sliderule]

Marlowe:

Once again, I want to suggest, you can do all of the above, withOUT a text file IN the HSQL database engine, and in ONE scheduled batch file. It really is 'simple' once we define the exact 'connections' between the two database engines ( Informix and HSQL ).

So, I am offering to help you do this if you contact me here, via PM ( Private Message ).

Sliderule

[Marlowe]

to villeroy:

i followed this tutorial: http://forum.openoffice.org/en/forum/vi ... 83&t=23260
i was doing good but hear what happen:
if i edit the csv file, example: i add a new row, i can see the new row only after i restart base, not just refreshing...
actually right now i cannot see the new edited file even if i restart base...

[Villeroy]

Marlowe:

Once again, I want to suggest, you can do all of the above, withOUT a text file IN the HSQL database engine, and in ONE scheduled batch file. It really is 'simple' once we define the exact 'connections' between the two database engines ( Informix and HSQL ).

So, I am offering to help you do this if you contact me here, via PM ( Private Message ).

Sliderule

Any public hint how to do the job without csv exchange?

[Villeroy]

to villeroy:

i followed this tutorial: http://forum.openoffice.org/en/forum/vi ... 83&t=23260
i was doing good but hear what happen:
if i edit the csv file, example: i add a new row, i can see the new row only after i restart base, not just refreshing...
actually right now i cannot see the new edited file even if i restart base...

It is important that the data are in the database. Forget Base.

[Marlowe]

so what's wrong?
this command give me no error
SET TABLE "Names" SOURCE "names.csv"

[Marlowe]

i can see in SYSTEM_TABLES that the text table have the field read_only=true: does it have something to do with my issue?

[Villeroy]

Does it require a primary key? I don't know. Let's wait for Sliderule's suggestion how to do the trick without text file.

[Sliderule]

Villeroy:

You wrote / asked:

"Any public hint how to do the job without csv exchange?

The short answer is, use SqlTool.jar from HSQL .

An 'old' version, as of today, is available in the hsqldb.jar file ( version 1.1.8.10 ) provided in both OpenOffice and LibreOffice. However, the newest version, available as a separate file in a download of HSQL database ( in the lib directory ) is available.

The advantage . . . it can connect to any database, JAVA or ODBC and eXport data to a text file ( from a SELECT statement ) with ANY column delimiter ( for example, a tab, or, ; , or | or ~ ) and write the file to wherever user wants. And, it can next, connect to another database ( for example HSQL ) and iMport the data to any specified TABLE or TABLES.

SqlTool.jar also provides the ability to create an HTML report, showing, number of records in the iMport skipped, rejected, imported. And, with any record rejected . . . it will give an explanation why . . . for example . . . data not in correct format ( DATE ), or, duplicate Primary Key etc. AND, for any row rejected, it can, if so desired, write a separate file, so, they can be corrected to make the iMport successful.

Documentation for SqlTool.jar . . . is available at:

http://www.hsqldb.org/doc/2.0/util-guide/index.html

Below, is an EXAMPLE of starting SqlTool.jar , using the older version within the hsqldb.jar file from OpenOffice / LibreOffice file. This is on MY machine ( a Windows version . . . would have to be changed / modified to the location on users machine for the hsqldb.jar file ) . . . and . . . change the location of the database to be connected to ( --inlineRC url= just like the connection that can be found for your given database . . . Menu: Edit -> Database -> Properties... ).

Code: Select all

"C:\Program Files\Java\jre6\bin\java.exe" -jar "C:\Program Files\OpenOffice.org 3\Basis\program\classes\hsqldb.jar" --inlineRC url="jdbc:hsqldb:file:C:\Program Files\HSQL-18\data\mydata;default_schema=true;shutdown=true",User=SA,Password=

Bottom line, this does NOT require any NEW TEXT file in HSQL, so, makes it more efficient ( smaller database ) . . . and, SqlTool.jar is extremely FAST / EFFICIENT.

Sliderule

[Marlowe]

i'll try that!

[Sliderule]

OK that you will try it . . . BUT . . . I strongly suggest . . . in your case Marlowe, that you use the NEWER version of SqlTool.jar that I can email you, OR, you can get from HSQL site.

AND, it is important, that the BAT file example I showed above, provides an INTERACTIVE use of SqlTool.jar. That is, it is waiting for commands to be entered. This can be important as you 'test and learn it' . . . BUT . . . once you know the commands to issue, they can be written / saved to a file ( for example: export_informix_import_hsql.sql ) . . . AND . . . the BAT file can be told to execute the commands in that file. Put another way, it can be done, completely as a BATCH file, compared to NOT interactively, once the commands to execute are known for sure.

I hope that makes sense.

Sliderule

[Marlowe]

i sent you a PM sliderule: hope you can help me soon!

[Marlowe]

I did work on that with Sliderule.
I'm successfully using sqltool right now: it automatically runs every night importing data from informix and html reporting me the information i need to work on in the morning.
Actually we couldn't import data straight from informix, so i'm using a csv files exported created by informix.
Thanks to Sliderule, he's the man!