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