# Migrate an ODB database to SQL server?

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!

edit retag close merge delete

Sort by » oldest newest most voted

(Update: better read the OP question)

Yes, you can do that in two steps:

1. Exporting your existing DB to a MySQL DB
2. 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))
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')


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
:
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 ...

more

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?

( 2014-10-17 16:46:06 +0200 )edit

I have LibreOffice 4.3. and command SCRIPT works just as described. Thanks Cristian.

( 2014-11-06 19:30:40 +0200 )edit

Using LibreOffice 5.1.4.2 and I get the following result: 1: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

( 2016-10-06 04:14:37 +0200 )edit

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

more

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 ...

more

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
:
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

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)


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!!!!!

more