Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index

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.


On 3/25/20 10:53, Dan Lewis wrote:

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?


On 3/22/20 17:48, Drew Jensen wrote:

On Sun, Mar 22, 2020 at 5:31 PM Drew Jensen < <>> wrote:

    On Sun, Mar 22, 2020 at 5:25 PM Drew Jensen
    < <>>

        Ah, sorry I didn't really address your question.

        To recreate the HSQLdb database in a new empty Base file
        w/hsql embedded engine:

        Open the exported script file in a text editor. Copy
        everything after the line "CREATE SCHEMA PUBLIC AUTHORIZATION
        Everything if it all fits in 65K of text, if the file is
        bigger then that you need to copy pieces of it separately.

    When I have needed to do this with files bigger then 65K of text
    I would copy only the parts of the file which recreate the
    Tables, Relations and such. All of these DDL commands are at the
    head of the file.
    Then the data follows as a set of  INSERT commands. If you need
    to move them in separate copy paste steps you just need to be
    sure not to break any of the individual INSERT commands, which
    may be multiple lines each.

One more: Kind of a power user tip!
If there is a large amount of data, and many indexes, it will go much faster if when you copy the paste the DDL commands you do not include any Create Index commands. Then after all the data has been moved go back and apply the Create Index commands, this way only a single indexing update happens vs updating the indexes after each insert command. --I'm not really sure how closely that would fit with the average Base users needs, so it would be worth a review by, or question to, the other folks on the docs ML IMO

        Now in the new Base file open the SQL window, paste the
        contents of the clipboard into it and execute the script.
        This will rebuild the database and repopulate with the data.


        On Sun, Mar 22, 2020 at 5:16 PM Dan Lewis
        < <>> wrote:

            On 3/22/20 15:32, Drew Jensen wrote:
            Thanks for asking Pulkit.

            I did search the AOO form didn't see my CSV export
            tutorial (thinking it must of been on the old oooForum
            and that is now kaput) - but there are plenty of
            examples there of using SELECT INTO TEXT function with
            the HSQL embedded dbms.

            But then I got thinking that is not the only way to
            answer this question. The main way people export data
            from base is with Calc (drag a Table icon or Query icon
            to a calc sheet for instance), and that there is
            already good documentation on in the Calc Guide for one
            thing (it is import there, of course). If you wanted a
            csv file from a table just drag it to a blank Calc file
            save it as the .csv file.

            Dave, does that make sense as a way to go for you?

            Best wishes,


            On Sat, Mar 21, 2020 at 10:48 PM Pulkit Krishna
            <>> wrote:

                Hello Drew and Dan,
                Are you done with your respective reviews?

                Pulkit Krishna

                On Thu, Mar 19, 2020 at 10:13 PM Drew Jensen
                <>> wrote:

                    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.

                    On Thu, Mar 19, 2020 at 12:37 PM Drew Jensen

                    > 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
                    > On Thu, Mar 19, 2020 at 12:26 PM Dan Lewis
                    > wrote:
                    >> 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
                    >> --
                    >> To unsubscribe e-mail to:
                    >> Problems?
                    >> Posting guidelines + more:
                    >> List archive:
                    >> Privacy Policy:

            Here is what is written in the Base Guide:

              Exporting data

            Along with the possibility of exporting data by opening
            the *.odb file, there is a much simplermethod. Directly
            at the Base interface, you can use *Tools > SQL*to enter
            a simple command that, in server databases, is reserved
            for the system administrator.

            SCRIPT 'Database'

            This creates a complete SQL extraction of the database
            with all table definitions, relationships between tables,
            and records. Queries are not accessed since they were
            created in the user interface and are not stored in the
            internal database. However all views are included.

            By default, the exported file is a normal text file. It
            can also be provided in binary or zipped form, especially
            for large databases. However this makes re-importing it
            into LO somewhat more complicated.

            The format of the exported file can be changed using:


            **The file can be read in using *Tools > SQL*, creating a
            new database with the same data. In the case of an
            internal database, the following lines must be removed
            before import:


            The schema already exists. It will therefore be treated
            as an invalid schema name.


            GRANT DBA TO SA

            Where I am having problems is follows the **.


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.