java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

Hibernate JIRA | mjjuang | 1 decade ago
  1. 0

    I got two tables, course and student. Besides, there is a cross table, courseXstudent, which keeps the relationship between them. With the property "hibernate.jdbc.batch_size" is set greater than 0, an exception will be thrown when I delete an student, who has no course information in the cross table. However, everything is ok when "hibernate.jdbc.batch_size" is set to be 0. I don't know if the batch update or the db2 jdbc driver causes the exception. Please help, thanks. [DDL & data] create table course ( id integer not null generated by default as identity (start with +1), cname varchar(20), primary key (id) ); create table student ( id integer not null generated by default as identity (start with +1), sname varchar(20), did integer not null, primary key (id), foreign key (did) references department(id) on delete restrict ); create table cXs ( id integer not null generated by default as identity (start with +1), sid integer not null, cid integer not null, primary key (id), foreign key (sid) references student(id) on delete cascade, foreign key (cid) references course(id) on delete cascade, unique (sid, cid) ); insert into course values (1, 'Math'), (2, 'English'), (3, 'Science'), (4, 'Chinese'); insert into student values (1, 'John', 1), (2, 'Mary', 2), (3, 'Tom', 1), (4, 'Sara', 2), (5, 'Alan', 1), (6, 'Molly', 2); insert into cXs values (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 2, 1); [hbm file: course.hbm.xml] <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" > <hibernate-mapping> <class name="Course" table="course" lazy="true"> <id name="id" type="java.lang.Integer" column="ID"> <generator class="identity" /> </id> <property name="name" type="java.lang.String" column="cNAME" /> <bag name="students" table="cxs" lazy="true" cascade="save-update" inverse="true"> <key column="cid" /> <many-to-many column="sid" class="Student" outer-join="true" /> </bag> </class> </hibernate-mapping> [hbm file: student.hbm.xml] <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" > <hibernate-mapping> <class name="Student" table="student" lazy="true"> <id name="id" type="java.lang.Integer" column="ID"> <generator class="identity" /> </id> <property name="name" type="java.lang.String" column="sNAME" /> <bag name="courses" table="cxs" lazy="true" cascade="none" inverse="false"> <key column="sid" /> <many-to-many column="cid" class="Course" outer-join="true" /> </bag> </class> </hibernate-mapping> [java file: course.java] public class Course { private Integer id; private String name; private List students; public Integer getId() { return id; } public String getName() { return name; } public void setId(Integer integer) { id = integer; } public void setName(String string) { name = string; } public List getStudents() { return students; } public void setStudents(List set) { students = set; } } [java file: student.java] public class Student { private Integer id; private String name; private List courses; public Integer getId() { return id; } public void setId(Integer integer) { id = integer; } public String getName() { return name; } public void setName(String string) { name = string; } public List getCourses() { return courses; } public void setCourses(List list) { courses = list; } } [main program] public class Run { public static void main(String[] args) throws HibernateException { SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory(); Session sess = sessionFactory.openSession(); Transaction trx = sess.beginTransaction(); Student s = (Student)sess.get(Student.class, new Integer(5)); sess.delete(s); trx.commit(); session.close(); } } [Exception thrown] java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(Unknown Source) at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(Unknown Source) at com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260) at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2421) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2373) at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240) at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61) at Run.main(Run.java:36) 14:11:23,375 WARN JDBCExceptionReporter:38 - SQL Error: 100, SQLState: 02000

    Hibernate JIRA | 1 decade ago | mjjuang
    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
  2. 0

    I got two tables, course and student. Besides, there is a cross table, courseXstudent, which keeps the relationship between them. With the property "hibernate.jdbc.batch_size" is set greater than 0, an exception will be thrown when I delete an student, who has no course information in the cross table. However, everything is ok when "hibernate.jdbc.batch_size" is set to be 0. I don't know if the batch update or the db2 jdbc driver causes the exception. Please help, thanks. [DDL & data] create table course ( id integer not null generated by default as identity (start with +1), cname varchar(20), primary key (id) ); create table student ( id integer not null generated by default as identity (start with +1), sname varchar(20), did integer not null, primary key (id), foreign key (did) references department(id) on delete restrict ); create table cXs ( id integer not null generated by default as identity (start with +1), sid integer not null, cid integer not null, primary key (id), foreign key (sid) references student(id) on delete cascade, foreign key (cid) references course(id) on delete cascade, unique (sid, cid) ); insert into course values (1, 'Math'), (2, 'English'), (3, 'Science'), (4, 'Chinese'); insert into student values (1, 'John', 1), (2, 'Mary', 2), (3, 'Tom', 1), (4, 'Sara', 2), (5, 'Alan', 1), (6, 'Molly', 2); insert into cXs values (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 2, 1); [hbm file: course.hbm.xml] <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" > <hibernate-mapping> <class name="Course" table="course" lazy="true"> <id name="id" type="java.lang.Integer" column="ID"> <generator class="identity" /> </id> <property name="name" type="java.lang.String" column="cNAME" /> <bag name="students" table="cxs" lazy="true" cascade="save-update" inverse="true"> <key column="cid" /> <many-to-many column="sid" class="Student" outer-join="true" /> </bag> </class> </hibernate-mapping> [hbm file: student.hbm.xml] <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" > <hibernate-mapping> <class name="Student" table="student" lazy="true"> <id name="id" type="java.lang.Integer" column="ID"> <generator class="identity" /> </id> <property name="name" type="java.lang.String" column="sNAME" /> <bag name="courses" table="cxs" lazy="true" cascade="none" inverse="false"> <key column="sid" /> <many-to-many column="cid" class="Course" outer-join="true" /> </bag> </class> </hibernate-mapping> [java file: course.java] public class Course { private Integer id; private String name; private List students; public Integer getId() { return id; } public String getName() { return name; } public void setId(Integer integer) { id = integer; } public void setName(String string) { name = string; } public List getStudents() { return students; } public void setStudents(List set) { students = set; } } [java file: student.java] public class Student { private Integer id; private String name; private List courses; public Integer getId() { return id; } public void setId(Integer integer) { id = integer; } public String getName() { return name; } public void setName(String string) { name = string; } public List getCourses() { return courses; } public void setCourses(List list) { courses = list; } } [main program] public class Run { public static void main(String[] args) throws HibernateException { SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory(); Session sess = sessionFactory.openSession(); Transaction trx = sess.beginTransaction(); Student s = (Student)sess.get(Student.class, new Integer(5)); sess.delete(s); trx.commit(); session.close(); } } [Exception thrown] java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(Unknown Source) at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(Unknown Source) at com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260) at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2421) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2373) at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240) at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61) at Run.main(Run.java:36) 14:11:23,375 WARN JDBCExceptionReporter:38 - SQL Error: 100, SQLState: 02000

    Hibernate JIRA | 1 decade ago | mjjuang
    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
  3. 0

    problem with excuteBatch return codes from PreparedStatements

    Google Groups | 2 decades ago | Dawn
    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2] SQL0301N The value of a host variable in the EXECUTE or OPEN statement cannot be used because of its data type. SQLSTATE=02000
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0301N The value of a host variable in the EXECUTE

    Google Groups | 1 decade ago | koti
    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/NT] SQL0301N The value of a host variable in the EXECUTE or OPEN statement cannot be used because of its data type.
  6. 0

    Business service failed to insert the data in DB2

    Oracle Community | 5 years ago | 924244
    java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/LINUXX8664] SQL0798N A value cannot be specified for co umn "SEARCH_ID" which is defined as GENERATED ALWAYS. SQLSTATE=428C9 . Please see the logs for the full DBAdapter logging output prior to this exception. ConnectionFactory property platform lassName was set to org.eclipse.persistence.platform.database.oracle.Oracle10Platform but the database you are connecti g to is DB2/LINUXX8664. Please validate your platformClassName setting. This mismatch can cause the adapter to trigge runtime exceptions or execute SQL that is invalid for the database you are connected to. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCo es with value "--798" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these omposite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties ar integers.

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.BatchUpdateException

      [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException()
    2. COM.ibm.db2
      DB2PreparedStatement.executeBatch
      1. COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
      2. COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(Unknown Source)
      3. COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(Unknown Source)
      3 frames
    3. c3p0:JDBC DataSources/Resource Pools
      FilterPreparedStatement.executeBatch
      1. com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260)
      1 frame
    4. net.sf.hibernate
      JDBCTransaction.commit
      1. net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
      2. net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
      3. net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2421)
      4. net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2373)
      5. net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
      6. net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
      6 frames
    5. Unknown
      Run.main
      1. Run.main(Run.java:36)
      1 frame