JPA called stored procedure selecting data from 2 db

Question

I am using mySql and jee application using jpa and hibernate

I have a stored procedure that deletes data from table in db1 then selects data from table from db2 and inserts them in table from db1

CREATE PROCEDURE `update_data`(myId int(11))
BEGIN
    DELETE FROM db1.tbl1
    WHERE db1.tbl1.id = myId;

    INSERT INTO
    db1.tbl1
    SELECT * FROM
    db2.tbl1
    WHERE db2.tbl1.id = myId;
END

When calling in sql it succeeds When calling from jpa it returns exception

ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-17) ResultSet is from UPDATE. No Data.
ERROR [stderr] (default task-17) org.hibernate.exception.GenericJDBCException: could not execute query

JAVA code:

@PersistenceContext(name = "db2", unitName = "db2")
EntityManager em;

org.hibernate.Session session = (org.hibernate.Session) em.getDelegate();

        Transaction tx = null;
        tx = session.beginTransaction();
        try {
            Query query = session
                    .createSQLQuery("CALL update_teachers(:sesId)")
                    .addEntity(Teacher.class).setParameter("sesId", sesId);
            query.list();
            session.flush();
            session.clear();

            tx.commit();
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } finally {
        session.close();
        }

The problem seems that there is selection from 2 different databases where em can't handle this operation How can i perform this task

Note : I also tried native named query and i tried @NamedStoredProcedureQuery All gave the same exception

What is the value od `sesId` parameter when you call the procedure?And what MySQL version are you using? I found two MySQL bugs reported for this that could be related if you use the affected version: https://bugs.mysql.com/bug.php?id=15500 and http://bugs.mysql.com/bug.php?id=25009 — Sva.Mu, Sep 20 '15 at 06:23

score 2 · Answer 1 · answered Sep 18 '15 at 18:30

2

You seem to execute the stored procedure and expect a result set in return:

query.list();

This doesn't seem correct to me, you won't be getting any records. Try calling it as if you want to run update or delete:

query.executeUpdate();

answered Sep 18 '15 at 18:30

Sva.Mu

1,141
1
14
24

The problem is that only returning an exception where in case the stored procedure is succeeding with no errors I checked db and that what i found – oooss Sep 19 '15 at 11:28

score 0 · Answer 2 · answered Sep 21 '15 at 12:16

As Sva.Mu already wrote, the stored procedure call throws an exception because the entity manager expect a result from the call, if you use

query.list();

You can see it in this part of the error message

ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-17) ResultSet is from UPDATE. No Data.

The call should work, if you instead use

query.executeUpdate()

In that case, the entity manager will not expect any result from the stored procedure call.

This is the same, if you use a @NamedStoredProcedureQuery or the more dynamic StoredProcedureQuery. You can read more about it on my here: @NamedStoredProcedureQuery and StoredProcedureQuery

JPA called stored procedure selecting data from 2 db

2 Answers2