Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"

Revision as of 14:04, 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_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);

    }
}

[EPS Info]: 2007.11.23 01:03:23.515--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])--TopLink, version: Eclipse Persistence Services - @VERSION@ (Build @BUILD_NUMBER@)
[EPS Config]: 2007.11.23 01:03:23.531--DatabaseSessionImpl(15674464)--Connection(4877503)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
	platform=>Oracle10Platform
	user name=> "scott"
	datasource URL=> "jdbc:oracle:thin:@localhost:1521:ORCL"
))
[EPS Config]: 2007.11.23 01:03:23.875--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:ORCL
	User: SCOTT
	Database: Oracle  Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
	Driver: Oracle JDBC driver  Version: 10.2.0.1.0
[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]