1

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

Soma
  • 861
  • 2
  • 17
  • 32
  • do you need to enter into database to be able to run the script ? – Ashish Jan 03 '14 at 08:38
  • well.. I didn't understand what you're saying ^^ I have some scripts and I want HSQLDB to use them to create the databases on the server – Soma Jan 03 '14 at 08:46
  • i have not used hsqldb but i use mysql instead, so for example if i have a script on my Desktop. i would go into Desktop directory and then enter mysql(mysql -u username -p password) from command line. now i use command "source script.sql" so this is how it picks it up. and yes the extension is .sql. hope it makes sense :) – Ashish Jan 03 '14 at 09:34
  • That will create the database on the MySQL server ? Is the database accessible from a webapp through an URL ? – Soma Jan 03 '14 at 10:01
  • yes it will, depending on what script you write. and yes it will be via application. – Ashish Jan 06 '14 at 08:12

3 Answers3

3

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 ! :)

Soma
  • 861
  • 2
  • 17
  • 32
  • If you don't want any persistence on your database, just delete the file `mydb.backup` from your directory. In fact, the only file you need is `mydb.script` – Soma Jan 07 '14 at 14:26
1

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.

Vikas Kukreti
  • 319
  • 8
  • 16
0

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Right now, I've a different issue. I launch the Server with the command in my question. I've changed the extension of my SQL-file to SCRIPT-file. When the Server is launched, my SCRIPT-file was updated but still have my `CREATE TABLE`. I tried to connect to it with the `DatabaseManager` but it doesn't find any `USER`. I tried the name of the database, sa or blank. Any idea ? – Soma Jan 06 '14 at 16:46
  • Solved it using a server.properties file :) – Soma Jan 07 '14 at 12:44