Cannot insert object to HSQLDB with ActiveJDBC

Question

I'm trying to use ActiveJDBC with HSQLDB:

Users.sql

CREATE TABLE users (
    "ID" INTEGER GENERATED BY DEFAULT AS SEQUENCE seq NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(100) NOT NULL,
    LAST_NAME VARCHAR(100) NOT NULL
);

User.java

@IdName("ID")
public class User extends Model {....}

Main.java

User u = new User();
u.setFirstName("first_name");
u.setLastName("last_name");
u.save();

And when I try to save new row I have the following exception:

org.javalite.activejdbc.DBException: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: id, Query: INSERT INTO users (first_name, last_name) VALUES (?, ?), params: first_name,last_name

I think that problem in lower case in insert query. How I can fix this problem?

Why exactly is the ID name in double quotes in the create script? — Gimby, Nov 25 '16 at 15:52
As I understood from documentation double quotes make field name case sensetive — ap_student, Nov 25 '16 at 16:12
This has nothing to do with the framework. Try executing the same without ActiveJDBC and resolve the privilege issue: INSERT INTO users (first_name, last_name) VALUES (?, ?). Additionally, HSQL is not in a list of supported databases: http://javalite.io/activejdbc#supported-databases, but most things should still work out of the box . — ipolevoy, Nov 25 '16 at 16:21
@ipolevoy I executed and request works correctly without ActiveJDBC — ap_student, Nov 25 '16 at 16:50
@Nastya, can you enable loggin and see exactly what SQL is generated: http://javalite.io/logging, and add entire stack trace — ipolevoy, Nov 25 '16 at 17:15
I think problem in following ActiveJDBC code : ps = e.prepareStatement(query, new String[]{autoIncrementColumnName}) in class org.javalite.activejdbc.DB#execInsert autoIncrementColumnName = this.getMetaModelLocal().getIdName() in class org.javalite.activejdbc.Model#insert and equals "id" (in lower case). But if I set this variable as "ID" (in upper case) it works correctly. Maybe you know why this.getMetaModelLocal().getIdName() return id in lower case if I set @IdName("ID") — ap_student, Nov 25 '16 at 18:53
I think the answer is http://javalite.github.io/activejdbc/1.4.11/org/javalite/activejdbc/Model.html#toMap-- — ap_student, Nov 25 '16 at 18:55

score 1 · Answer 1 · answered Nov 25 '16 at 16:01

1

CREATE TABLE users (
    ID INTEGER GENERATED BY DEFAULT AS SEQUENCE seq NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(100) NOT NULL,
    LAST_NAME VARCHAR(100) NOT NULL
);

answered Nov 25 '16 at 16:01

j.Stark

191
2
8

What exactly do I have to do from this? Because I did't understand – ap_student Nov 25 '16 at 17:16

score 1 · Accepted Answer · answered Nov 27 '16 at 05:56

After googling for this HSQL exception message, I threw this code together that actually works. It uses ActiveJDBC, works with plain query and also works with instrumented model:

Base.open("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:file:./target/tmp/hsql-test", "sa", "");

String create = "CREATE TABLE people (\n" +
        "    ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,\n" +
        "    FIRST_NAME VARCHAR(100) NOT NULL,\n" +
        "    LAST_NAME VARCHAR(100) NOT NULL\n" +
        ");";

Base.exec(create);
Base.exec("INSERT INTO people (first_name, last_name) VALUES (?, ?)", "John", "Doe");

System.out.println("===>" + Base.findAll("select * from people"));

Person p = new Person();
p.set("first_name", "Jane", "last_name", "Doe").saveIt();

System.out.println(Base.findAll("select * from people"));
System.out.println(Person.findAll());
Base.close();

As you can see, the code that creates a table is a bit different, especially around the ID column.

The model looks like this:

@IdName("ID")
public class Person extends Model {}

Basically, you had the following issues with your code:

Definition of ID column with double quotes
ID column needs to be defined as IDENTITY (do not hold my feet to the fire, I'm no HSQL expert, but t works)
The model needs to overwrite the @IdName("ID"), since ActiveJDBC defaults to lower case id, unless you change that in the table (why not?)

Keep in mind, that some things may not work, since HSQL is not on a list of supported databases.

I hope this helps!

`GENERATED BY DEFAULT AS SEQUENCE seq ` is fine but you need to `CREATE SEQUENCE seq AS INTEGER` first. — fredt, Dec 11 '16 at 01:20

Cannot insert object to HSQLDB with ActiveJDBC

2 Answers2