What Is the Signature of a Stored Procedure? JDBC

How can a client investigate the parameters to send into and receive from a database stored procedure? Understanding the signature of a stored procedure is important for SQL adapter development in order to obtain the signature information at runtime. A signature is the name of the procedure and the name and type of its arguments. The Database MetaData interface provides a method,
getProcedureColumns(), which returns detailed metadata information on
arguments(columns)of stored procedures. This section provides a few tables and stored procedures that will help you understand how best to use the
getProcedureColumns() method.

The MySQL database does not support stored procedures yet, but it will in future releases(starting with MySQL 5.0.1). We’ll focus here on the Oracle database. We’ll define a table, called EMPLOYEE, and a stored procedure, raiseSalary, to retrieve the salary of a specific department as a percentage.

Oracle Database Setup

SQL> create table EMPLOYEE (
2 badgeNumber number(4) not null,
3 empName varchar2(40) not null,
4 jobTitle varchar2(30),
5 manager number(4),
6 hireDate date,
7 salary number(7,2),
8 deptNumber number(2)
9 );
Table created.
SQL> describe employee;
Name Null? Type
---------------- -------- -------------
BADGENUMBER NOT NULL NUMBER(4)
EMPNAME NOT NULL VARCHAR2(40)
JOB TITLE VARCHAR2(30)
MANAGER NUMBER(4)
HIREDATE DATE
SALARY NUMBER(7,2)
DEPTNUMBER NUMBER(2)
Next, let’s insert some records into an EMPLOYEE table:
SQL> insert into EMPLOYEE(badgeNumber , empName, jobTitle, hireDate,
2 salary, deptNumber)
3 values(1111, 'Alex Smith', 'Manager', '12-JAN-1981', 78000.00, 23);
SQL> insert into EMPLOYEE(badgeNumber , empName, jobTitle, manager,
2 hireDate, salary, deptNumber)
3 values(2222, 'Jane Taylor', 'Engineer', 1111, '12-DEC-1988', 65000.00, 23);
SQL> insert into EMPLOYEE(badgeNumber , empName, jobTitle, manager,
2 hireDate, salary, deptNumber)
3 values(3333, 'Art Karpov', 'Engineer', 1111, '12-DEC-1978', 80000.00, 23);
SQL> insert into EMPLOYEE(badgeNumber , empName, jobTitle, manager,
2 hireDate, salary, deptNumber)
3 values(4444, 'Bob Price', 'Engineer', 1111, '12-DEC-1979', 70000.00, 55);
SQL> commit;
Commit complete.
SQL> select badgeNumber, empName, salary, deptNumber from employee;
BADGENUMBER EMPNAME SALARY DEPTNUMBER
----------- --------------- ---------- ----------
1111 Alex Smith 78000 23
2222 Jane Taylor 65000 23
3333 Art Karpov 80000 23
4444 Bob Price 70000 55
Next, let’s create a stored procedure called raiseSalary:
SQL> create procedure raiseSalary(deptNumber_Param number,
2 percentage_Param number DEFAULT 0.20) is
3 cursor empCursor (dept_number number) is
4 select salary from EMPLOYEE where deptNumber = dept_number
5 for update of salary;
6
7 empsal number(8);
8 begin
9 open empCursor(deptNumber_Param);
10 loop
11 fetch empCursor into empsal;
12 exit when empCursor%NOTFOUND;
13 update EMPLOYEE set salary = empsal * ((100 + percentage_Param)/100)
14 where current of empCursor;
15 end loop;
16 close empCursor;
17 commit;
18 end raisesalary;
19 /
Procedure created.
SQL> describe raiseSalary;
PROCEDURE raiseSalary
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DEPTNUMBER_PARAM NUMBER IN
PERCENTAGE_PARAM NUMBER IN DEFAULT

Invoking/Executing raiseSalary As a Stored Procedure

In order to raise the salary of all employees in department number 23, run raiseSalary as follows:

SQL> execute raiseSalary(23, 10);
PL/SQL procedure successfully completed.
SQL> select badgeNumber, empName, salary, deptNumber from employee;
BADGENUMBER EMPNAME SALARY DEPTNUMBER
----------- --------------- ---------- ----------
1111 Alex Smith 85800 23
2222 Jane Taylor 71500 23
3333 Art Karpov 88000 23
4444 Bob Price 70000 55

The Solution: getStoredProcedureSignature()

The getStoredProcedureSignature() method retrieves the signature of a stored procedure and returns the metadata as an XML object, serialized as a String object for efficiency purposes.

Here is the signature of getStoredProcedureSignature():

/**
* Retrieves a description of the given catalog's stored
* procedure parameter and result columns. This method
* calls getProcedureColumns() to get the signature
* and then transforms the result set into XML.
*
* @param conn the Connection object
* @param catalog a catalog.
* @param schemaPattern a schema pattern.
* @param procedureNamePattern name of a stored procedure
* @param columnNamePattern a column name pattern.
* @return an XML.
* @throws Exception Failed to get the stored procedure's signature.
*/
public static String getStoredProcedureSignature(
java.sql.Connection conn,
String catalog,
String schemaPattern,
String procedureNamePattern,
String columnNamePattern)
throws Exception {...}

Oracle9i Considerations for the getProcedureColumns() Method

Inside our solution, getStoredProcedureSignature(), we call
getProcedureColumns(), to which we have to give special consideration. According
to Oracle, the methods getProcedures() and getProcedureColumns()(defined in the
DatabaseMetaData interface) treat the catalog, schemaPattern,
columnNamePattern, and procedureNamePattern parameters in the same way. In the Oracle definition of these methods, the parameters are treated differently. Table is taken from the Oracle 9i documentation.
The getProcedureColumns() Method According to Oracle

Oracle9i Considerations for the getProcedureColumns() Method

A Weakness for the JDBC Metadata

Before we delve into the signature of this method, let’s look at a weakness of the getProcedureColumns() method: inside getStoredProcedureSignature(), we use the method getProcedureColumns() in the interface DatabaseMetaData to obtain a stored procedure’s metadata. The exact usage is described in the code that follows. You should note that this method (getProcedureColumns()) can only discover parameter values. Some databases (such as Sybase and Microsoft’s SQL Server 2000) can return multiple result sets without using any arguments. For databases where a returning ResultSet is created simply by executing a SQL SELECT statement within a stored procedure (thus not sending the return ResultSet to the client application via a declared parameter), the real return value of the stored procedure cannot be detected. This is a weakness for the JDBC metadata.

Signature of getProcedureColumns()

The getProcedureColumns() method’s signature is defined in JDK1.4.2 as follows:
public ResultSet getProcedureColumns
(String catalog,
String schemaPattern,
String procedureNamePattern, // in Oracle it must be uppercase
String columnNamePattern)
throws SQLException

This method retrieves a description of the given catalog’s stored procedure parameter and result columns. Only descriptions matching the schema, procedure, and parameter name criteria are returned. They are ordered by PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value, if any, is first. Next are the parameter descriptions in call order. The column descriptions follow in column number order.

Each row in the ResultSet is a parameter or column description with the fields shown in

Parameter or Column Description Fields

Parameter or Column Description Fields

Parameter or Column Description Fields

Note Some databases may not return the column descriptions for a procedure. Additional columns beyond REMARKS can be defined by the database.

The parameters for this method are as follows:

  • catalog: A catalog name; it must match the catalog name as it is stored in the database. "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search.
  • schemaPattern: A schema name pattern; it must match the schema name as it is stored in the database. "" retrieves those without a schema; null means that the schema name should not be used to narrow the search.
  • procedureNamePattern: A procedure name pattern; it must match the procedure name as it is stored in the database.
  • columnNamePattern: A column name pattern; it must match the column name as it is stored in the database.

This method returns a ResultSet in which each row describes a stored procedure parameter or column. If a database access error occurs, it throws a SQLException.

The Complete Solution: getStoredProcedureSignature()

You need to be careful in invoking the DatabaseMetaData.getProcedureColumns() method. First, make sure that you pass actual parameter values for catalogs and schemas rather than passing empty and null values (this will speed up your method call). Second, be aware of overloaded stored procedures (each database vendor might handle overloaded stored procedures differently—refer to the vendor’s database documentation).

/**
* Retrieves a description of the given catalog's stored
* procedure parameter and result columns.
*
* @param conn the Connection object
* @param catalog a catalog.
* @param schemaPattern a schema pattern.
* @param procedureNamePattern name of a stored procedure
* @param columnNamePattern a column name pattern.
* @return XML.
* @throws Exception Failed to get the stored procedure's signature.
*/
public static String getStoredProcedureSignature(
java.sql.Connection conn,
String catalog,
String schemaPattern,
String procedureNamePattern,
String columnNamePattern) throws Exception {
// Get DatabaseMetaData
DatabaseMetaData dbMetaData = conn.getMetaData();
if (dbMetaData == null) {
return null;
}
ResultSet rs = dbMetaData.getProcedureColumns(catalog,
schemaPattern,
procedureNamePattern,
columnNamePattern);
StringBuffer sb = new StringBuffer("<?xml version='1.0'>");
sb.append("<stored_procedures_signature>");
while(rs.next()) {
// get stored procedure metadata
String procedureCatalog = rs.getString(1);
String procedureSchema = rs.getString(2);
String procedureName = rs.getString(3);
String columnName = rs.getString(4);
short columnReturn = rs.getShort(5);
int columnDataType = rs.getInt(6);
String columnReturnTypeName = rs.getString(7);
int columnPrecision = rs.getInt(8);
int columnByteLength = rs.getInt(9);
short columnScale = rs.getShort(10);
short columnRadix = rs.getShort(11);
short columnNullable = rs.getShort(12);
String columnRemarks = rs.getString(13);
sb.append("<storedProcedure name="");
sb.append(procedureName);
sb.append("">");
appendXMLTag(sb, "catalog", procedureCatalog);
appendXMLTag(sb, "schema", procedureSchema);
appendXMLTag(sb, "columnName", columnName);
appendXMLTag(sb, "columnReturn", getColumnReturn(columnReturn));
appendXMLTag(sb, "columnDataType", columnDataType);
appendXMLTag(sb, "columnReturnTypeName", columnReturnTypeName);
appendXMLTag(sb, "columnPrecision", columnPrecision);
appendXMLTag(sb, "columnByteLength", columnByteLength);
appendXMLTag(sb, "columnScale", columnScale);
appendXMLTag(sb, "columnRadix", columnRadix);
appendXMLTag(sb, "columnNullable", columnNullable);
appendXMLTag(sb, "columnRemarks", columnRemarks);
sb.append("</storedProcedure>");
}
sb.append("</stored_procedures_signature>");
// Close database resources
rs.close();
//conn.close();
return sb.toString();
}

getColumnReturn():

private static String getColumnReturn(short columnReturn) {
switch(columnReturn) {
case DatabaseMetaData.procedureColumnIn:
return "In";
case DatabaseMetaData.procedureColumnOut:
return "Out";
case DatabaseMetaData.procedureColumnInOut:
return "In/Out";
case DatabaseMetaData.procedureColumnReturn:
return "return value";
case DatabaseMetaData.procedureColumnResult:
return "return ResultSet";
default:
return "unknown";
}
}

appendXMLTag():

private static void appendXMLTag(StringBuffer buffer,
String tagName,
int value) {
buffer.append("<");
buffer.append(tagName);
buffer.append(">");
buffer.append(value);
buffer.append("</");
buffer.append(tagName);
buffer.append(">");
}
private static void appendXMLTag(StringBuffer buffer,
String tagName,
String value) {
buffer.append("<");
buffer.append(tagName);
buffer.append(">");
buffer.append(value);
buffer.append("</");
buffer.append(tagName);
buffer.append(">");
}
}

Client Program 1

String signature = DatabaseMetaDataTool.getStoredProcedureSignature
(conn,
"",
"OCTOPUS", // user
"RAISESALARY", // stored procedure name
"%"); // all columns
System.out.println(signature);

Output of Client Program 1

<?xml version='1.0'>
<stored_procedures_signature>
<storedProcedure name="RAISESALARY">
<catalog>null</catalog>
<schema>OCTOPUS</schema>
<columnName>DEPTNUMBERPARAM</columnName>
<columnReturn>In</columnReturn>
<columnDataType>3</columnDataType>
<columnReturnTypeName>NUMBER</columnReturnTypeName>
<columnPrecision>22</columnPrecision>
<columnByteLength>22</columnByteLength>
<columnScale>0</columnScale>
<columnRadix>10</columnRadix>
<columnNullable>1</columnNullable>
<columnRemarks>null</columnRemarks>
</storedProcedure>
<storedProcedure name="RAISESALARY">
<catalog>null</catalog>
<schema>OCTOPUS</schema>
<columnName>PERCENTAGE</columnName>
<columnReturn>In</columnReturn>
<columnDataType>3</columnDataType>
<columnReturnTypeName>NUMBER</columnReturnTypeName>
<columnPrecision>22</columnPrecision>
<columnByteLength>22</columnByteLength>
<columnScale>0</columnScale>
<columnRadix>10</columnRadix>
<columnNullable>1</columnNullable>
<columnRemarks>null</columnRemarks>
</storedProcedure>
</stored_procedures_signature>

Client Program 2

For this client program, let’s define another stored procedure (call it showUsers, which lists all of the users) that does not have any arguments. Note that the all_users table holds all of the
users in the Oracle database.

SQL> describe all_users;
Name Null? Type
------------------------------------- -------- ------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
SQL>
SQL> CREATE OR REPLACE PROCEDURE showUsers AS
2 BEGIN
3 for A_USER in ( SELECT * from all_users ) LOOP
4 -- do something
5 DBMS_OUTPUT.PUT_LINE('UserName: '|| A_USER.UserName);
6 end loop;
7 END showUsers;
8 /
Procedure created.
SQL> describe showusers;
PROCEDURE showusers
SQL> set serveroutput on
SQL> exec showUsers;
UserName: SYS
UserName: SYSTEM
UserName: OUTLN
UserName: DBSNMP
...
UserName: QS_CBADM
UserName: QS_CB
UserName: QS_CS
UserName: SCOTT
UserName: OCTOPUS
PL/SQL procedure successfully completed.
String signature = DatabaseMetaDataTool.getStoredProcedureSignature
(conn,
"",
"OCTOPUS", // user
"SHOWUSERS", // stored procedure name
"%"); // all columns
System.out.println(signature);

Output of Client Program 2

As you can observe, there are no signature definitions for the showUsers stored procedure because showUsers has no arguments whatsoever.

<?xml version='1.0'>
<stored_procedures_signature>
</stored_procedures_signature>


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics