Is it possible to add Stored procedure dynamically from hibernate?

Question

I want to add stored procedures dynamically from my Java code using hibernate. I did the following but this constantly results in error saying "

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

what am I doing wrong ?

String dnyLockProcedure = "DELIMITER ||" 
                        +"\n DROP PROCEDURE IF EXISTS `dyn_lock`; "
                        +"\n CREATE PROCEDURE `dyn_lock`(IN `query` TEXT, IN `lock_name` CHAR(64), IN `timeout` TINYINT UNSIGNED)"
                        -- PROCEDURE HERE 
                        +"\n END;"
                        +"\n || "
                        +"\n DELIMITER ;";

                Query queryTest = mEntityManager.createNativeQuery(dnyLockProcedure);
                queryTest.executeUpdate();

EDIT: Below is the entire procedure:

        String dnyLockProcedure ="DROP PROCEDURE IF EXISTS `dyn_lock`; "
                +" CREATE PROCEDURE `dyn_lock`(IN `query` TEXT, IN `lock_name` CHAR(64), IN `timeout` TINYINT UNSIGNED)"
                +" NO SQL "
                +" LANGUAGE SQL "
                +" COMMENT 'Get a lock for dyn cursor' "
                +" BEGIN "
                +" DECLARE lock_res TINYINT UNSIGNED;"
                +" DECLARE CONTINUE HANDLER"
                +" FOR SQLEXCEPTION"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Dynamic SQL returned an error';"
                +" SET lock_res = GET_LOCK(`lock_name`, IFNULL(`timeout`, 5));"
                +" IF (lock_res IS NULL) THEN"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Unknown error';"
                +" ELSEIF (lock_res = 0) THEN"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Timeout expired';"
                +" END IF;"
                +" SET @dyn_sql = CONCAT('CREATE OR REPLACE VIEW `', lock_name, '` AS ', query, ';');"
                +" PREPARE stmt_dyn_view FROM @dyn_sql;"
                +" EXECUTE stmt_dyn_view;"
                +" DEALLOCATE PREPARE stmt_dyn_view;"
                +" END;";

Answer 1

So I found out what was wrong in the above stored procedure :

1. The use of DELIMITER is not required, begin with CREATE PROCEDURE
2. The use of "\n" is also not required, but it will not prompt an error if used.
3. You can't use DROP and CREATE in the same query; they should be called from separate query.

Cheers!

Answer 2

Try using this code. I hope your problem will be solved.

public final List<CoverageEntity> getCMDataTable(final String countryCode, final CoverageBean coverageBean) {

            getHibernateTemplate().execute(new HibernateCallback() {
                public (List) doInHibernate(final Session session)throws HibernateException, SQLException {
//                  Query query = getQuery(countryCode, coverageBean , session);
                    Query query = session.createSQLQuery("call ASPECT.SC_CVRG_SEARCH('99991',null,null,null,null,null,null)").addEntity(CoverageEntity.class);
                    System.out.println(ToStringBuilder.reflectionToString(query.list(),ToStringStyle.MULTI_LINE_STYLE));

                    return query.query.list();

        }
            })
           ;
            return new  ArrayList<CoverageEntity>();
}