File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes hsqldb - how to create db? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "hsqldb - how to create db?" Watch "hsqldb - how to create db?" New topic
Author

hsqldb - how to create db?

Brandi Love
Ranch Hand

Joined: Sep 19, 2003
Posts: 133
Hey all!

I'm new to JDBC so bear with me, I'm not quite sure what I'm doing. I've gotten as far as to find and download a dmbs (I'm using hsqldb) and now I've no idea how to actually go about creating a database and creating tables, entering data, etc. I've been searching for help but many resources assume that you've already created a database. For now I want the database to be local. Can someone point me in the right direction to get started? Can I create the database in access first, maybe?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Hypersonic comes with fairly good docs and a sample db so you should start wit those and modify to suit.

HSQLDB can run in many forms, one of the easiest is 'in process', meaning the database only gets created as soon as you try to connect to it - it behaves like a RDBMS but really it's just a library attached to your app which stores and retrieves data. I recommend placing the db files in an absolute location on your drive.

The problem initially is that first time you call the db it will be empty, not even any tables, and setting this up in an application is annoying. In the demo directory you will find a 'runManager' app which you can use to get started.

Create the following files in the /temp directory:

sampleDB.properties


sampleDB.script
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE V_TABLE_USER(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,CN VARCHAR(40) NOT NULL,OU VARCHAR(40) NOT NULL,TITLE VARCHAR(10),FN VARCHAR(10) NOT NULL,SN VARCHAR(10) NOT NULL,MAIL VARCHAR(100),ROLE VARCHAR(5) NOT NULL,MEMBERTYPE VARCHAR(6) NOT NULL,GENDER VARCHAR(1) NOT NULL,USERPASSWORD VARCHAR(40),CONSTRAINT SYS_CT_64 UNIQUE(CN,OU))
ALTER TABLE V_TABLE_USER ALTER COLUMN ID RESTART WITH 5
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 20
SET SCHEMA PUBLIC
INSERT INTO V_TABLE_USER VALUES(1, 'person1', 'companyOne', NULL, 'first1', 'last1', 'person1@companyOne.com', 'ADMIN', 'PRIVATE', 'M', 'password1')
INSERT INTO V_TABLE_USER VALUES(2,'person2', 'companyOne',NULL,'first2', 'last2', 'person2@companyOne.com', 'USER', 'PUBLIC', 'M', 'password1')
INSERT INTO V_TABLE_USER VALUES(3,'person1', 'companyTwo',NULL,'first3', 'last3', 'person1@companyTwo.com', 'ADMIN', 'PRIVATE', 'M', 'password1')
INSERT INTO V_TABLE_USER VALUES(4,'person4', 'companyTwo',NULL,'first4', 'last4', 'person4@companyTwo.com', 'USER', 'PUBLIC', 'M', 'password1')

Start the manager in the hsqldb demo directory and use these settings:

Type: HSQLDB IN-Memory (ie the first option)
Driver: org.hsqldb.jdbcDriver (ie the default option)
URL: jdbc:hsqldb:file:/temp/sampleDB
User: sa
Password (no password)

Hit 'OK' and you should see a database with one table, v_table_user.

Note that if you create an application to connect to the DB, you use the same values as above.

(Marilyn tried to fix formatting by removing code tags)
[ May 27, 2006: Message edited by: Marilyn de Queiroz ]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I appologise for the long line messing u the page formatting, but I haven't checked if the file still works if you break it up so I decided to leave it as is.
 
GeeCON Prague 2014
 
subject: hsqldb - how to create db?