Apache OpenOffice Community Forum

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

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

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

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.

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

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

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?

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