Backing up to .csv

Creating tables and queries

Backing up to .csv

Postby MSPhobe » Sat Jan 13, 2018 12:10 am

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.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
MSPhobe
 
Posts: 90
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Postby RusselB » Sat Jan 13, 2018 3:06 am

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4192
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Backing up to .csv

Postby Sliderule » Sat Jan 13, 2018 5:22 am

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 http://www.hsqldb.org/doc/1.8/guide/ch09.html#script-section 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   Expand viewCollapse view
    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/xi9x8boibr1eh0p/HSQL-WORLDAREA4.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
Sliderule
Volunteer
 
Posts: 1108
Joined: Thu Nov 29, 2007 9:46 am

Re: Backing up to .csv

Postby MSPhobe » Sat Jan 13, 2018 5:22 am

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
User avatar
MSPhobe
 
Posts: 90
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Backing up to .csv

Postby UnklDonald418 » Sat Jan 13, 2018 6:10 pm

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 subject line
Apache OpenOffice 4.1.4 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 606
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Backing up to .csv

Postby MSPhobe » Tue Jan 16, 2018 2:12 pm

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
MSPhobe
 
Posts: 90
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests