GET_MESSAGE stored procedure

Example

Example 1

The following example shows an XML input document that is returned by the xml_output parameter when the stored procedure is running in Complete mode.

  <?xml version="1.0" encoding="UTF-8" ?> 
  <plist version="1.0">
  <dict>
     <key>Document Type Name</key> 
     <string>Data Server Message Input</string> 
     <key>Document Type Major Version</key> 
     <integer>1</integer> 
     <key>Document Type Minor Version</key> 
     <integer>0</integer> 
     <key>Document Locale</key> 
     <string>en_US</string> 
     <key>Required Parameters</key>  
     <dict>
        <key>Display Name</key> 
        <string>Required Parameters</string> 
        <key>SQLCODE</key> 
        <dict>
           <key>Display Name</key> 
           <string>SQLCODE</string> 
           <key>Value</key> 
           <integer /> 
           <key>Hint</key> 
           <string /> 
        </dict>
        <key>Hint</key> 
        <string /> 
     </dict>
     <key>Optional Parameters</key> 
     <dict>
        <key>Display Name</key> 
        <string>Optional Parameters</string> 
        <key>Message Tokens</key> 
        <dict>
           <key>Display Name</key> 
           <string>Message Tokens</string> 
           <key>Value</key> 
           <array>
              <string /> 
           </array>
           <key>Hint</key> 
           <string /> 
        </dict>
        <key>Hint</key> 
        <string /> 
     </dict>
  </dict>
  </plist>

Example 2

The following example shows a complete sample of an XML input document for the GET_MESSAGE stored procedure.

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key>
   <string>Data Server Message Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Document Locale</key><string>en_US</string>
   <key>Required Parameters</key>
   <dict>
      <key>SQLCODE</key>
      <dict>
         <key>Value</key><integer>-104</integer>
      </dict>
   </dict>
   <key>Optional Parameters</key>
   <dict>
      <key>Message Tokens</key>
      <dict>
         <key>Value</key>
         <array>
            <string>X</string>
            <string>( . LIKE AS</string>
         </array>
      </dict>
   </dict>
</dict>
</plist>

Example 3

The following example shows a complete sample of an XML output document for the GET_MESSAGE stored procedure. The short message text for an SQLCODE will be encapsulated in a dictionary entry, which is comprised of Display Name, Value, and Hint.

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key>
   <string>Data Server Message Output</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Data Server Product Name</key><string>DSN</string>
   <key>Data Server Product Version</key><string>9.1.5</string>
   <key>Data Server Major Version</key><integer>9</integer>
   <key>Data Server Minor Version</key><integer>1</integer>
   <key>Data Server Platform</key><string>z/OS</string>
   <key>Document Locale</key><string>en_US</string>

   <key>Short Message Text</key>
   <dict>
      <key>Display Name</key><string>Short Message Text</string>
      <key>Hint</key><string />
   </dict>
 
</dict>
</plist>

Example 4

The following example shows a sample XML message document for the GET_MESSAGE stored procedure. Similar to an XML output document, the details about an SQL warning condition will be encapsulated in a dictionary entry, which is comprised of Display Name, Value, and Hint.

  <?xml version="1.0" encoding="UTF-8" ?> 
  <plist version="1.0">
  <dict>
     <key>Document Type Name</key><string>Data Server Message</string> 
     <key>Document Type Major Version</key><integer>1</integer> 
     <key>Document Type Minor Version</key><integer>0</integer> 
     <key>Data Server Product Name</key><string>DSN</string> 
     <key>Data Server Product Version</key><string>9.1.5</string> 
     <key>Data Server Major Version</key><integer>9</integer> 
     <key>Data Server Minor Version</key><integer>1</integer> 
     <key>Data Server Platform</key><string>z/OS</string> 
     <key>Document Locale</key><string>en_US</string> 
     <key>Short Message Text</key> 
     <dict>
        <key>Display Name</key><string>Short Message Text</string> 
        <key>Value</key> 
        <string>DSNA630I DSNADMGM A PARAMETER FORMAT OR CONTENT ERROR WAS FOUND.
                The value for key 'Document Type Minor Version' is '2'. It does
                not match the value '0', which was specified for parameter 2 of
                the stored procedure. Both values must be equal.</string> 
        <key>Hint</key><string /> 
     </dict>
  </dict>
  </plist>

Example 5

This example shows a simple and static Java™ program that calls the GET_MESSAGE stored procedure with an XML input document and an XPath that queries the short message text of an SQLCODE.

The XML input document is initially saved as a file called xml_input.xml that is in the same directory where the GetMessageDriver class resides. This sample program uses the following xml_input.xml file:

  <?xml version="1.0" encoding="UTF-8" ?> 
  <plist version="1.0">
    <dict>
      <key>Document Type Name</key> 
      <string>Data Server Message Input</string> 
      <key>Document Type Major Version</key> 
      <integer>1</integer> 
      <key>Document Type Minor Version</key> 
      <integer>0</integer> 
      <key>Document Locale</key> 
      <string>en_US</string> 
      <key>Complete</key> 
      <false /> 
      <key>Required Parameters</key> 
      <dict>
        <key>SQLCODE</key> 
        <dict>
          <key>Value</key> 
          <integer>-204</integer> 
        </dict>
      </dict>
      <key>Optional Parameters</key> 
      <dict>
        <key>Message Tokens</key> 
        <dict>
          <key>Value</key> 
          <array>
            <string>SYSIBM.DDF_CONFIG</string> 
          </array>
        </dict>
      </dict>
    </dict>
  </plist>

The XPath is statically created as a string object by the program and then converted to a BLOB to serve as input for the xml_filter parameter. After the stored procedure is called, the xml_output parameter contains only a single string and no XML document. This output is materialized as a file called xml_output.xml that is in the same directory where the GetMessageDriver class resides.

Sample invocation of the GET_MESSAGE stored procedure with a valid XML input document and a valid XPath:

//***************************************************************************
// Licensed Materials - Property of IBM
// 5635-DB2
// (C) COPYRIGHT 1982, 2006 IBM Corp. All Rights Reserved.
//
// STATUS = Version 9
//***************************************************************************
// Source file name: GetSystemDriver.java
//
// Sample: How to call SYSPROC.GET_SYSTEM_INFO with a valid XML input document
// and a valid XPath to extract the operating system name and release.
//
// The user runs the program by issuing:
// java GetSystemDriver <alias or //server/database> <userid> <password>
//
// The arguments are:
// <alias> - DB2 subsystem alias for type 2 or //server/database for type 4
//    connectivity
// <userid> - user ID to connect as
// <password> - password to connect with
//***************************************************************************
import java.io.*;
import java.sql.*;

public class GetSystemDriver
{
   
   public static void main (String[] args)
   {
      Connection con = null;
      CallableStatement cstmt = null;
      String driver = "com.ibm.db2.jcc.DB2Driver";
      String url = "jdbc:db2:";
      String userid = null;
      String password = null;
      
      // Parse arguments
      if (args.length != 3)
      {
        System.err.println("Usage: GetSystemDriver <alias or //server/database> 
<userid> <password>");
        System.err.println("where <alias or //server/database> is DB2 subsystem 
alias or //server/database for type 4 connectivity");
        System.err.println(" <userid> is user ID to connect as");
        System.err.println(" <password> is password to connect with");
        return;
      }
      url += args[0];
      userid = args[1];
      password = args[2];
      
      try {
      
        String str_xmlfilter = new String(    
          "/plist/dict/key[.='Operating System Information']/following-sibling::
dict[1]" +
          "/key[.='Name and Release']/following-sibling::dict[1]" +
          "/key[.='Value']/following-sibling::string[1]");
        
        // Convert XML_FILTER to byte array to pass as BLOB 
        byte[] xml_filter = str_xmlfilter.getBytes("UTF-8");        
        
	      // Read XML_INPUT from file 
	      File fptr = new File("xml_input.xml"); 
	    
	      int file_length =  (int)fptr.length();
	      byte[] xml_input = new byte[file_length];
	
	      FileInputStream instream = new FileInputStream(fptr);
	      int tot_bytes = instream.read(xml_input,0, xml_input.length);
	      if (tot_bytes == -1) {
	        System.out.println("Error during file read");
          return;   
        }
	      instream.close();
               
        // Load the DB2 Universal JDBC Driver
        Class.forName(driver);
        
        // Connect to database
        con = DriverManager.getConnection(url, userid, password);
        con.setAutoCommit(false);
        
        cstmt = con.prepareCall("CALL SYSPROC.GET_SYSTEM_INFO(?,?,?,?,?,?,?)");
        
        // Major / Minor Version / Requested Locale
        cstmt.setInt(1, 1);
        cstmt.setInt(2, 1);
        cstmt.setString(3, "en_US");
        
        // Input documents
        cstmt.setObject(4, xml_input, Types.BLOB);
        cstmt.setObject(5, xml_filter, Types.BLOB);
        
        // Output Parms
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.registerOutParameter(6, Types.BLOB);
        cstmt.registerOutParameter(7, Types.BLOB);
        
        cstmt.execute();
        con.commit();
       
        SQLWarning ctstmt_warning = cstmt.getWarnings();
        if (ctstmt_warning != null) {
          System.out.println("SQL Warning: " + ctstmt_warning.getMessage());
        }
        else {
          System.out.println("SQL Warning: None\r\n");
        }
        
        System.out.println("Major Version returned " + cstmt.getInt(1) );
        System.out.println("Minor Version returned " + cstmt.getInt(2) );
        
        // Get output BLOBs 
        Blob b_out = cstmt.getBlob(6);
        
        if(b_out != null)
        {
          int out_length = (int)b_out.length();
          byte[] bxml_output = new byte[out_length];
          
          // Open an inputstream on BLOB data 
          InputStream instr_out = b_out.getBinaryStream();
          
          // Copy from inputstream into byte array 
          int out_len = instr_out.read(bxml_output, 0, out_length);
          
          // Write byte array content into FileOutputStream 
          FileOutputStream fxml_out = new FileOutputStream("xml_output.xml");
          fxml_out.write(bxml_output, 0, out_length );
          
          //Close streams
          instr_out.close();
          fxml_out.close();
        }
        
        Blob b_msg = cstmt.getBlob(7);
        
        if(b_msg != null)
        {
          int msg_length = (int)b_msg.length();
          byte[] bxml_message = new byte[msg_length];
          
          // Open an inputstream on BLOB data 
          InputStream instr_msg = b_msg.getBinaryStream();
          
          // Copy from inputstream into byte array 
          int msg_len = instr_msg.read(bxml_message, 0, msg_length);
          
          // Write byte array content into FileOutputStream 
          FileOutputStream fxml_msg = new FileOutputStream(new File
("xml_message.xml"));
          fxml_msg.write(bxml_message, 0, msg_length);
          
          //Close streams
          instr_msg.close();
          fxml_msg.close();
        }
      }
      
      catch (SQLException sqle) {
         System.out.println("Error during CALL "
               + " SQLSTATE = " + sqle.getSQLState()
               + " SQLCODE = " + sqle.getErrorCode()
               + " : " + sqle.getMessage());
      }
      
      catch (Exception e) {
         System.out.println("Internal Error " + e.toString());
      }
      
      finally
      {
         if(cstmt != null)
            try { cstmt.close(); } catch ( SQLException sqle) 
{ sqle.printStackTrace(); }
         if(con != null)
            try { con.close(); } catch ( SQLException sqle) 
{ sqle.printStackTrace(); }
      }
   }
}