[Solved] Database Export

[LeroyTennison]

Created a simple database (one table, 3 records, 8 fields/record, size: 3.7 Kb) in ODB format and saved it. Selected File->Save As and the only option was ODB, is Base not able to save in other formats? I realize I can register the database, open it in Calc and export it there but that's not the point.

[eremmel]

Yes.

[Villeroy]

Tools>SQL...

Code: Select all

SELECT * INTO TEXT "FileName" FROM "TableName"

exports from table "TableName" to a file "FileName.csv"

ODB is not a document format and Base is not a database. Things are very different with Base.

[LeroyTennison]

Thank you for your responses, I appreciate the replies. I would like to get some clarification as I am trying to understand the differences between Access and Base. If my questions have been covered before feel free to reply with URLs to the discussions/answers.

eremmel:

• Should I understand your "Yes" answer as meaning that Base can save in only one format?

villeroy:

• You said "ODB is not a document format and Base is not a database. Things are very different with Base."
  
  What little reading I've done seems to indicate that the ODB format is more or less a compressed archive ("ZIP file"), is that what you mean by "not a document format"? Is it not a standardized and recognized file format like Writer, Calc and Impress? Is the file format for Microsoft Access considered a recognized file format (I'm trying to compare the capabilities of the two)?
  
  When you say "Base is not a database" in what sense do you mean that? I understand that it is a program to create and manipulate databases (for lack of a better term) and certainly not an SQL client-server implementation. My current understanding is that you would say the same thing about Access too.
  
  I realize that an answer to "Things are very different with Base." could easily turn into a lengthy response but could you reply with or point me to a high level discussion of the most important differences?

Again, thanks for your responses, I appreciate any input I can get in helping me understand Base and how it compares to Access better.

[Villeroy]

Just like MS Access, Base is NOT a database. JET is the database that is used by default with MS Access and embedded in .mdb files. HSQL is the database that is used by default with Base and embedded in .odb files. You can use MS Access with MySQL databases or a dozend of other database engines just like a Base document can be connected to any database engine you have a driver for. MS Access is a tool to ACCESS databases, create meaningful queries, input forms and output reports for databases in general. The difference between MS Access and Base amounts to a decade of development and some dozends of millions of dollars. An ODB or MDB document may contain some database or not but it is not a database. The database and/or the software handling the database may reside on a different computer. Access/Base is only the tool to read from and write to some type of database. Right now, a set of scripts connects your browser with the MySQL database that stores this forum in tables with strings, numbers, dates, times and URLs.

[eremmel]

eremmel:

Should I understand your "Yes" answer as meaning that Base can save in only one format?

Yes.

P.S.
Logic should reigns in Base forum
Villeroy pointed you to some capabilities that the used database engine has might not be that portable.

[LeroyTennison]

Thank you both for your replies, I appreciate the clarification.

[LeroyTennison]

Hopefully final question related to eremmel's point about capabilities which may not be portable, am I assuming correctly that, if I want to determine the capabilities and limitations of the Tools->SQL interface, I should look at the documentation for the kind of database I've opened (for example, hsqldb.org for the HSQL database engine)? I did do enough research to note that the TEXT keyword appears to be specific to HSQL (apparently MySQL uses a different syntax).

[Villeroy]

Contrary to the GUI, there are zero limitations when using Tools>SQL. You can issue any command that is understood by the respective database engine.
If the statusbar of your db document indicates "embedded HSQLDB" this is the software you are actually working with: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Of course you may connect a Base document to MySQL if this is your preferred database engine and use Tools>SQL with MySQL syntax.

[Sliderule]

LeroyTennison:

You said / asked:

if I want to determine the capabilities and limitations of the Tools->SQL interface, I should look at the documentation for the kind of database I've opened (for example, hsqldb.org for the HSQL database engine)?

I would just like to add some additional information to the excellent response given by Villeroy above.

1. Since, from the Menu: Tools -> SQL... . . . after executing a command ( really a DDL . . . ( Data Definition Language ) ) . . . any DDL command canNOT return a result set. Therefore, a Query ( Select statement ) should be issued in the Query section of Base.
2. Any command issued, including date and time format, must match the back-end database required syntax, since, Base will NOT read and parse the statement first.
3. Base will only read the database information schema once, when it ( the *.odb file ) is opened. Therefore, if you make a change ( for example, a table definition ) to a table, if you look at it with the Base GUI ( Graphical User Inferace ) . . . you will need to 're-read' the information schema . . . by EITHER
   1. Close and re-open the Base file ( *.odb )
   2. From the Base Menu: View -> Refresh Tables

I hope this helps, please be sure to let me / us know.

Sliderule

[LeroyTennison]

Thanks to both of you for your replies, good information to have.