incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: Retrieving auto-generated keys after insert
Date Sun, 04 Oct 2009 08:31:38 GMT
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 (acooke.org@gmail.com) 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.

Mime
View raw message