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 your existing DB to a MySQL DB
- Connecting databases
== 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 command SCRIPT is not recognized in LibreOffice 4.1.x, it tells me there’s only SELECT, INSERT, UPDATE and REMOVE. Do I need to install some kind of extension? Was it silently removed in newer versions? In which old version should it be available?
I have LibreOffice 4.3. and command SCRIPT works just as described. Thanks Cristian.
Using LibreOffice 5.1.4.2 and I get the following result: 1: Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.
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
Thanks to Libre Office, moving a database out into SQL, ready for import into mySQL is a real pain in the arse. Here is a list of changes I needed to make after running SCRIPT
As per the post above, remove the following lines, be careful because they are not all at the top, some are in the middle.
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
Every line needs a ; added at the end, but it also works at the beginning
Replace
CREATE TABLE ;CREATE TABLE
ALTER TABLE ;ALTER TABLE
INSERT INTO ;INSERT INTO
// There may be others in your data, but there are all the commands I have so far. Then I removed the ; from the 1st line and added 1 at the end
Replace With Comments
CREATE CACHED TABLE CREATE TABLE CACHED not recognised
TABLE " TABLE Trying to remove all " without leaving extra spaces
" <nothing> Remove remaining "
TIMESTAMP(0) TIMESTAMP Doesn't like (0)
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) INTEGER AUTO_INCREMENT
Remove all
LONGVARBINARY from IMAGE fields ended up changing to pathname varchars<br/>
ALTER TABLE RESTART AT 0 mySQL didn't accept any of these, had various -- AT # numbers, so I ended taking them out<br/>
At this point I can import all my tables with relationships, BUT I cannot insert any data
As per the above post, these commands will fail because mySQL starts auto_increment fields at 1, not 0 like Libre Office
INSERT INTO "Contatti" VALUES(0,'prova','prova') -- this will be added as ID = 1
INSERT INTO "Contatti" VALUES(1,'pippo','pippo') -- so this will fail here as a duplicate value
INSERT INTO "Contatti" VALUES(2,'pluto','pluto')
There are ways of telling mySQL to start at 0 but everyone describes these instructions with the words **WARNING* because it is not a safe option.
All it needs is for Libre Office to have an option in the Auto Increment field, Start Number at: 1
As it is I have wasted all day trying to get this database moved across and it still has no data, Computers and software are supposed to make life easier not impossible, standards are designed for all to use, not sure who is doing their own thing but both use SQL in a different way. Life it too short for all this bollocks so I'm off to find something else to use for databases instead of Libre Office, something that doesn't hold me to ransom when I want to export it to something else.
Sorry about the poor formatting above, but I've given up try to get things to line up in the editor, move on!!!!!
Hi!
In case anyone was wondering I found a way to migrate from .odb to .db for use in SQlite as I required it.
It’s a bit of a process but It worked for me in the end.
Requires using Base, Calc, and DB Browser (located here: [https://sqlitebrowser.org])
Part one, exporting tables to CSV.
You will need to export each table individually. In Base ‘right click’ the table you want to export, ‘COPY’.
Go into a new Calc spreadsheet and ‘right click’ cell A1, ‘PASTE’.
File > Save As, change Save as Type: to 'Text CSV (.csv), Save, (I left options that come up all Default), Save.
Do this for each table.
Part two, importing table into DB browser:
Open up DB Browser, Click ‘New Database’, Name your database and save, When the next window pops up click ‘Cancel’.
File > Import > Table from CSV file…, Find and insert your first table (you will need to import each one separately).
In the window that pops up make sure to name your table
Check ’ Column names in first line’
field separator ,
quote character "
Encoding (I had weird characters with Umlauts, etc and found that setting it to ISO-8559-1 worked for me)
Trim fields (I don’t know what this does exactly but mine was checked and worked)
‘OK’
Part three, modifying tables and setting keys:
Expand your table list by clicking the down arrow under ‘Tables’. Select your table then click ‘Modify Table’.
Set the ‘Types’ to match what the data should be (e.g. text, integer, etc).
Checking the boxes will set up the each column for:
'Not' = Not Null
'PK' = Primary Key
'AI' = Autoincrement **See note below
'U' = Unique
Now to set the ‘Foreign Key’ you will need to either expand the size of entire window, or shrink down the width of the name,type, default headers. There should be a sneaky ‘Foreign Key’ header hiding out of sight on the right hand side that should appear. Use the dropdown boxes to set the foreign keys.
Note
LibreOffice starts its’ autoincrements at 0. Sql starts at 1. If you’re using autoincrements you will need to change the entry on the table with a '0" to ‘1’ or more. Because I imported populated tables I simply just changed the line with a 0 to the next number after the last one e.g. 28. If your tables need to keep the order of the autoincrements then may God have mercy on your soul.
Exporting database:
Once all that is done and your ready to export your database, File > Export > Database to SQL file…
Make sure all your tables are selected and hit ‘OK’ (I left everything default and it worked for me), name your database and hit ‘Save’.
Importing into SQlite:
(I moved the database file to the same location as the SQlite executable)
Set your directory to where you keep your SQlite (for me it’s C:\sqlite and below will follow suit)
\ CD C:\sqlite
\ sqlite3
\ .read yourfilename.sql
(to check that it loaded type)
\ .tables
(if all is well then we’ll save it as a .db now)
\ .save yourfilename.db
CONGRATS YOU DID IT!
now to open your database in sqlite the command will now be
\ sqlite3 yourfilename.db