[Solved] Export odb database to .sql

[mxdog]

Ok this is 2020 ....I have open office 4.1.5 and am trying to dump a database to a sql file for import into mysql or postgres
I can live without exporting the data I can cut and paste that into calc and export as cvs but I really need to export the create statements for the tables, about 45 of them and they have a ton of fields.

All I find online are commands like this menu/tools/sql ....SCRIPT 'c:\temp\mydatabase.sql' which does nothing but give me an invalid SQL statement error
as it should because i have no clue what that "SCRIPT" command is or where it comes from.
I am looking for a way to dump at the very least the structure of this database.

am i missing an extension or something ? is that a historical thing that is no longer available ?
can someone get me on the right path ?

any help is appreciated
thx

[Villeroy]

Base is not a database. It is a tool to work with databases. If you have one of those databases in a single file, the status bar reads: "HSQL | Embedded" which means that you are using a HSQLDB which is embedded in the Base document which is a zip archive. When you opening the document, the embedded HSQLDB is "installed" to a temporary directory. When you close a form or table, the modified database is wrapped back into the zip archive.
In the zip archive you find the file database/script which contains the data definition.
For a complete export including the all the data run menu:Tools>SQL...

Code: Select all

SCRIPT 'path/mydb.script'

Your actual database application (in case of embedded HSQL) is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html

[mxdog]

Thanks for the explanation
To summarize for next time I need this....

the SCRIPT 'c:testdb.sql' command will create a dump/sql create file of the database named the supplied name

this only works for the native HSQLDB data engine/file . not for any foreign ones like access MDB file or ACCDB

in my case i just dragged tables from the access db I was working with to a new HSQLDB and ran the script. clumsy but got it done in minutes.
the downside is the data structure takes some liberties with field sizes and types so some fixing will be required.
and they would definitely be some problems with a big/huge database.

[Villeroy]

The SCRIPT command works with all database engines adhering to the SQL standard. Of course it does not work with MS products. You did not mention the actual software you are working with so I assumed that you are working with embedded HSQL. If I would ask a similar question on a MS Access forum, the first thing I would mention that my Access document is connected to MySQL / MariaDB / PostgreSQL / HSQL / whatever.

[mxdog]

I didn't know base was only generating tables from the file for display when I started this. I assumed after they where rendered they would be in the OpenOffice native format and would be available for output or when I was looking at it I assumed they could be used to export to another format. It was my fault not specifying the exact input .... historically for me using Dase and Paradox back in the day importing from other formats it was implied they where then in the native format and could be acted upon as native. I haven't used Access enough to know what that DB does either ( other then it is just another DB file ) ... Anyway your explanation fixed me up ..
thanks again