[EoDsql-users] Auto generated keys in Derby

[Ricardo P. <rpm...@ya...>]

Hi,

I'm using EoD SQL 1.1. I have a table with an ID column that I want
the database to generate automatically, and I intend my program to be
able to use different database engines through JDBC and standard SQL.

I've seen in the file "database_notes.txt" this warning about Derby
(which is what I'm using to develop and test):

> 2) Auto-generated keys are supported by the database engine, but only as unnamed
>    columns. You can either use UUID's or RETURNED_KEYS_FIRST_COLUMN to work around
>    this limitation.


I've done this in my "ProductQuery" interface to deal with Product class:

        @Update(sql = "INSERT INTO product (prodcode, prodname, "
                      + "produrl, prodnotes, prodowner) "
                      + "VALUES (?{1}, ?{2}, ?{3}, ?{4}, ?{5})",
        keys = GeneratedKeys.RETURNED_KEYS_FIRST_COLUMN)
        public Product insertProduct(String prodCode, String prodName,
                                     String url, String notes,
                                     String prodOwner)
                                     throws SQLException;

but I keep getting this exception (message is translated back from
Spanish):

java.sql.SQLIntegrityConstraintViolationException: Column 'ID' can't
accept a NULL value.


While working with EoD SQL 1.0, I managed to have a similar scenario
working with no problem:

        @Update(sql="INSERT INTO versions (l10ncode, prodcode, "
                  + "verscode, versvcsroot, versbasedir, "
                  + "verspreimpcmd, verspostexpcmd, versowner) "
                  + "VALUES (?{1} , ?{2}, ?{3}, ?{4}, ?{5}, ?{6}, "
                  + "?{7}, ?{8})",
                keys = GeneratedKeys.RETURNED_KEYS_DRIVER_DEFINED)
        public DataSet<Version> insertVersion(String l10nCode,
                String prodCode, String versCode, String verVcsRoot,
                String versBaseDir, String versPreImpCmd,
                String versPostExpCmd, String versOwner)
                throws SQLException;


Am I doing something wrong? Or really something in EoD SQL 1.1 changed
from 1.0? I know I could use UUIDs, but, in some cases, I have other
string fields that I could use as primary key, and in others, I really
need a synthetic ID field.

TIA