Introduction to methods and instances for calling stored procedures in DB2 Databases

The last time we introduced the implementation process of creating a trigger for a DB2 database, this article will introduceDB2 databasePairStored ProcedureNext, let's take a look at this part.

I. Stored Procedure calls are divided into three parts

1. Connection (establish a connection with the database)

 
 

  
  
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();  
  
  
 
  
  
Connection con=DriverManager.getConnection(url,user,password); 
 
 

2. Register output parameters

 
 

  
  
cs.registerOutParameter (3, Types.INTEGER); 
 
 

3. Call the stored procedure:

 
 

  
  
CallableStatement cs = con. prepareCall ("{call store_name (parameter, parameter, parameter )}");
 
 

Ii. Call example:

 
 

  
  
Import java.net. URL;
  
  
 
  
  
Import java. SQL .*;
  
  
 
  
  
Class test2
  
  
 
  
  
{
  
  
 
  
  
Public static void main (String args [])
  
  
 
  
  
{
  
  
 
  
  
String url = "jdbc: db2: // wellhope/sample ";
  
  
 
  
  
String user = "db2admin ";
  
  
 
  
  
String password = "db2admin ";
  
  
 
  
  
Try
  
  
 
  
  
{
  
  
 
  
  
Class. forName ("COM.ibm.db2.jdbc.net. DB2Driver"). newInstance ();
  
  
 
  
  
// Establish a connection with the database
  
  
 
  
  
Connection con = DriverManager. getConnection (url, user, password );
  
  
 
  
  
CheckForWarning (con. getWarnings ());
  
  
 
  
  
DatabaseMetaData dma = con. getMetaData ();
  
  
 
  
  
String str = "This is a string ";
  
  
 
  
  
// Int hashcode = str. hashCode ();
  
  
 
  
  
// System. out. println ("Hashcode" + hashcode );
  
  
 
  
  
// Create a Statement object for executing SQL statements
  
  
 
  
  
Statement stmt = con. createStatement ();
  
  
 
  
  
// Create a CallableStatement object for executing the Stored Procedure
  
  
 
  
  
CallableStatement cs = con. prepareCall ("{call PRO_YHDL1 (?,?,?)} ");
  
  
 
  
  
// Register output parameters
  
  
 
  
  
Cs. registerOutParameter (3, Types. INTEGER );
  
  
 
  
  
Int result = 0;
  
  
 
  
  
Cs. setString (1, "123 ");
  
  
 
  
  
Cs. setString (2, "123 ");
  
  
 
  
  
Cs.exe cute ();
  
  
 
  
  
Result = cs. getInt (3 );
  
  
 
  
  
DispResultSet (result );
  
  
 
  
  
Cs. close ();
  
  
 
  
  
Con. close ();
  
  
 
  
  
}
  
  
 
  
  
Catch (SQLException ex)
  
  
 
  
  
{
  
  
 
  
  
System. out. println ("*** SQLException caught ***");
  
  
 
  
  
While (ex! = Null)
  
  
 
  
  
{
  
  
 
  
  
System. out. println ("SQLState:" + ex. getSQLState ());
  
  
 
  
  
System. out. println ("Message:" + ex. getMessage ());
  
  
 
  
  
System. out. println ("Vendor:" + ex. getErrorCode ());
  
  
 
  
  
Exex = ex. getNextException ();
  
  
 
  
  
System. out. println ("");
  
  
 
  
  
}
  
  
 
  
  
}
  
  
 
  
  
Catch (java. lang. Exception ex)
  
  
 
  
  
{
  
  
 
  
  
Ex. printStackTrace ();
  
  
 
  
  
}
  
  
 
  
  
}
 
 

Iii. Example of stored procedure:

Pro_yhdl1 is a stored procedure. Its function is to retrieve PWD from the database table YHDL:

 
 

  
  
import java.sql.*;                    
  
  
 
  
  
public class Pro_yhdl1  
  
  
 
  
  
{  
  
  
 
  
  
public static void pro_yhdl1 ( String m_id,  
  
  
 
  
  
String m_pwd,  
  
  
 
  
  
int[] result ) throws SQLException, Exception  
  
  
 
  
  
{  
  
  
 
  
  
// Get connection to the database  
  
  
 
  
  
Connection con = DriverManager.getConnection("jdbc:default:connection");  
  
  
 
  
  
PreparedStatement stmt = null;  
  
  
 
  
  
ResultSet rs = null;  
  
  
 
  
  
String sql;  
  
  
 
  
  
String m_password="";  
  
  
 
  
  
sql = "SELECT" 
  
  
 
  
  
+ "       DB2ADMIN.YHDL.PWD"  
  
  
 
  
  
+ " FROM"  
  
  
 
  
  
+ "    DB2ADMIN.YHDL"  
  
  
 
  
  
+ " WHERE"  
  
  
 
  
  
+ "    ("  
  
  
 
  
  
+ "       ( "  
  
  
 
  
  
+ "          DB2ADMIN.YHDL.ID = '"+m_id.trim()+"'"  
  
  
 
  
  
+ "       )"  
  
  
 
  
  
+ "    )";  
  
  
 
  
  
stmt = con.prepareStatement( sql );  
  
  
 
  
  
rs = stmt.executeQuery();  
  
  
 
  
  
// Access query results  
  
  
 
  
  
while (rs.next())  
  
  
 
  
  
{  
  
  
 
  
  
m_password=rs.getString(1);  
  
  
 
  
  
m_passwordm_password=m_password.trim();  
  
  
 
  
  
if (rs.wasNull())  
  
  
 
  
  
System.out.print("NULL");  
  
  
 
  
  
else  
  
  
 
  
  
System.out.print(m_password);  
  
  
 
  
  
}  
  
  
 
  
  
if(m_password.equals(m_pwd.trim()))  
  
  
 
  
  
{  
  
  
 
  
  
result[0] =1;  
  
  
 
  
  
}  
  
  
 
  
  
else  
  
  
 
  
  
{  
  
  
 
  
  
result[0] =0;  
  
  
 
  
  
}  
  
  
 
  
  
// close open resources  
  
  
 
  
  
if (rs != null) rs.close();  
  
  
 
  
  
if (stmt != null) stmt.close();  
  
  
 
  
  
if (con != null) con.close();  
  
  
 
  
  
// set return parameter  
  
  
 
  
  
//result[0] = result[0];  
  
  
 
  
  
}  
  
  
 
  
  
} 
 
 

This article describes how to call a stored procedure in a DB2 database.