Page 1 of 1

Can anyone help me convert my ODB file to SQL?

PostPosted: Mon Jan 19, 2009 12:44 am
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

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

PostPosted: Mon Jan 19, 2009 1:50 am
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: viewtopic.php?f=13&t=12117

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

PostPosted: Mon Jan 19, 2009 3:56 am
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

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

PostPosted: Mon Jan 19, 2009 4:42 am
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.

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

PostPosted: Mon Jan 19, 2009 5:33 am
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

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

PostPosted: Tue Jan 31, 2012 10:35 pm
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?

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

PostPosted: Wed Feb 01, 2012 2:00 am
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

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

PostPosted: Wed Feb 01, 2012 2:22 am
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.

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

PostPosted: Thu Feb 02, 2012 2:53 am
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:

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

PostPosted: Thu Feb 02, 2012 5:57 am
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:

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

PostPosted: Thu Feb 02, 2012 8:58 pm
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   Expand viewCollapse view
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.