How Do You Get the Signature of a Stored Procedure? JDBC

To invoke a stored procedure or function from a web-based application, you need to know the details of all of the parameters and result types for your stored procedures or functions. To get a signature of a stored procedure or function, you can use the DatabaseMetaData. getProcedureColumns() method, which retrieves a description of the given catalog’s stored procedure parameter and result columns. The getProcedureColumns() method signature is as follows:

When using this method, you have to be very careful to pass the right parameters. For example, the Oracle JDBC driver does not care about the catalog parameter at all and ignores it completely. On the other hand, the MySQL JDBC driver ignores the schema pattern (the schemaPattern parameter).

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 description or column description with the fields shown in Table.

Table : ResultSet Columns for getProcedureColumns()

ResultSet Columns for getProcedureColumns()

The parameters are as follows:

  • catalog: A catalog name; 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; 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; must match the procedure name as it is stored in the database.
  • columnNamePattern: A column name pattern; must match the column name as it is stored in the database.

Oracle Database Setup

Procedure created. SQL>

MySQL Database Setup

The Solution

Invoking GetSPColumns for MySQL

Figure : shows how to run the solution for the MySQL database.

Invoking GetSPColumns for MySQLInvoking GetSPColumns for MySQL

Invoking GetSPColumns for Oracle

Figure : shows how to run the solution for the Oracle database.

Invoking GetSPColumns for OracleInvoking GetSPColumns for Oracle


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

JDBC Topics