Hello,
If I have a locally saved ODB Database file, is there a way to migrate/export it to a SQL server (such as MySQL, PostgreSQL, etc.)? That is, the data will be in the SQL server, but I still use LibreOffice to access it.
Thanks!
(Update: better read the OP question)
Yes, you can do that in two steps:
== Exporting the DB ==
In the menu Tools > SQL...
type:
SCRIPT '<your-path>/<your-db-name>.sql'
Mind the quotes. Also replace <your-path> and <your-db-name> with your path and filename respectively.
On Windows this can look like:
SCRIPT 'c:/my documents/mydb.sql'
On Linux:
SCRIPT '/home/myuser/mydb.sql'
Click on Execute.
You will find the SQL dump of the database in the file <your-path>/<your-db-name>.sql
Here's how the result of the command looks like on a test db I have created:
SET DATABASE COLLATION "Italian"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE "Contatti"("IDAccount" INTEGER NOT NULL PRIMARY KEY,"NomeAccount" VARCHAR(50),"Descrizione" VARCHAR(250))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
INSERT INTO "Contatti" VALUES(0,'prova','prova')
INSERT INTO "Contatti" VALUES(1,'pippo','pippo')
INSERT INTO "Contatti" VALUES(2,'pluto','pluto')
Also, please read these words of wisdom:
The output of the SCRIPT command or statement in the Tools -> SQL ... is a set of commands to create a complete database, including a default user, the database itself, create the tables and fill the tables with data. If you want to import this into a different database engine you have to skip several parts:
SET DATABASE COLLATION "Latin1_General"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
:
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
This are specific commands for the (single user) HDBSQL database and will probably cause funny or hazardous things in other databases. Databases uses their own language flavour to create a database and specify the language and encoding. [...] In general don't execute the complete content of the generated sql file at once in phpmyadmin (or whatever you use to manage the other database), but split it into logical segments (first only the lines with CREATE, ... and finally the lines with INSERT) and better try a single insert statement first before you run them all for the same table.
Source: OpenOffice Forum
== Connecting DB ==
When you create a new DB the wizard will show you the possibility to connect your Base file to an existing database (using various protocols). The wizard is quite self-explanatory, so you're done. I also think that if you know in advance that you'll be using your DB with MySQL or other DBMSs this is the best option to take since the creation of the DB.
(for Debian/Ubuntu systems and the like) If you are working particularly with MySQL you may find useful to install the package:
sudo apt-get install libreoffice-mysql-connector
which brings in native support for Base-MySQL connections.
The suggested way to export data from Base is to use Calc as a 'helper' application.
Select Table, and select the table to export, then select Edit > Copy. That copies the contents of the table to the clipboard.
Select File > New and create a Calc spreadsheet. With the cursor in cell A1, do Edit > Paste. That puts the table into Calc.
The next step is to export it from Calc in a format suitable for the 'SQL' database. You will have to consult the Help for the 'SQL' database to find out what it needs. Then you can do File > Save As and save as a file type 'Text CSV'. This gives you a dialogue to set the export options, such a field separators and quoting. You will then need to issue whatever command is needed to import this into the 'SQL' database.
When you have all the tables migrated to the 'SQL' database, you can open a new Base file and use 'Connect to an existing database'. You will have to read up on connection types (ODBC or JDBC) to get the 'SQL' database recognised and connected to Base.
Once you have done that, you can use Base in much the same way as you used it with the built in HSQL database
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-03-31 06:00:05 +0200
Seen: 954 times
Last updated: Jan 03
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
