On Sun, Mar 22, 2020 at 5:31 PM Drew Jensen
<drewjensen.inbox@gmail.com <mailto:drewjensen.inbox@gmail.com>> wrote:
On Sun, Mar 22, 2020 at 5:25 PM Drew Jensen
<drewjensen.inbox@gmail.com <mailto:drewjensen.inbox@gmail.com>>
wrote:
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
DBA".
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.
Drew
On Sun, Mar 22, 2020 at 5:16 PM Dan Lewis
<elderdanlewis@gmail.com <mailto:elderdanlewis@gmail.com>> 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,
Drew
On Sat, Mar 21, 2020 at 10:48 PM Pulkit Krishna
<pulkitkrishna00@gmail.com
<mailto:pulkitkrishna00@gmail.com>> 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
<drewjensen.inbox@gmail.com
<mailto:drewjensen.inbox@gmail.com>> 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
<drewjensen.inbox@gmail.com
<mailto:drewjensen.inbox@gmail.com>>
wrote:
> 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
<elderdanlewis@gmail.com
<mailto:elderdanlewis@gmail.com>>
> 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:
>>
documentation+unsubscribe@global.libreoffice.org
<mailto:documentation%2Bunsubscribe@global.libreoffice.org>
>> Problems?
>>
https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more:
https://wiki.documentfoundation.org/Netiquette
>> List archive:
https://listarchives.libreoffice.org/global/documentation/
>> Privacy Policy:
https://www.documentfoundation.org/privacy
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:
SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED};
**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:
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
The schema already exists. It will therefore be treated as
an invalid schema name.
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
Where I am having problems is follows the **.
Dan
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.