[SQL] SQL insert into with Hsqldb Script

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

Continue reading...