Hi All,
As discussed before [1], attached is a patch which implements stored procedures support for the procedures returning ResultSet. This code has been tested under Oracle, and with it you can do:
Query query = QueryFactory.newQuery(MyBean.class, "{ ?= call MY_PROCEDURE()");
MyBean bean = (MyBean) broker.getObjectByQuery(query);
// Or:
// broker.getCollectionByQuery(query);Patch is against OJB_1_0_RELEASE branch, I hope it can be applied after the 1.0.2 release s out.
Thanks, Vadim
[1] http://marc.theaimsgroup.com/?t=111039680600005&r=1
Index: src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java,v
retrieving revision 1.22.2.3
diff -u -r1.22.2.3 JdbcAccessImpl.java
--- src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java 4 Mar
2005 22:39:05 -0000 1.22.2.3
+++ src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java 21 Mar
2005 16:55:52 -0000
@@ -1,6 +1,6 @@
package org.apache.ojb.broker.accesslayer;
-/* Copyright 2003-2004 The Apache Software Foundation
+/* Copyright 2003-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
@@ -35,6 +35,7 @@
import org.apache.ojb.broker.metadata.FieldDescriptor;
import org.apache.ojb.broker.metadata.ProcedureDescriptor;
import org.apache.ojb.broker.metadata.fieldaccess.PersistentField;
+import org.apache.ojb.broker.platforms.Platform;
import org.apache.ojb.broker.query.Query;
import org.apache.ojb.broker.util.logging.Logger;
import org.apache.ojb.broker.util.logging.LoggerFactory;
@@ -83,6 +84,15 @@
}
/**
+ * Helper Platform accessor method
+ * @return Platform for the current broker connection manager.
+ */
+ private Platform getPlatform()
+ {
+ return this.broker.serviceConnectionManager().getSupportedPlatform();
+ }
+
+ /**
* performs a DELETE operation against RDBMS.
* @param cld ClassDescriptor providing mapping information.
* @param obj The object to be deleted.
@@ -305,14 +315,31 @@
String sql =
broker.serviceSqlGenerator().getPreparedSelectStatement(query, cld);
PreparedStatement stmt =
broker.serviceStatementManager().getPreparedStatement(cld, sql, scrollable);
- broker.serviceStatementManager().bindStatement(stmt, query, cld,
1);
- if (logger.isDebugEnabled())
- logger.debug("executeQuery: " + stmt);
+ ResultSet rs;
+ if (getPlatform().isCallableStatement(stmt))
+ {
+ // Query implemented as a stored procedure, which must return
a result set.
+ // Query sytax is: { ?= call PROCEDURE_NAME(?,...,?)}
+ getPlatform().registerOutResultSet((CallableStatement) stmt,
1);
+ broker.serviceStatementManager().bindStatement(stmt, query,
cld, 2);
- ResultSet rs = stmt.executeQuery();
+ if (logger.isDebugEnabled())
+ logger.debug("executeQuery: " + stmt);
- retval = new ResultSetAndStatement(
- broker.serviceConnectionManager().getSupportedPlatform(),
stmt, rs);
+ stmt.execute();
+ rs = (ResultSet) ((CallableStatement) stmt).getObject(1);
+ }
+ else
+ {
+ broker.serviceStatementManager().bindStatement(stmt, query,
cld, 1);
+
+ if (logger.isDebugEnabled())
+ logger.debug("executeQuery: " + stmt);
+
+ rs = stmt.executeQuery();
+ }
+
+ retval = new ResultSetAndStatement(getPlatform(), stmt, rs);
return retval;
}
catch (PersistenceBrokerException e)
@@ -377,12 +404,26 @@
try
{
PreparedStatement stmt = stmtMan.getPreparedStatement(cld,
sqlStatement, scrollable);
- stmtMan.bindValues(stmt, values, 1);
- ResultSet rs = stmt.executeQuery();
+
+ ResultSet rs;
+ if (getPlatform().isCallableStatement(stmt))
+ {
+ // Query implemented as a stored procedure, which must return
a result set.
+ // Query sytax is: { ?= call PROCEDURE_NAME(?,...,?)}
+ getPlatform().registerOutResultSet((CallableStatement) stmt,
1);
+ stmtMan.bindValues(stmt, values, 2);
+ stmt.execute();
+ rs = (ResultSet) ((CallableStatement) stmt).getObject(1);
+ }
+ else
+ {
+ stmtMan.bindValues(stmt, values, 1);
+ rs = stmt.executeQuery();
+ }
+
// as we return the resultset for further operations, we cannot
release the statement yet.
// that has to be done by the JdbcAccess-clients (i.e. RsIterator,
ProxyRsIterator and PkEnumeration.)
- retval = new ResultSetAndStatement(
- broker.serviceConnectionManager().getSupportedPlatform(),
stmt, rs);
+ retval = new ResultSetAndStatement(getPlatform(), stmt, rs);
return retval;
}
catch (PersistenceBrokerException e)
@@ -583,7 +624,18 @@
throw new PersistenceBrokerException("getSelectByPKStatement
returned a null statement");
}
broker.serviceStatementManager().bindSelect(stmt, oid, cld);
- rs = stmt.executeQuery();
+
+ if (getPlatform().isCallableStatement(stmt))
+ {
+ // If this is a stored procedure call, first argument is
ResultSet
+ stmt.execute();
+ rs = (ResultSet) ((CallableStatement) stmt).getObject(1);
+ }
+ else
+ {
+ rs = stmt.executeQuery();
+ }
+
// data available read object, else return null
if (rs.next())
{
@@ -655,10 +707,9 @@
PreparedStatement stmt)
throws PersistenceBrokerSQLException
{
-
// If the procedure descriptor is null or has no return values or
// if the statement is not a callable statment, then we're done.
- if ((proc == null) || (!proc.hasReturnValues()) || (!(stmt instanceof
CallableStatement)))
+ if ((proc == null) || (!proc.hasReturnValues()) ||
(!getPlatform().isCallableStatement(stmt)))
{
return;
}
Index: src/java/org/apache/ojb/broker/accesslayer/StatementManager.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/accesslayer/StatementManager.java,v
retrieving revision 1.47.2.2
diff -u -r1.47.2.2 StatementManager.java
--- src/java/org/apache/ojb/broker/accesslayer/StatementManager.java 2 Mar
2005 20:32:35 -0000 1.47.2.2
+++ src/java/org/apache/ojb/broker/accesslayer/StatementManager.java 21 Mar
2005 16:55:52 -0000
@@ -1,6 +1,6 @@
package org.apache.ojb.broker.accesslayer;
-/* Copyright 2002-2004 The Apache Software Foundation
+/* Copyright 2002-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
@@ -449,12 +449,12 @@
}
/**
- * binds the Identities Primary key values to the statement
+ * Binds the Identities Primary key values to the statement
*/
public void bindSelect(PreparedStatement stmt, Identity oid,
ClassDescriptor cld) throws SQLException
{
ValueContainer[] values = null;
- int i = 0;
+ int i = 0, j = 0;
if (cld == null)
{
@@ -462,10 +462,17 @@
}
try
{
+ if (m_platform.isCallableStatement(stmt))
+ {
+ // First argument is the result set
+ m_platform.registerOutResultSet((CallableStatement) stmt, 1);
+ j++;
+ }
+
values = getKeyValues(m_broker, cld, oid);
- for (i = 0; i < values.length; i++)
+ for (; i < values.length; i++, j++)
{
- setObjectForStatement(stmt, i + 1, values[i].getValue(),
values[i].getJdbcType().getType());
+ setObjectForStatement(stmt, j + 1, values[i].getValue(),
values[i].getJdbcType().getType());
}
}
catch (SQLException e)
@@ -699,7 +706,7 @@
// Figure out if we are using a callable statement. If we are, then we
// will need to register one or more output parameters.
CallableStatement callable = null;
- if (stmt instanceof CallableStatement)
+ if (m_platform.isCallableStatement(stmt))
{
callable = (CallableStatement) stmt;
}
Index: src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java,v
retrieving revision 1.22.2.1
diff -u -r1.22.2.1 StatementsForClassImpl.java
--- src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
13 Jan 2005 15:50:05 -0000 1.22.2.1
+++ src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
21 Mar 2005 16:55:52 -0000
@@ -44,6 +44,8 @@
{
private Logger log = LoggerFactory.getLogger(StatementsForClassImpl.class);
+ private static String CALL = "{ ?= call";
+
/**
* sets the escape processing mode
*/
@@ -175,7 +177,8 @@
PreparedStatement stmt = null;
try
{
- stmt = prepareStatement(con, sql, scrollable);
+ // FIXME: Improve check for stored procedure call syntax
+ stmt = prepareStatement(con, sql, scrollable,
!sql.toLowerCase().startsWith(CALL));
}
catch (java.sql.SQLException ex)
{
Index: src/java/org/apache/ojb/broker/platforms/Platform.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/platforms/Platform.java,v
retrieving revision 1.24
diff -u -r1.24 Platform.java
--- src/java/org/apache/ojb/broker/platforms/Platform.java 4 Apr 2004
23:53:35 -0000 1.24
+++ src/java/org/apache/ojb/broker/platforms/Platform.java 21 Mar 2005
16:55:53 -0000
@@ -1,6 +1,6 @@
package org.apache.ojb.broker.platforms;
-/* Copyright 2002-2004 The Apache Software Foundation
+/* Copyright 2002-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
@@ -224,5 +224,20 @@
* @return
*/
public String getEscapeClause(LikeCriteria aCriteria);
-
+
+ /**
+ * Determines whether statement is [EMAIL PROTECTED] CallableStatement} or
not.
+ * @param stmt the statement
+ * @return true if statement is [EMAIL PROTECTED] CallableStatement}.
+ */
+ public boolean isCallableStatement(PreparedStatement stmt);
+
+ /**
+ * Register call argument at <code>position</code> as returning
+ * a [EMAIL PROTECTED] ResultSet} value.
+ * @param stmt the statement
+ * @param position argument position
+ */
+ public void registerOutResultSet(CallableStatement stmt, int position)
+ throws SQLException;
}
Index: src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java,v
retrieving revision 1.27.2.1
diff -u -r1.27.2.1 PlatformDefaultImpl.java
--- src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java 19 Aug
2004 08:12:38 -0000 1.27.2.1
+++ src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java 21 Mar
2005 16:55:53 -0000
@@ -1,6 +1,6 @@
package org.apache.ojb.broker.platforms;
-/* Copyright 2002-2004 The Apache Software Foundation
+/* Copyright 2002-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
@@ -405,4 +405,20 @@
return "";
}
}
+
+ /**
+ * @see
org.apache.ojb.broker.platforms.Platform#isCallableStatement(java.sql.PreparedStatement)
+ */
+ public boolean isCallableStatement(PreparedStatement stmt) {
+ return stmt instanceof CallableStatement;
+ }
+
+ /**
+ * @see
org.apache.ojb.broker.platforms.Platform#registerOutResultSet(java.sql.CallableStatement,
int)
+ */
+ public void registerOutResultSet(CallableStatement stmt, int position)
+ throws SQLException
+ {
+ stmt.registerOutParameter(position, Types.OTHER);
+ }
}
Index: src/java/org/apache/ojb/broker/platforms/PlatformHsqldbImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformHsqldbImpl.java,v
retrieving revision 1.7
diff -u -r1.7 PlatformHsqldbImpl.java
--- src/java/org/apache/ojb/broker/platforms/PlatformHsqldbImpl.java 4 Apr
2004 23:53:35 -0000 1.7
+++ src/java/org/apache/ojb/broker/platforms/PlatformHsqldbImpl.java 21 Mar
2005 16:55:53 -0000
@@ -1,5 +1,6 @@
package org.apache.ojb.broker.platforms;
+import java.sql.PreparedStatement;
/* Copyright 2002-2004 The Apache Software Foundation
*
@@ -16,7 +17,6 @@
* limitations under the License.
*/
-
/**
* This class extends <code>PlatformDefaultImpl</code> and defines specific
* behavior for the Hsqldb platform.
@@ -65,5 +65,10 @@
return true;
}
-
+ /**
+ * HSQLDB does not implement CallableStatement.
+ */
+ public boolean isCallableStatement(PreparedStatement stmt) {
+ return false;
+ }
}
Index: src/java/org/apache/ojb/broker/platforms/PlatformOracleImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformOracleImpl.java,v
retrieving revision 1.20.2.2
diff -u -r1.20.2.2 PlatformOracleImpl.java
--- src/java/org/apache/ojb/broker/platforms/PlatformOracleImpl.java 2 Mar
2005 16:25:28 -0000 1.20.2.2
+++ src/java/org/apache/ojb/broker/platforms/PlatformOracleImpl.java 21 Mar
2005 16:55:53 -0000
@@ -1,6 +1,6 @@
package org.apache.ojb.broker.platforms;
-/* Copyright 2002-2004 The Apache Software Foundation
+/* Copyright 2002-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
@@ -254,4 +254,13 @@
return false;
}
+ /**
+ * @see
org.apache.ojb.broker.platforms.Platform#registerOutResultSet(java.sql.CallableStatement,
int)
+ */
+ public void registerOutResultSet(CallableStatement stmt, int position)
+ throws SQLException
+ {
+ // oracle.jdbc.OracleTypes.CURSOR
+ stmt.registerOutParameter(position, -10);
+ }
}--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
