Ask Your Question

Revision history [back]

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