Mailing-List: contact empire-db-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-user@incubator.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Content-class: urn:content-classes:message
Subject: re: Retrieving auto-generated keys after insert
Date: Sun, 4 Oct 2009 10:31:38 +0200
Message-ID: <B297D4737108A0459B66C3A85BB7F9011855@kn.esteam.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <4ca747c30910011645q39bc419aj982dc068b3cf83f6@mail.gmail.com>
Thread-Topic: re: Retrieving auto-generated keys after insert
Thread-Index: AcpC8X3n2lCV3Z4YRgWhH33J/xDp+QB13zvA
References: <4ca747c30910011645q39bc419aj982dc068b3cf83f6@mail.gmail.com>
From: =?iso-8859-1?Q?Rainer_D=F6bele?= <doebele@esteam.de>
To: <empire-db-user@incubator.apache.org>

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(...) {
		}	=09
		public void set(Object value) {
			// set the key;
		}
	}

	DBCommand cmd =3D db.createCommand();
	cmd.set(field, value);
	...
	int affected =3D db.executeSQL(
				cmd.getInsert(),=20
				cmd.getCmdParams(),=20
				conn,=20
				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
>=20
> Hi,
>=20
> 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?
>=20
> 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)
>=20
> 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...
>=20
> Otherwise, I really like the library.  It's like SQLAlchey, but
> simpler (thankfully!)
>=20
> Thanks,
> Andrew
>=20
> PS If there is no uniform way I can live with a HSQLDB specific
> solution if someone happens to know what that might be.