The database returned no natively generated identity value

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

I created a simple database with a users table, when i created a user object and want to save it in the table, always failed because of the error: The database returned no natively generated identity value. I checked every detail, but still can't solve it, how can i deal with it. anything i missed??

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

anyone know how to deal with that??

one further problem is, when i try to create a table in program, like this:
new SchemaExport(cfg).create(true, true);

the error is database.tablename does not exist.

help????????????????

jw3525 · **Joined:** Sat Aug 28, 2004 4:15 pm **Posts:** 61

People might be more interested in helping if you followed the forum recommendations and posted some code.

Why dont you put an entire mapping, and domain object for people to cruise through? This is very likely a simple issue but people can't help you until you help them help you.

joe

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

sorry about wot i did, my code is here:

first, I defined a table in MySQL:

Code:

CREATE TABLE IF NOT EXISTS users(
  userID INT NOT NULL,
  account VARCHAR(20) NOT NULL UNIQUE,
  password VARCHAR(20) NOT NULL,
  email VARCHAR(50) NOT NULL,
  description TEXT NULL,
  gender CHAR(1) NOT NULL,         #m: male; f: female
  dob DATE NULL,
  CONSTRAINT user_pk PRIMARY KEY (userID),
  INDEX (role),
  CONSTRAINT user_fk FOREIGN KEY (role) REFERENCES roles(role)
) TYPE=InnoDB;

correspondent Bean is:

Code:

public class User
    implements Serializable {
  private Integer userID;
  private String account;
  private String password;
  private String email;
  private String description;
  private char gender;
  private String dob;

  public User() {
  }
 
  public User(String account, String password, String email, char gender,
              int role) {
    this.account = account;
    this.showname = account;
    this.password = password;
    this.email = email;
    this.gender = gender;
    this.role = role;
  }

  /**
   * set user's ID
   * @param userID String user's ID
   */
  private void setUserID(Integer userID) {
    this.userID = userID;
  }
  public Integer getUserID() {
    return userID;
  }
  ............Accessor Methods.....................

Mapping file for this Bean:

Code:

  <class name="com.element.photohost.business.object.User" table="users">
    <!--=====Identify definition===========-->
    <id name="userID" column="userID">
      <generator class="native"/>
    </id>
    <!--======property mapping=======-->
    <property name="account" not-null="true" update="false">
      <column name="account" length="20" sql-type="string" unique="true"/>
    </property>
    <property name="password" not-null="true">
      <column name="password" sql-type="string" length="20"/>
    </property>
     ................Normal property mapping statement............

the last part, I defined my hibernate.cfg.xml file like this:

Code:

     <hibernate-configuration>
       <session-factory name="java:/hibernate/HibernateFactory">

         <property name="show_sql">true</property>
         <property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>

         <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
         <property name="connection.url">jdbc:mysql://localhost/photobank</property>
         <property name="connection.username">phAdmin</property>
         <property name="connection.password">phpass</property>

         <property name="c3p0.max_size">20</property>
         <property name="c3p0.min_size">5</property>
         <property name="c3p0.timeout">500</property>
         <property name="c3p0.max_statements">100</property>
         <property name="c3p0.idle_test_period">3000</property>
         <property name="c3p0.acquire_increment">2</property>

         <property name="proxool.pool_alias">pool1</property>

         <property name="jdbc.batch_versioned_data">true</property>
         <property name="jdbc.use_streams_for_binary">true</property>
         <property name="max_fetch_depth">2</property>
         <property name="cache.region_prefix">hibernate.test</property>
         <property name="cache.use_query_cache">true</property>
         <property name="cache.provider_class">net.sf.hibernate.cache.EhCacheProvider</property>


         <mapping resource="com/element/photohost/business/object/User.hbm.xml"/>
        </session-factory>
     </hibernate-configuration>

in my program,

Code:

    sessionFactory = configuration.configure().buildSessionFactory();
    session=sessionFactory.openSession();
    Transaction tx=session.beginTransaction();
    User user = new User("account01", "password01", "email01", 'm', 1);

    session.save(user);
    session.flush();
    tx.commit();

when i run this code, output error message is:

Quote:

Hibernate: insert into users (account, showname, password, defaultIcon, email, description, gender, dob, role) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
17:40:59,543 WARN JDBCExceptionReporter: SQL Error: 1216, SQLState: 23000
17:40:59,543 ERROR JDBCExceptionReporter: Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails"
17:40:59,553 WARN JDBCExceptionReporter: SQL Error: 1216, SQLState: 23000
17:40:59,553 ERROR JDBCExceptionReporter: Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails"
17:40:59,553 ERROR JDBCExceptionReporter: could not insert: [com.element.photohost.business.object.User]
java.sql.SQLException: Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails"

[/code]

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

sorry, i made some mistakes.

the table definition:

Code:

CREATE TABLE IF NOT EXISTS users(
  userID INT NOT NULL,
  account VARCHAR(20) NOT NULL UNIQUE,
  password VARCHAR(20) NOT NULL,
  email VARCHAR(50) NOT NULL,
  description TEXT NULL,
  gender CHAR(1) NOT NULL,         #m: male; f: female
  dob DATE NULL,
) TYPE=InnoDB;

and the output error is:

Hibernate: insert into users (account, showname, password, defaultIcon, email, description, gender, dob, role) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
net.sf.hibernate.HibernateException: The database returned no natively generated identity value

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

the exception track is:

Hibernate: insert into users (account, password, email, description, gender, dob) values (?, ?, ?, ?, ?, ?)
net.sf.hibernate.HibernateException: The database returned no natively generated identity value
at net.sf.hibernate.persister.AbstractEntityPersister.getGeneratedIdentity(AbstractEntityPersister.java:1230)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:530)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:432)
at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIdentityInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:932)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:857)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:775)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at com.element.photohost.business.object.VoRunning.main(VoRunning.java:36)
Exception in thread "main"

jw3525 · **Joined:** Sat Aug 28, 2004 4:15 pm **Posts:** 61

Quote:

Did you define a sequence to use for this? Native means it will use either sequence or identity depending on what the db you're using uses. But, you still need to create that for your db.

It seems as though you abandoned using SchemaExport. That would make the sequence you need if you give your id generator a name in your mapping.

While the ddl generated from SchemaExport might not be what you want for production purposes it is excellent for checking your mappings, etc..

Check the reference document for the section about IDs. It is very well written and will tell you what you need to know.

Good luck
Joe

jw3525 · **Joined:** Sat Aug 28, 2004 4:15 pm **Posts:** 61

Just to be more clear -

You need to define the id generator in the database

Something along the lines of

Code:

create sequence mysequence ...

In sql.

gavin · **Posted:** Sat Jan 15, 2005 7:44 pm

Actually, you need to have the primary key defined as autoincrement. Why not let SchemaExport generate your DDL for you? It will make much less mistakes ;-)

redragon6479 · **Joined:** Thu Jan 13, 2005 3:11 am **Posts:** 11

Thanks soo much for all of you, I solved the problem by creating a table: Hibernate_unique_key with one record: 1.

rodrigueznan · **Joined:** Mon Jan 30, 2006 5:23 pm **Posts:** 2

I'm having the same error as you were having with Hibernate 3 and SQL 2000.

Could you please give me some information on how you solved this problem?

I'm able to update, delete and search for records in the database, but I can't insert new records.

Thanks

NRodriguez

plughead · **Joined:** Tue Feb 14, 2006 10:29 am **Posts:** 1

I had the same problem with MS SQL 2000. It turns out that the triggers we have on our database have an 'insert instead of' clause in them.

The problem is that hibernate (and perhaps the JDBC driver itself) uses 'SELECT SCOPE_IDENTITY()' after the insert to return the id of the newly created row. Normally this is what you want, but because of our triggers, the id returned was always null. Apparently when you use insert instead of, the new record gets created outside the scope of SCOPE_IDENTITY().

My solution was to override the SQLServerDialect class and provide a replacement to the appendIdentitySelectToInsert() method that uses IDENT_CURRENT() instead, like this:

Code:

public String appendIdentitySelectToInsert(String insertSQL) {
    String table = insertSQL.substring(12);
    table = table.substring(0, table.indexOf('(')-1).trim();
    return insertSQL + " select IDENT_CURRENT('"+table+"')";
}

IDENT_CURRENT() returns the current identity for a specific table, regardless of scope. (Note that you'll probably get some really strange bugs using this, if your trigger happens to insert into the same table that you're inserting into in the first place....)

Then replace the SQLServerDialect with your own class name in hibernate.cfg.xml--you'll also have to add this line, so the append...() method actually gets used:

Code:

<property name="hibernate.jdbc.use_get_generated_keys">false</property>

HTH,
-Jason

arun.rocky4 · **Joined:** Sat Sep 18, 2010 2:23 am **Posts:** 1

hi ,

check your table and u can find the column id wont have the auto_increment option . check with ur respective databases how to create an table with auto_increment for a column. i have faced the same issue but once i altered my table now its working fine.