Connect databases to HSQLDB Server

问题:

I'm using version hsqldb-1.8 in a stand-alone way. I want to start the server with some databases. I'd like to use the command line from the HSQLDB documentation :

java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb

My problem is I have the script of each database I need and this command line creates a new database, without using my script. May the problem comes from one of these points :

• The location of my script
• The extension of my script which is a SQL-file
• Something wrong or missing in the command line
• The command line can't do it => If yes, is there any way to do it ?

I'd like to stay in the console to do all that. That way, I'll only have to launch a script to do all the job. Any help will be much appreciated ! Thanks

回答1:

I found the solution of my issue :)

I've created a server.properties file located in the directory ../hsqldb-1.8.10/hsqldb/ which contains that :

server.database.0=file:mydb;user=test;password=test
server.dbname.0=mydb

I've also created the mydb.script file with that code in it:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE MYDB(ID BIGINT NOT NULL,VERSION INTEGER NOT NULL,NOM VARCHAR(255))
CREATE USER TEST PASSWORD "TEST"
GRANT DBA TO TEST
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO MYDB VALUES(1,0,'test')

Then, I launch the HSQLDB Server with this command:

java -cp ../lib/hsqldb.jar org.hsqldb.Server

We can see that the database is successfully created :

[Server@10f0f6ac]: Database [index=0, id=0, db=file:mydb, alias=mydb] opened successfully in 313 ms.

To check if the database really contains my data, I use the HSQLDB DatabaseManager tool with this command:

java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager

To connect:

• URL : jdbc:hsqldb:file:mydb
• User : test
• Password : test

After that, we are connected to the database. Execute the command SELECT * FROM MYDB; and we can see the line of the database.

Hope that will help ! :)

回答2:

The easist way to connect with with HSQLDB (IMDB)

Connection String

<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url" value="jdbc:hsqldb:file:/home/vikask/elmo/db/elmo;" />
<property name="username" value="sa" /> 
<property name="password" value="" />

In this simple Java project to demonstrate Hibernate, HSQL and Maven using Java Annotations. HSQL database is used to make the project simple, as we can use in-memory database and we would need only a JAR file to be included in our project.

To connect to an embedded HSQLDB database, select the JDBC (HSQLDB Embedded) connection type from the connection type list. Enter any login information if applicable, and then specify whether to use an existing embedded database, or to have HSQLDB create a new embedded database.

If the embedded database already exists, browse to the directory where the database files are located (such as database_name.log, database_name.script, and database_name.properties) and select the database_name.script file. If the database does not exist, type in or browse to create a new location for the HSQLDB database. HSQLDB will then create the necessary files with the prefixed with the database name typed in. For example, if typing /home/vikask/sample as the location of the database, HSQLDB will create a file called sample.properites, and perhaps sample.log, etc. The actual name of the database is simply sample in this case.

回答3:

HSQLDB creates a file with the .script extension for its internal use. This is not something that you create.

First run the server and connect to one of the databases. The database will be empty at this point. Then use the utilitly, SQLTool which is in the HSQLDB zip package to execute YOUR script on the database. All the tables and data that are created by your script are persisted in the HSQLDB database.