[Solved] Primary Key Issue: New Record not saving w/ INSERT

[TreCinn]

I've spent the last two days looking through the documentation and forums trying to come up with a reason I can't get new records to save in a table with a primary key when using the INSERT command.

I am using HSQLDB_2.3.2.

If first created the table using:

Code: Select all

CREATE TABLE "tblCertificateOfInsurance"
(
"InsuranceId" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"InsuranceContractorID" int NOT NULL,
"InsuranceExpirationDate" date,
)

Once I created the table I used:

Code: Select all

INSERT INTO "tblCertificationOfInsurance" ("InsuranceID", "InsuranceContractorID", "InsuranceExpirationDate") VALUES (NULL, 99,""01/01/1900"");
    CALL IDENTITY();

The INSERT fails with "user lacks privilege or object not found: tblCertificationOfInsurance". I have tried several suggestions from the forums but nothing seems to work. Can someone point out my mistake?

Ultimately what I need to do is create the record with values from a forum. At this point I just would like to save a new record using SQL.

[FJCC]

This worked for me

Code: Select all

INSERT INTO "tblCertificateOfInsurance" ("InsuranceId", "InsuranceContractorID", "InsuranceExpirationDate") VALUES (NULL, 99,'2015-09-15');

Note the table name is "tblCertificateOfInsurance" not "tblCertificationOfInsurance" and the first column is "InsureanceId" not "InsuranceID". I didn't try any date formats other than 'YYYY-MM-DD' because I have a vague memory that HSQLDB demands that format. I might be wrong.

[TreCinn]

Thanks I will try that. I noticed the table name error but would have never caught the id/ID error.