Exporting data: how is it done

Page 6 of chapter 10 Database Maintenance contains this section. Can anyone explain how to actually do this? I can use SCRIPT 'database name'; and then SET SCRIPTFORMAT TEXT. This reduces the database folder within the *.odb file. But how do I use Tools > SQL after these two steps?

Dan

Howdy,

Well, IMO, dumping the DB with a simple script command is great if you are
migrating the data but for exporting data:

You need to look at the HSQLdb documentation on TEXT TABLES and the SELECT
INTO construct.

Using that you can export all of a table or use the standard query
functions to export a subset of records or to join records from multiple
tables into a single export file.

I hope that helps,

Drew

Also, others may have also, but I wrote a small tutorial on doing this
(exporting using TEXT TABLES) for the old OOo forums and that should be
there still. I'll go this evening and see if I can find that and pass it
onto you if I can find it.

Hello Drew,
What about chapter 9 macros?

Pulkit Krishna

Hello Drew,
You have updated the spreadsheet and added the expected return date of
chapter 9 macros as 22nd March 2020 and today is 23rd March 2020. Kindly
correct it.

Pulkit Krishna

I have been able to export the script file successfully. I have also created a new database and use Tools > SQL to copy the contents of the script file recreating the the database without the queries, forms, and reports. So, now I am thinking that this should be useful to update the HSQLDB version from 1.8 to 2.50.  I copied an embedded database and place the copy in another folder. Then I exported the script file from one of them. I use LibreOffice 6.3 to open the second file. In this version, I have altered the Advance property under Tools > Options to add hsqldb.jar to the Class Path (for HSQLDB 2.50). Now I had a database with all of the queries, forms, and reports, all the things I was missing in the database created with the script file. So, this is when I use it to create the tables, views, and the data. Is this a fairly simple way to take an embedded database using hsqldb 1.8 and convert it to using version 2.50?

Dan

Actually, I can answer my own question is these steps:

1. Export the script file from an embedded database.

2. Open LibreOffice in which the Class Path contains the path to the hsqldb.jar file for version 2.50.

3. Connect to an empty database supplying the pertinent information.

4.Using Tools > SQL, Copy and paste the contents of the script file as described below. This external database now contains all of the tables, views, and data that the embedded database has. There are no queries, forms, nor reports.

5. Create a copy of the embedded database.

6. In the main database window of the external database, open the copy of the embedded database. Since the LibreOffice doing this is using HSQLDB 2.50, this action will remove all of the tables, views, and data from the embedded database. It however contains all of the queries, forms, and reports.

7. Since the external database and the embedded one are both using HSQLDB 2.50, clicking on an individual query, form, or report and drag and drop it from the main database window of one to the other.

The result is an external database with all of the tables, views, data, queries, forms, and reports that are part of the embedded database. There is a problem that has to be corrected in the forms: the form property for each form and subform. The Content property has to be modified to point to the correct the name of the table or query.

Dan