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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
