[Solved] Automatically import data from external database

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] Automatically import data from external database

Postby Marlowe » Wed Feb 06, 2013 10:14 am

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
Last edited by Marlowe on Fri Apr 12, 2013 5:01 pm, edited 1 time in total.
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: automatically import data from external db

Postby Villeroy » Wed Feb 06, 2013 10:26 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: automatically import data from external db

Postby Marlowe » Wed Feb 06, 2013 10:45 am

my very particular and specific situation require exactly what i asked: automatically query informix then fill up hsqldb with those data. thank you anyway!
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: automatically import data from external db

Postby Villeroy » Wed Feb 06, 2013 10:58 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Sliderule » Wed Feb 06, 2013 5:16 pm

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. :super:

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1245
Joined: Thu Nov 29, 2007 9:46 am

Re: Automatically import data from external db

Postby Villeroy » Wed Feb 06, 2013 6:21 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 11:49 am

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
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Villeroy » Fri Feb 08, 2013 12:45 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 1:16 pm

ok i'll work on that, thank you very much!!!
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Sliderule » Fri Feb 08, 2013 4:05 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1245
Joined: Thu Nov 29, 2007 9:46 am

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 6:26 pm

to villeroy:

i followed this tutorial: viewtopic.php?f=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...
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Villeroy » Fri Feb 08, 2013 6:28 pm

Sliderule wrote: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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Villeroy » Fri Feb 08, 2013 6:28 pm

Marlowe wrote:to villeroy:

i followed this tutorial: viewtopic.php?f=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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 6:33 pm

so what's wrong?
this command give me no error
SET TABLE "Names" SOURCE "names.csv"
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 6:35 pm

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?
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Villeroy » Fri Feb 08, 2013 6:44 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28827
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically import data from external db

Postby Sliderule » Fri Feb 08, 2013 6:57 pm

Villeroy:

You wrote / asked:

Villeroy wrote:"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   Expand viewCollapse view
"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
User avatar
Sliderule
Volunteer
 
Posts: 1245
Joined: Thu Nov 29, 2007 9:46 am

Re: Automatically import data from external db

Postby Marlowe » Fri Feb 08, 2013 7:36 pm

i'll try that!
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Sliderule » Fri Feb 08, 2013 7:43 pm

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. :super:

I hope that makes sense.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1245
Joined: Thu Nov 29, 2007 9:46 am

Re: Automatically import data from external db

Postby Marlowe » Wed Feb 13, 2013 3:21 pm

i sent you a PM sliderule: hope you can help me soon!
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm

Re: Automatically import data from external db

Postby Marlowe » Fri Apr 12, 2013 5:01 pm

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!
Marlowe
 
Posts: 22
Joined: Thu May 12, 2011 1:57 pm


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 2 guests