[Solved] Backing up to .csv

Creating tables and queries
Post Reply
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

[Solved] Backing up to .csv

Post by MSPhobe »

PS- Delighted to say that solution written up by Sliderule (see near bottom) involving HSQL "Script" meets my wants.

(Note: The command, "Script", CREATES a plain text script with which the database could be rebuilt. The command is NOT something for "running scripts" (or macros), which was the idea that came into my head when I first saw it under that name.)

=== Original post follows...
I've used OO for years... very happily. With VERY few crashes/ lost data.

But for some applications, the possibility of even one crash is a nightmare. Years ago, I went to MySQL for some work, primarily because I could back up data and schema in a way that I trusted... .csv files.

If I have some data in OO Base, using the native (HSQL) database engine, I have the skills I want to export the data in a table to a .csv file which could, after a crash, be "fed" into a newly built, empty table. It would be a pain, but I'd have my data in files I trust.

This post is to ask: What about what I think is called the "schema" of my database? Is there a way to get "out of the machine" a comprehensive list, in a text form, of what the fields are in each table, their data-types, lenghts, etc? What fields are primary keys? What relationships have been set up between fields in different tables. (If the format of reports used by the database could also be extracted, so much the better.)

Perhaps this is a case of a good question but a user looking at the wrong answer. General comments on "how to back up" welcome, too.
Last edited by MSPhobe on Fri Jan 26, 2018 4:04 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Backing up to .csv

Post by RusselB »

While I can't say that this isn't a good idea, one problem that you don't seem to be taking into account is the vulnerability of your storage medium.
I, personally, use my Dropbox account for my main storage, as the files are maintained not only on my computer, but also on the Dropbox website and any other devices that I have linked to my Dropbox account.
When accessing a file that is already in my Dropbox, a snapshot of the file before it is accessed is made and stored on the Dropbox site.
I can then save the file after editing it (if needed). The next time I access the file, another snapshot is made, so now there are two snapshots and the main file on the Dropbox site.
Only the main file is synced on the devices that are linked to my Dropbox account. The snapshots are stored entirely on the Dropbox site and can only be accessed via the Dropbox site.
I have some files that have over 25 snapshots, and each snapshot is date and time stamped.

As to your data and the fields that are stored, you can easily look at that by opening the .odb file using a compression program (I like WinRar) and then extracting the content.xml file, which you can then open with any XML editor/viewer.

I realize I didn't answer your questions in the order you presented them, but wanted to get the "back-up" information to you first.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Backing up to .csv

Post by Sliderule »

MSPhobe:

Perhaps, you could try a few of these options, to see it they meet your needs:
  1. According to HSQL 1.8.0 Documentation:
    HSQL 1.8.0 Documentation [url]http://www.hsqldb.org/doc/1.8/guide/ch09.html#script-section[/url] wrote:
    SCRIPT

    SCRIPT ['file'];

    Creates an SQL script describing the database. If the file is not specified, a result set containing only the DDL script is returned. If the file is specified then this file is saved with the path relative to the machine where the database engine is located.
    So, if you want write your database definition ( DDL statements ) to a file, from the OpenOffice / LibreOffice Base Menu:

    Tools -> SQL...

    Enter in the box, for example ( change what is between single quotes to the file name you desire )

    Code: Select all

    SCRIPT 'C:\Users\MY_NAME\Documents\MY_ODB_File_Name_20180115.txt'
  2. Download this recent file I posted for another user, and, explore some of the Reports, and, you might also find interesting, some of the Queries:

    http://www.mediafire.com/file/xi9x8boib ... DAREA4.odb
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Post by MSPhobe »

Sliderule... thank you! This is the sort of thing I was hoping existed... I shall study your "answer" soon"

---
Backing store: Absolutely right, RussellB, of course. That side of things I feel I have under control. But once I have good procedures in place, as you say, to protect myself against, say, a failed hard drive, I still need a good system to "capture" the things to be moved to multiple, independent storage devices.

It was "how to have the files I need" that was my greatest Black Hole of Ignorance!
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Backing up to .csv

Post by UnklDonald418 »

What about what I think is called the "schema" of my database? Is there a way to get "out of the machine" a comprehensive list, in a text form, of what the fields are in each table, their data-types, lenghts, etc? What fields are primary keys?
Since moving from the Embedded database model to the split model (JDBC) I no longer rely on the Table design GUI and instead create my tables and relationships using SQL. I often add a Form to my database with all SQL commands needed to recreate the tables and relationships. Once you get used to doing it that way it isn't really much more difficult than using the GUI.
When I encounter a database that I don't have the SQL source I turn to SQLworkbench/J which can be downloaded for free
http://www.sql-workbench.net/
You should be able to use the same JDBC command that you use to connect Base to your database. Once the connection has been established go to View->Database Explorer and select the PUBLIC schema to display a list of all the tables in the database. Then select a table from the list and click on the SQL source tab to see the SQL command(s) that could be used to recreate that table.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Post by MSPhobe »

Thank you UnklDonald418! Just when I was about ready to give up, having spend DAYS on this (again!)... encouragement in the form of an indication that someone actually USES Base+MySQL, which is what I've done in the past for some things, and want to do again one day. (And thank you again, Sliderule, for something that may make me a more comfortable user of "just" Base in the meantime.

Sadly, "the day job" is screaming for attention, having been dangerously neglected while I've been on the binge I'm trying to wrap up... but you, and others in another thread, have given me hope that maybe revisiting this when I can will bear fruit. (A central point from the other thread: ODBC defeated me (for now); I DID get connection via JDBC "working", sort of, but not "exercised"/ "tested". I did NOT use the old, seemingly defunct, ooBase "MySQL connector. (All this on dog's dinner Win10. Oh, how strong the siren call of Linux!))
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Backing up to .csv

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Post by MSPhobe »

Thank you Villeroy. Good to know alternatives. However my issue wasn't how to back up in MySQL... I had good procedures for that. Rather, if I had a process under 00Base using embedded database engine, HSQL, that I trusted for backing up my database schema, that would be wonderful, as it would spare me all the DOWNSIDE of trying to use ooBase as a front end for MySQL.

I'd hoped using ooBase+MySQL was still as (relatively) easy as it was a few years ago. (Not so, it seems, after days of work)

Currently, I am hoping, when I have time, that Sliderule's answer may fulfill my wants.

With regret. I liked MySQL for other reasons, too.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Backing up to .csv

Post by Villeroy »

HSQL, MySQL and OpenOffice are 3 different and completely unrelated products. HSQL can backup its own databases and MySQL can backup its own databases. OpenOffice does not care about maintainance tasks for any of these databases. It treats HSQL very much in the same ways as it treats MySQL. It connects to some database resource (server or local files) and lets an office user work with database data.
There is no reason to backup MySQL with any other tool than the one that is shipped with MySQL.
HSQL v2 comes with a "sqltool.jar" which includes a command line interface and a backup tool for HSQL. It backs up my databases every night since many years.
A Base documents with embedded HSQL v1.8 needs to be backed up by making a copy of the odb document while the database is shut down. After a clean shutdown the actual HSQLDB is embedded in the Base document.
OpenOffice is a tiny little tool set. The whole package including all drivers weighs no more than 25 MB. It is not an adequate tool for database development nor maintainance. As a database developer and maintainer you must not expect that OpenOffice can help you anyway at all. You can connect and dump data from the connected database to office documents. You can develop rather primitive input forms to edit single records within the connected database. This is all you can do with OpenOffice Base.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Post by MSPhobe »

"Backing up" HSQL by taking a second copy of the who-knows-if-corrupt .odb file is not, for me, adequate as backup for some work.

I prefer what I can easily get under MySQL... text files of...
a) The schema
b) The data, in .csv

Worst case? I can rebuild from those files.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Backing up to .csv

Post by Villeroy »

With external HSQL this is not a problem. The office suite may crash without affecting the database. It just works like MySQL but simpler.

Millions of MySQL databases are backed up every day. But this has nothing to do with OpenOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply