StoredProcedureReference < DataCaster

Stored Procedures

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. In our Database Java/Jython methods can be used as stored procedures, so we can have any java/pyhton method stored in the schema as a stored procedure. This improves the ability to embed business logic in the Database, and helps to avoid duplicating the logic in each program that accesses the data. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters. Three types of parameters are supported:
a) IN patrameters are used to send data to the methods, any change to these values in the method is not available for the user.
b) OUT parameters are used to retrieve data from the methods, no data can be sent through these parameters. If the value is set it's neglected, and the value set in the procedure is passed back to the user.
c) INOUT parameters are used to send data as well as retrive the changed values.

SQL Statements for Creating a Stored Procedure

Create procedure statements that are used to add procedures to the Schema has the following syntax:

create procedure [[[parameter type] [parametername datatype]] ....] '<Filename/ClassName>' 'Method name'

"Procedure Name" -> Name to be associated to this procedure in the schema. This has to be unique in the schema. Procedure name is used to access the stored procedure through the call procedure statement.

"[[[in/out/inout] [parametername datatype]] ....]" -> List of parameter defination , this is optional. The defination, when present, should have atleast one parameter name and it's sql Datatype. The parameter name need not be the same as the parameter name specified in the external method, but should be unique for the procedure. Parameter type is optional , all parameters are treated as 'IN' parameter by default .
Three types of parameters are supported:

a) IN patrameters are used to send data to the methods, any change to these values in the method is not available for the user.
b) OUT parameters are used to retrieve data from the methods, no data can be sent through these parameters. If the value is set,it is neglected, and the value set in the procedure is passed back to the user.
c) INOUT parameters are used to send data as well as retrive the changed values.

Note: For out and inout parameters the corresponding method should accept a array of size one.

'Filename/Classname' -> The actual file which has the method specified by the 'MethodName'. In case of Java Methods it should point to the Java Class ( without the .class extension , in case of Jython Method this should point to the file containing the method specified. In either case this can be an absolute path or a relativepath. If relative path is used, it is assumed to be relative to 'procedure.dir' path in the dbprefs.xml, if not specified it's assumed to be relative to current working directory.

Note: For Jython methods the filename should end in .py, otherwise it's assumed to be a Java method.

Using Java Methods as stored procedures:

1) create procedure testProcedure in id int in str varchar(40) inout outVal int 'com.applibase.test.jdbc.ProcedureFunctions' 'setInt'

Explaination: In this example 'setInt' method in com.applibase.test.jdbc.ProcedureFunctions class is registered as a stored Procedure named 'testProcedure' in the current schema. Three variables are defined: id, str and outVal, id and str are defined as in parameters while outVal is defined as an INOUT parameter.

2) create procedure testProcedure 'com.applibase.test.jdbc.ProcedureFunctions' 'setInt'

Explaination: In this example 'setInt' method in com.applibase.test.jdbc.ProcedureFunctions class is registered as a stored Procedure named 'testProcedure' in the current schema. No parameters are specified during the creation, so a method appropriate to the number and type of parameters passed while calling the stored proceure is used.

Using Jython Methods as Stored Procedures:

Jython methods are used eactly like java methods are, The file specified is loaded in a interpreter and the mentiloned method is called.

1.) create procedure testProc in id int in str varchar(40) in datearg date '../TestDataCore/src/com/applibase/test/db/testPathPy.py' 'testAbsolutePath'

Explaination: In this example 'testAbsolutePath' method in testpathPy.py module is registered as a stored Procedure in the current schema.

SQL Statement for calling stored Procedures

Call statemetns are used to execute the stored procedures in the current schema.

[?=]call ( [parameter],[parameter]....);

procedureName specifies the name of the procdure. parameter specifies the value of the parameter.

A procedure can have zero or more parameters. It may or may not return a value as indicateed by optional '?=' at the beginiing of the syntax. If a parameter is a In or Inout parameter it can be a literal or a parameter marker or an uservariable. If the parameter is an Out parameter it has to be a userVariable or a parameter marker because the value is unknown. in parameters can be omitted from procedure calls. If the procedure does not have any arguments it may be called as follow:
call procedureName();
Or through JDBC we can use the Escape syntax {call procedureName}

Note: UserVariables are used wih variable name prefixed with '@'. Ex: @EmployeeName.

Examples:
1.) create procedure addEmployee in userName varchar(256) in employeeId int 'EmployeeOps' 'addEmployee';
call addEmployee( 'Employee_name', ?)

Explaination: Here we are sending a String Literal for userName and a parameter marker for employeeId.

2.) create procedure addEmployee in userName varchar(256) in employeeId int 'EmployeeOps' 'addEmployee';
call addEmployee(?,?);

Explaination: Here we are not sending any literal values, instead we assign propervalues before calling through set...() methods of CallableStatements.

Creating Stored Procedure:

Stored rocedures can be created in two ways :

Through SQl Query "Create procedure".
Through procedures.xml file. ( Read the comments in the example procedures.xml file for explaination. )

Accessing Stored Procedures through API:

Using DbStatements to call stored Procedures:

We cal call stored procedures through the DbStatements as shown in the example. Since the DbStatements dont support substituting values for parameter markers, only literals and userVariables can be used to pass values to the procedures. Return values and out variables cannot be accessed.

Example: DbStatement stmt = db.createStatement("call employeeAdd('newEmp_name', @userID); stmt.execute();

Using DbPrepareedSatements to call storedProcedures:

DbPreparedStatements support parameter markers and hence can be used for in/inout parameters but out parameters are not supported.

Example: DbPreparedStatement stmt = db.cretePreparedStatement("call employeeAdd(?,?)"); stmt.setString(1,"newEmployeeName); stmt.setInt(2,10); stmt.execute();

Using DbCallableStatement to call stored procedures:

DbCallableStatements are the prefered way to execute storedprocedures as they support parameter markers and out variables. Before executing the statemtent we should register the required out variables through "registerOutParameter()" method in the DbCallableStatement insterface. To access the new values of out/inout variables after the procedure execution "getValue()" method should be used.

Example: DbStatement stmt = db.createStatement("create procedure testProcedure " +
"in id int in str varchar(40) " +
"inout outVal int 'com.applibase.test.jdbc.ProcedureFunctions' " +
"'setInt'");
stmt.execute();
DbCallableStatement cstmt = db.createCallableStatement("call testProcedure(?,?,?)");
cstmt.setVariable(1,new Integer(10));
cstmt.setVariable(2,new String("theju"));
cstmt.setVariable(3,new Integer(20));
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.execute();
Object val = (Integer)cstmt.getValue(3);

Accessing Stored Procedures through JDBC:

Accessing through Statement:

Statement interface does not provide method to set and get values to parameter markers and hence parameter markers cannot be used. onlu literals and userVariables are allowed. Out parameters cannot be accessed for the same reason.

Example: Statement stmt = con.createStatement(); stmt.execute("call employeeAdd( 'newEmp_name', @userID)");

Accessing through PreparedStatements:

PreparedStatements support setting values to parameter markers and hence they can be used while calling stored procedures. But they donot support retriving values of out parameters.

Example: PreparedStatement stmt = con.prepareStatement("call employeeAdd( ?,?)"); stmt.setString(1,"newEmpName"); stmt.setInt(2,10); stmt.execute();

Accessing through CallableStatements:

CallableStatements support both setting values to parameter markers and fetching new values of parameters that are registered as out parameters , hence are the most suited to call stored procedures.

Example: con.createStatement().executeUpdate("create procedure " +
"testProcedure in id int in str varchar(40) " +
"out outVal varchar(40) " +
"'com.applibase.test.jdbc.ProcedureFunctions' 'testProcedure'");

CallableStatement cstmt = con.prepareCall("{call testProcedure(?,?,?)}");
cstmt.setInt(1,10);
cstmt.setString(2,"theju");
cstmt.setString(3,"old String");
cstmt.registerOutParameter(3,Types.VARCHAR);
cstmt.executeUpdate();

String returnString = cstmt.getString(3);