Difference between revisions of "HSQLDB:Tips and Tricks"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (How to connect to a "normal" (not embedded) HSQL database?)
(22 intermediate revisions by 8 users not shown)
Line 1: Line 1:
 +
{{Documentation/Candidate}}
 +
__TOC__
 +
 +
= The Base FAQ (Frequently Asked Questions) =
 +
* See also the <big>[http://wiki.openoffice.org/wiki/FAQ_(Base) Base (FAQ)]</big> for additional information on these and other topics.
 +
 
= How to connect to a "normal" (not embedded) HSQL database? =
 
= How to connect to a "normal" (not embedded) HSQL database? =
  TODO
+
* Connect via JDBC
 +
* as driver class, use org.hsqldb.jdbcDriver
 +
* enter the URL to your HSQL database as described in the [http://www.hsqldb.org/web/hsqlDocsFrame.html HSQLDB documentation], and append <code>;default_schema=true</code> to the URL
  
 
= How to migrate data between embedded and non-embedded HSQLDB? =
 
= How to migrate data between embedded and non-embedded HSQLDB? =
   TODO
+
    
 +
Starting work for this section see blog entry titled
 +
[http://blog.360.yahoo.com/blog-zNQn2Rw.dKkBKTB_X7..bvsZ?bid=15&yy=2007&mm=3  Migrating Base embedded db to HSQL server].
  
 
= How to know the version of the embedded HSQLDB? =
 
= How to know the version of the embedded HSQLDB? =
 
You can determine the version of the embedded HSQLDB engine with the following Basic macro
 
You can determine the version of the embedded HSQLDB engine with the following Basic macro
<code>[basic]
+
<source lang="oobas">
 
Option Explicit
 
Option Explicit
  
Line 16: Line 26:
 
   ' or a complete URL
 
   ' or a complete URL
  
  Dim databaseContext as Object
+
  Dim databaseContext As Object
 
  databaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
 
  databaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
  
Line 22: Line 32:
 
  databaseDocument = databaseContext.getByName( databaseURLOrRegisteredName )
 
  databaseDocument = databaseContext.getByName( databaseURLOrRegisteredName )
  
  Dim connection as Object
+
  Dim connection As Object
 
  connection = databaseDocument.getConnection( "", "" )
 
  connection = databaseDocument.getConnection( "", "" )
  
Line 29: Line 39:
 
  connection.close
 
  connection.close
 
End Sub
 
End Sub
</code>
+
</source>
Note that this will give you a version number such as "1.8.0", but ''not'' "1.8.0.1". To my best knowledge, this last digit in the HSQLDB version cannot be obtained by any means at runtime.
+
Note that this will give you a version number such as "1.8.0", but ''not'' "1.8.0.1".
 +
 
 +
The minor version can be obtained by issueing a SQL statement in the query designer's "Direct SQL" mode:
 +
 
 +
  call "org.hsqldb.Library.getDatabaseFullProductVersion"()
 +
 
 +
will give you the full version. Drawbacks:
 +
* This is only available in 1.8.0.5 and later
 +
* in 1.8.0.5, it - wrongly - returns 1.8.0.4 :(
 +
 
 +
If you replace <code>getDatabaseFullProductVersion</code> in the above SQL statement with <code>getDatabaseProductVersion</code>, then you'll get the same result as with the macro above.
 +
 
 +
If you, for whatever reason, can not connect to embedded HSQL databases anymore, but want to know the version you're using, nonetheless, do the following:
 +
* create a database pointing to some external HSQLDB (new DB of type JDBC, with an URL "jdbc:hsqldb:file:foo" - this will create a HSQL database named "foo" in the processes startup directory (you can also specify a complete path).
 +
* create a new query in SQL view
 +
* enter
 +
  call "org.hsqldb.Library.getFullDatabaseProductVersion"()
 +
* press the "Run SQL Direct" button in the toolbar
 +
* execute the query
 +
 
 +
[[Category:How to]]
 +
[[Category:HSQLDB]]
 +
 
 +
= How to retrieve the definition of a view =
 +
In Base once a view has been created there is no obvious way to retrieve the view definition. You can do so however using a query.
 +
 
 +
If the database had view named View1 you would do the following:
 +
 
 +
*Open a new Query design window, in SQL view mode.
 +
*Enter the following select statement -
 +
 
 +
  SELECT VIEW_DEFINITION
 +
  FROM INFORMATION_SCHEMA.SYSTEM_VIEWS
 +
  WHERE TABLE_NAME = "View1"
 +
 
 +
You can not alter the definition directly in this system table, however if you select create view in Base you can turn off the designer and copy paste the select statement that you just received, make whatever changes you need and save it as a new view.
 +
 
 +
[[Category:How to]]
 +
[[Category:HSQLDB]]

Revision as of 01:58, 31 January 2013

The Base FAQ (Frequently Asked Questions)

  • See also the Base (FAQ) for additional information on these and other topics.

How to connect to a "normal" (not embedded) HSQL database?

  • Connect via JDBC
  • as driver class, use org.hsqldb.jdbcDriver
  • enter the URL to your HSQL database as described in the HSQLDB documentation, and append ;default_schema=true to the URL

How to migrate data between embedded and non-embedded HSQLDB?

Starting work for this section see blog entry titled Migrating Base embedded db to HSQL server.

How to know the version of the embedded HSQLDB?

You can determine the version of the embedded HSQLDB engine with the following Basic macro

Option Explicit
 
Sub hsqlVersion
 Dim databaseURLOrRegisteredName As String
 databaseURLOrRegisteredName = "hsqldb"
  ' adjust this string to your needs. It needs to be the name of a registered database,
  ' or a complete URL
 
 Dim databaseContext As Object
 databaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
 
 Dim databaseDocument As Object
 databaseDocument = databaseContext.getByName( databaseURLOrRegisteredName )
 
 Dim connection As Object
 connection = databaseDocument.getConnection( "", "" )
 
 MsgBox "product version: " & connection.getMetaData().getDatabaseProductVersion()
 
 connection.close
End Sub

Note that this will give you a version number such as "1.8.0", but not "1.8.0.1".

The minor version can be obtained by issueing a SQL statement in the query designer's "Direct SQL" mode:

 call "org.hsqldb.Library.getDatabaseFullProductVersion"()

will give you the full version. Drawbacks:

  • This is only available in 1.8.0.5 and later
  • in 1.8.0.5, it - wrongly - returns 1.8.0.4 :(

If you replace getDatabaseFullProductVersion in the above SQL statement with getDatabaseProductVersion, then you'll get the same result as with the macro above.

If you, for whatever reason, can not connect to embedded HSQL databases anymore, but want to know the version you're using, nonetheless, do the following:

  • create a database pointing to some external HSQLDB (new DB of type JDBC, with an URL "jdbc:hsqldb:file:foo" - this will create a HSQL database named "foo" in the processes startup directory (you can also specify a complete path).
  • create a new query in SQL view
  • enter
 call "org.hsqldb.Library.getFullDatabaseProductVersion"()
  • press the "Run SQL Direct" button in the toolbar
  • execute the query

How to retrieve the definition of a view

In Base once a view has been created there is no obvious way to retrieve the view definition. You can do so however using a query.

If the database had view named View1 you would do the following:

  • Open a new Query design window, in SQL view mode.
  • Enter the following select statement -
 SELECT VIEW_DEFINITION 
 FROM INFORMATION_SCHEMA.SYSTEM_VIEWS 
 WHERE TABLE_NAME = "View1"

You can not alter the definition directly in this system table, however if you select create view in Base you can turn off the designer and copy paste the select statement that you just received, make whatever changes you need and save it as a new view.

Personal tools