Can anyone help me convert my ODB file to SQL?

Discuss the database features
Post Reply
russadspec
Posts: 3
Joined: Mon Jan 19, 2009 12:37 am

Can anyone help me convert my ODB file to SQL?

Post by russadspec »

Howdy, y'all!

Can anyone help tutor me on how to convert my ODB database file into SQL format so I can use it?

I'm a newbie web developer creating an online store for someone. The setup requires a database for inventory. That database must then be loaded onto the hosting site's MySQL server to be used. I was told to create a database, then upload it, but know one told me the database had to be in SQL format first.

---Lost
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can anyone help me convert my ODB file to SQL?

Post by Villeroy »

menu:Tools>SQL...
SCRIPT '/tmp/dbscript.sql'
Dumps the SQL to build the database schema.

Another method extracts the whole HSQLDB including data, so it can be served by a stand-alone hsql server: http://user.services.openoffice.org/en/ ... 13&t=12117
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
russadspec
Posts: 3
Joined: Mon Jan 19, 2009 12:37 am

Re: Can anyone help me convert my ODB file to SQL?

Post by russadspec »

Ok - Thanxs! ... but, I entered '/tmp/dbscript.sql' (with the quotes) in the "Command to execute" area in the SQL dialogue box, but it just comes up with the response, "Unexpected token: '/tmp/dbscript.sql' ..." in the "Status" area below it.

Then I tried it without the quotes and nearly the same response comes back, "Unexpected token: / in statement [/]"

So, what's supposed to happen and what do I do next? I'm a bit confused since I'm such a rookie ...!

Are there any freeware programs floating out there that'll do the job?

--------RW
OOo 2.3.X on Ms Windows XP
Pat01
Posts: 3
Joined: Sat Dec 20, 2008 9:16 pm

Re: Can anyone help me convert my ODB file to SQL?

Post by Pat01 »

You have to enter the word SCRIPT too. I believe the file path also has to exist. I entered

SCRIPT 'c:/my documents/dbscript.sql'

and it seemed to work.
russadspec
Posts: 3
Joined: Mon Jan 19, 2009 12:37 am

Re: Can anyone help me convert my ODB file to SQL?

Post by russadspec »

Thanx! That last one worked at it did make the SQL file.

However, the server I'm uploading it to is the hosting company's MySQL server. To upload databases, it defaults to phpMyAdmin. When I import the file, this error message comes up ...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE COLLATION "Latin1_General"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CRE' at line 1

Here is that actual portion of the script ...

SET DATABASE COLLATION "Latin1_General" CREATE SCHEMA PUBLIC AUTHORIZATION DBA CREATE CACHED TABLE "Table1"("ID"NUMERIC( 10 ) NOT NULL PRIMARY KEY , "availdates1"VARCHAR( 50 ) NOT NULL
) CREATE USER SA PASSWORD "" GRANT DBA TO SA SET WRITE_DELAY60 SET SCHEMA PUBLIC INSERT INTO "Table1"
VALUES ( 1, 'Wed., April 1' )
INSERT INTO "Table1"



... Any ideas?


----------------------RW
OOo 2.3.X on Ms Windows XP
adamzad
Posts: 3
Joined: Tue Jan 31, 2012 10:30 pm

Re: Can anyone help me convert my ODB file to SQL?

Post by adamzad »

So, where does this dbscript.sql file come from? It is on neither my Slackware Linux OpenOffice 3.2.1. box nor my Windows 7 LibreOffice 3.4 machine. Failing that, what commands are in said script?
OpenOffice 3.2.1 OOO320m18 (Build: 9502) on Slackware Linux 13.37 / LibreOffice 3.4.3 OOO340m1 (Build:302) on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Can anyone help me convert my ODB file to SQL?

Post by DACM »

See the tools under the following headings in this link:

Advanced database migration tools
HSQLDB Script command

or perhaps
Utilities that Export an HSQLDB table to CSV
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
adamzad
Posts: 3
Joined: Tue Jan 31, 2012 10:30 pm

Re: Can anyone help me convert my ODB file to SQL?

Post by adamzad »

Got it! Had to make a new folder and set the permissions on said folder to allow file creation. For some reason, Office wasn't doing the paths right when I tried to go to "My Documents" but it worked for a path with no spaces in it... after I set the permissions to allow new files.
OpenOffice 3.2.1 OOO320m18 (Build: 9502) on Slackware Linux 13.37 / LibreOffice 3.4.3 OOO340m1 (Build:302) on Windows 7
pauliolio
Posts: 1
Joined: Thu Feb 02, 2012 2:50 am

Re: Can anyone help me convert my ODB file to SQL?

Post by pauliolio »

OK, so like me you've now got the sql out of OpenOffice & are trying to get Access to create tables from it...

Any joy yet?


:crazy:
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
adamzad
Posts: 3
Joined: Tue Jan 31, 2012 10:30 pm

Re: Can anyone help me convert my ODB file to SQL?

Post by adamzad »

Actually, I'm porting to MySQL, because I'm making a completely platform-independent database... why I chose Open/Libre Office in the first place: so it could be run on Mac, Unix/Linux, and Windows. Turned out I have a friend who does co-location and other online services who can get me "cloud" hosting with a PHP front end, so you can access the data from any web browser, and I don't have to install anything on the client system, just create a server login and have my clients point their browsers to a URL. Then, I have complete control over the base, itself. :bravo:

I haven't had the time to try the import, yet. I'll let ya know when I figure that one out. :super:
OpenOffice 3.2.1 OOO320m18 (Build: 9502) on Slackware Linux 13.37 / LibreOffice 3.4.3 OOO340m1 (Build:302) on Windows 7
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Can anyone help me convert my ODB file to SQL?

Post by rudolfo »

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:

Code: Select all

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. But in general in most cases CREATE DATABASE your_db_name should work and uses meaningful defaults. Also note that you don't want CACHED tables in other database backends. So better change it to CREATE TABLE. INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) has to be translated into INTEGER AUTOINCREMENT for most other databases.

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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Post Reply