Hi Andrew,
sorry that I have not been able to reply earlier.
First it is our approach to let the database handler (any class derived from
DBDatabaseDriver) decide how to handle key generation for auto increment
fields. Basically there are three approaches supported by Empire-db:
1. The keys are generated using a sequence object if the database support such
a feature
2. Use auto-increment fields of the database if the database support such a
feature
3. Use a special sequence table in which Empire-db manages sequences itself.
The preffered way for the code that is using Empire-db is to use a DBRecord
object to insert new records. This will handle the key generation and provides
the key after the update method (which does inserts as well) has been called -
simply by using the getter of the appropriate field - no matter which of the
three methods above is used by the driver.
So if you want to use autoincrement fields of the databse you first have to
make sure, your database driver is using the auto-increment fields of the DB.
Currently this is the default e.g. for MySQL (DBDatabaseDriverMySQL) and
SQLServer (DBDatabaseDriverMSSQL) but not for HSQLDB.
The HSQLDB driver (DBDatabaseDriverHSql) has been implemented to use Sequences
which are also supported by HSQLDB.
Hence the value of an field that has been set to DataType.AUTOINC will be
retrieved using the database driver's getNextSequenceValue() method. If you
want to change this behaviour you would have to derive your own class from
DBDatabaseDriverHSql, overwrite the method isSupported(DBDriverFeature type)
and return false for DBDriverFeature.SEQUENCES. If you use Empire-db's DDL
generation to create the database or database objects you would have to make
modifications there as well.
BTW: To have a choice here with HSQLDB might be a possible improvement for an
upcoming release. If you work it out we would be grateful if you would let us
know what you have done.
Once this has been sorted out, you should now be able to use a DBRecord as
suggested in the samples. But you may also write your own update statement
using a DBCommand object, although this is more complex. First you need to
create a callback handler class that implments DBDatabaseDriver.DBSetGenKeys
and then provide it with the executeSQL function similar to this:
private static class MyIdHandler implements
DBDatabaseDriver.DBSetGenKeys {
public MyIdHandler(...) {
}
public void set(Object value) {
// set the key;
}
}
DBCommand cmd = db.createCommand();
cmd.set(field, value);
...
int affected = db.executeSQL(
cmd.getInsert(),
cmd.getCmdParams(),
conn,
new MyIdHandler(...));
But remember that this will only work for databases that support autoincrement
fields.
I hope I was of some help for you problem and I would appreciate if you let me
know.
Regards
Rainer
andrew cooke ([email protected]) wrote:
> re: Retrieving auto-generated keys after insert
>
> Hi,
>
> I realise that this is a complex problem, and that each database
> engine has a different solution, but does Empire DB provide any
> uniform way to retrieve the key for an insert if it has been
> auto-generated?
>
> Looking at the API I can see several related functions, but I can't
> work out what the standard approach should be. Maybe related - I
> cannot work out how to create and insert a record, only retrieve or
> modify one (I thought that if I could insert a record then I could
> read the key from the record, which might be how it is supposed to
> work - obviously I can use a "raw" SQL insert to insert data, but then
> I am back with the key problem)
>
> Anyway, if this is possible, I would love to know how. Sorry if I've
> missed something obvious - I've been looking through the docs and
> googling, but I really can't find the answer...
>
> Otherwise, I really like the library. It's like SQLAlchey, but
> simpler (thankfully!)
>
> Thanks,
> Andrew
>
> PS If there is no uniform way I can live with a HSQLDB specific
> solution if someone happens to know what that might be.
