db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sven Woltmann (JIRA)" <j...@apache.org>
Subject [jira] Created: (OJB-123) MySQL support for SequenceManagerStoredProcedureImpl
Date Fri, 13 Oct 2006 22:26:35 GMT
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


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

        

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message