I removed
mysqlDbAdapter.setSupportsGeneratedKeys(true);
and
isGenerated="true"
It works! It starts inserting at 200 and takes 20 PKs in batches and
then increment the correct value in AUTO_PK_SUPPORT table.
Where do I click to give you credits!?! ;-)
Thanks again,
Borut
P.S. What is your stand on (thread subject: MySql AUTO_PK_SUPPORT) the
following:
I had a look at DbGenerator class. I think it would be nice if only
tables which do not already exist would be created. This way the
application which calls runGenerator() wouldn't throw
java.sql.SQLException: Table 'foo-bar' already exists. I know there is
a constructor which accepts excludedEntities, but then the application
have to check in advance if the tables are already there and pass
appropriate entities to the constructor. Of course one can always
catch the exception and make it quiet.
Gentry, Michael (Contractor) pravi:
AUTO_PK_SUPPORT table is a Cayenne PK support table. (It is not the
MySQL way.) If you were using Sybase, you'd also have an
AUTO_PK_SUPPORT table created by Cayenne.
The AUTO_INCREMENT is MySQL-only and I believe with Cayenne 1.2, you can
use it instead of AUTO_PK_SUPPORT. The downside to AUTO_INCREMENT is
that you can't cache keys or know the keys in advance (to build
relationships), so this slows down INSERT and potentially UPDATE
operations. The upside to AUTO_INCREMENT is it is popular with many
scripting languages (such as PHP) and if you are trying to interface a
Cayenne-based application to an existing MySQL database, chances are
they used AUTO_INCREMENT to generate primary keys.
You shouldn't use AUTO_PK_SUPPORT and AUTO_INCREMENT on the same table.
/dev/mrg
-----Original Message-----
From: Borut Bolcina [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 11:39 AM
To: [email protected]
Subject: Re: MySql auto generated PK
My MySql knowledge is less than desirable, so pardon me for asking...
I thaught AUTO_PK_SUPPORT is the MySql way of auto incrementing
(together with AUTO_INCREMENT on column).
Are you saying I don't even need AUTO_PK_SUPPORT table?
-Borut
Gentry, Michael (Contractor) pravi:
I didn't keep up with the MySQL stuff too much, but I believe support
was added (sometime in 1.2) for auto-increment columns. The
isGenerated="true" is what controls it, I believe. Assuming my memory
is working, that attribute tells Cayenne to not use the
AUTO_PK_SUPPORT
table and instead rely on MySQL to auto-increment the value and return
it to Cayenne.
/dev/mrg
-----Original Message-----
From: Borut Bolcina [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 2:53 AM
To: [email protected]
Subject: MySql auto generated PK
Is something broken or am I missing something in my setup, but when
inserting, Cayenne is generating PK instead of using AUTO_PK_SUPPORT.
I use Cayenne 1.2RC1, mysql-connector-java-3.1.13
This is mapping:
<db-attribute name="oid" type="INTEGER" isPrimaryKey="true"
isGenerated="true" isMandatory="true"/>
and this is how I generate the tables
MySQLAdapter mysqlDbAdapter = new MySQLAdapter();
mysqlDbAdapter.setSupportsFkConstraints(true);
mysqlDbAdapter.setSupportsGeneratedKeys(true);
generator = new DbGenerator(mysqlDbAdapter, dataMap);
generator.setShouldCreatePKSupport(true);
generator.setShouldCreateFKConstraints(true);
generator.setShouldDropTables(false);
generator.runGenerator(dataSource);
AUTO_PK_SUPPORT table gets generated and correctly populated with
table
names and initial value 200.
When inserting records, Cayenne inserts records with PK 1,2,3,...
ignoring AUTO_PK_SUPPORT. No SELECT FROM AUTO_PK_SUPPORT happens in
log
files.
What did I miss. I think this worked months ago when exercising
Cayenne
with some simple examples.
Cheers,
Borut
