SQL insert into with Hsqldb Script

Question

I am trying to initialise my hsqldb with some default data but seem to be having a problem with identity and timestamp columns.

I just realised that I probably wasn't clear what I meant when I said "script". I am meaning the command line argument that you pass to hsqldb to generate your database at startup. I can successfully run the query inside DbVisualiser or some other database management tool.

I have a table with the following definition:

create table TableBob (
      ID int NOT NULL identity , 
      FieldA varchar(10) NULL, 
      FieldB varchar(50) NOT NULL, 
      INITIAL_DT timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

I can successfully create this table using the script but trying to insert a record doesn't work. Below is what I would consider valid sql for the insert since the ID and INITIAL_DT fields are Identity and Default columns). Strangely it inserts null into every column even though they are defined as NOT NULL....

e.g.
INSERT INTO TableBob (FieldA, FieldB) VALUES ('testFieldA', 'testFieldB');

Thanks for your help

Answer 1

Please try with HSQLDB's DatabaseManagerSwing (you can double click on the hsqldb.jar to start the database manager). First execute the CREATE TABLE statement, then the INSERT statement, finally the SELECT statement.

It should show the correct results.

If you want to use a script to insert data, use the SqlTool.jar which is available in the HSQLDB distribution zip package. See the guide: http://hsqldb.org/doc/2.0/util-guide/