[jira] Resolved: (OJB-123) MySQL support for SequenceManagerStoredProcedureImpl

     [ http://issues.apache.org/jira/browse/OJB-123?page=all ]

Armin Waibel resolved OJB-123.
------------------------------

    Fix Version/s: 1.0.5
       Resolution: Fixed

Thanks for this enhancement!

regards,
Armin

> MySQL support for SequenceManagerStoredProcedureImpl
> ----------------------------------------------------
>
>                 Key: OJB-123
>                 URL: http://issues.apache.org/jira/browse/OJB-123
>             Project: OJB
>          Issue Type: Improvement
>          Components: PB-API
>    Affects Versions: 1.0.4
>            Reporter: Sven Woltmann
>             Fix For: 1.0.5
>
>
> PROBLEM:
> ---------------
> The sequence manager documentation at http://db.apache.org/ojb/docu/guides/sequencemanager.html, section "Stored Procedures based (Oracle-style) sequencing", does not specify a table statement and a stored procedure for MySQL.
> Furthermore, when such a table and stored procedure exists, an UnsupportedOperationException "Not supported by this implementation" will be thrown.
> SOLUTION:
> ----------------
> 1.) Create the table "OJB_NEXTVAL_SEQ":
> DROP TABLE IF EXISTS OJB_NEXTVAL_SEQ;
> CREATE TABLE OJB_NEXTVAL_SEQ
> (
>     SEQ_NAME    VARCHAR(150) NOT NULL,
>     MAX_KEY     INTEGER,
>     PRIMARY KEY(SEQ_NAME)
> );
> 2.) Create the procedure "ojb_nextval_proc":
>     (It does not work with a function)
> DROP PROCEDURE IF EXISTS OJB_NEXTVAL_PROC;
> delimiter //
> CREATE PROCEDURE ojb_nextval_proc(OUT MAX_KEY_PARAM INT, IN SEQ_NAME_PARAM VARCHAR(150))
> DETERMINISTIC
> BEGIN
>     UPDATE OJB_NEXTVAL_SEQ
>     SET    MAX_KEY = MAX_KEY + 1
>     WHERE  SEQ_NAME = SEQ_NAME_PARAM;
>     SELECT MAX_KEY INTO MAX_KEY_PARAM
>     FROM OJB_NEXTVAL_SEQ
>     WHERE  SEQ_NAME = SEQ_NAME_PARAM;
> END;
> //
> delimiter ;
> 3.) Add the following method to class
>     "org.apache.ojb.broker.platforms.PlatformMySQLImpl":
> public CallableStatement prepareNextValProcedureStatement(Connection con, String procedureName, String sequenceName)
>         throws PlatformException
> {
>     try
>     {
>         String sp = "{call " + procedureName + "(?, ?)}";
>         CallableStatement cs = con.prepareCall(sp);
>         cs.registerOutParameter(1, Types.INTEGER);
>         cs.setString(2, sequenceName);
>         return cs;
>     }
>     catch (SQLException e)
>     {
>         throw new PlatformException(e);
>     }
> }
> That's it.  It works perfectly in a multithreaded or multi-server environment, because the stored procedure locks the table (MyIsam) or the appropriate row (InnoDB) of the new sequence table.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira