• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

hsqldb - how to create db?

 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic