Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"
(→Mixing IN and OUT arguments) |
(→Mixing IN and OUT arguments) |
||
Line 123: | Line 123: | ||
x :<font color="#808030"><nowiki>=</nowiki></font> <font color="#008c00">33</font><font color="#808030"><nowiki>;</nowiki></font> | x :<font color="#808030"><nowiki>=</nowiki></font> <font color="#008c00">33</font><font color="#808030"><nowiki>;</nowiki></font> | ||
'''<font color="#800000">end</font>'''<font color="#808030"><nowiki>;</nowiki></font> | '''<font color="#800000">end</font>'''<font color="#808030"><nowiki>;</nowiki></font> | ||
+ | </font> | ||
+ | |||
+ | The EclipseLink code: | ||
+ | <font color="#000000"> | ||
+ | ... | ||
+ | PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall(); | ||
+ | call.setProcedureName(<font color="#2a00ff">"two_arg_in_out"</font>); | ||
+ | call.addNamedOutputArgument(<font color="#2a00ff">"X"</font>, BinaryInteger); | ||
+ | call.addNamedArgument(<font color="#2a00ff">"Y"</font>, JDBCTypes.VARCHAR_TYPE, 40); | ||
+ | DataReadQuery query = '''<font color="#7f0055">new</font>''' DataReadQuery(); | ||
+ | query.setCall(call); | ||
+ | query.addArgument(<font color="#2a00ff">"Y"</font>); | ||
+ | '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector(); | ||
+ | queryArgs.add(<font color="#2a00ff">"testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds"</font>); | ||
+ | '''<font color="#7f0055">boolean</font>''' worked = '''<font color="#7f0055">false</font>'''<nowiki>; | ||
+ | </nowiki>'''<font color="#7f0055">String</font>''' msg = '''<font color="#7f0055">null</font>'''<nowiki>; | ||
+ | </nowiki>'''<font color="#7f0055">Vector</font>''' results = ('''<font color="#7f0055">Vector</font>''')s.executeQuery(query, queryArgs); | ||
+ | DatabaseRecord record = (DatabaseRecord)results.get(0); | ||
+ | '''<font color="#7f0055">BigDecimal</font>''' x = ('''<font color="#7f0055">BigDecimal</font>''')record.get(<font color="#2a00ff">"X"</font>); | ||
+ | '''<font color="#7f0055">if</font>''' (x.intValue() != 33) { | ||
+ | '''<font color="#7f0055">System</font>'''.out.println(<font color="#2a00ff">"wrong x value"</font>); | ||
+ | } | ||
</font> | </font> | ||
Revision as of 16:21, 23 November 2007
{available as of EclipseLink 1.0M2}
How to handle nonJDBC arguments for Oracle Stored Procedures
The standard way of handling a Stored Procedure is to build an instance of org.eclipse.persistence.queries.StoredProcedureCall. However, the arguments must be compatible with the JDBC specification.
To handle nonJDBC arguments (e.g. BOOLEAN, PLS_INTEGER, PL/SQL record, etc.) a new sub-class has been created: org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall:
For the target procedure:
procedure bool_in_test(x in boolean)
the EclipseLink code would be:
package test; // javase imports import java.util.Vector; // EclipseLink imports import org.eclipse.persistence.internal.helper.NonSynchronizedVector; import org.eclipse.persistence.logging.SessionLog; import org.eclipse.persistence.platform.database.oracle.Oracle10Platform; import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall; import org.eclipse.persistence.queries.DataModifyQuery; import org.eclipse.persistence.sessions.DatabaseLogin; import org.eclipse.persistence.sessions.DatabaseSession; import org.eclipse.persistence.sessions.Project; import org.eclipse.persistence.sessions.Session; import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.PLSQLBoolean; public class TestClass { public static String DATABASE_USERNAME = "scott"; public static String DATABASE_PASSWORD = "tiger"; public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL"; public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver"; public static void main(String[] args) { Project project = new Project(); DatabaseLogin login = new DatabaseLogin(); login.setUserName(DATABASE_USERNAME); login.setPassword(DATABASE_PASSWORD); login.setConnectionString(DATABASE_URL); login.setDriverClassName(DATABASE_DRIVER); login.setDatasourcePlatform(new Oracle10Platform()); project.setDatasourceLogin(login); Session s = project.createDatabaseSession(); s.setLogLevel(SessionLog.FINE); ((DatabaseSession)s).login(); PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("bool_in_test"); call.addNamedArgument("X", PLSQLBoolean); DataModifyQuery query = new DataModifyQuery(); query.addArgument("X"); query.setCall(call); Vector queryArgs = new NonSynchronizedVector(); queryArgs.add(Integer.valueOf(1)); s.executeQuery(query, queryArgs); } }
The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block:
... [EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful [EPS Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); BEGIN bool_in_test(X=>X_TARGET); END; bind => [:1 => 1]
Mixing regular JDBC arguments with nonJDBC arguments
A Stored Procedure may have a mix of regular and nonJDBC arguments. EclipseLink supports scenarios where all the arguments are regular JDBC types; however, when at least one argument is a nonJDBC type, then the PLSQLStoredProcedureCall class must be used. Additionally, because the target procedure is being invoked via an anonymous PL/SQL block, some additional information may be required for the JDBC type (length, scale or precision):
procedure two_arg_test(x in boolean, y in varchar)
The EclipseLink code:
... import org.eclipse.persistence.platform.database.jdbc.JDBCTypes; ... PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("two_arg_test"); call.addNamedArgument("X", PLSQLBoolean); call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40); DataModifyQuery query = new DataModifyQuery(); query.addArgument("X"); query.addArgument("Y"); query.setCall(call); Vector queryArgs = new NonSynchronizedVector(); queryArgs.add(Integer.valueOf(0)); queryArgs.add("test"); boolean worked = false; String msg = null; s.executeQuery(query, queryArgs);
The EclipseLink log:
[EPS Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); Y_TARGET VARCHAR(40) := :2; BEGIN two_arg_test(X=>X_TARGET, Y=>Y_TARGET); END; bind => [:1 => 0, :2 => test]
Mixing IN and OUT arguments
The Stored Procedure may contain both IN and OUT arguments:
procedure two_arg_in_out(x out binary_integer, y in varchar) as begin x := 33; end;
The EclipseLink code:
... PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("two_arg_in_out"); call.addNamedOutputArgument("X", BinaryInteger); call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40); DataReadQuery query = new DataReadQuery(); query.setCall(call); query.addArgument("Y"); Vector queryArgs = new NonSynchronizedVector(); queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds"); boolean worked = false; String msg = null; Vector results = (Vector)s.executeQuery(query, queryArgs); DatabaseRecord record = (DatabaseRecord)results.get(0); BigDecimal x = (BigDecimal)record.get("X"); if (x.intValue() != 33) { System.out.println("wrong x value"); }
[EPS Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE Y_TARGET VARCHAR(40) := :1; X_TARGET BINARY_INTEGER; BEGIN two_arg_in_out(X=>X_TARGET, Y=>Y_TARGET); :2 := X_TARGET; END; bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]