Multiple Outputs from pubdbcall

, ,
21

Here’s Java Service to return a result set from an Oracle stored procedure using JDBC and one input. You have to change the code for 2 inputs or 3 inputs and add more statements like this with a new variable name:
stmt.setString(1,inputVariable);

and change al1 the numbers 1-2-3-4- to increase them by one.
This was tested with Oracle 8.1.7, webMethods 4.6 and has 5 input fields
userId, passWord, dburl, storedProc and inputVariable.
Outputs are $dbMessage, $rowCount, results (recordset), errorCode, and errorText.
You would put your stored procedure name in the storedProc variable when you call this java service.

The shared tab includes oracle.java.driver.* as well as java.sql.*

You can copy this into the java service editor in Developer:

String userId=“”, passWord=“”, dburl=“”, storedProc=“”, inputVariable=“”;
int rowcount=0;
ArrayList al_results = new ArrayList();
Connection conn=null;
byte bytes=null;
int errorCode=0;
String errorText=“”;

IDataHashCursor id = pipeline.getHashCursor();
// only will use the first occurance of the message element
if (id.first(“userId”)) userId = (String)id.getValue();

if (id.first(“passWordword”)) passWord = (String)id.getValue();

if (id.first(“url”)) dburl = (String)id.getValue();

if (id.first(“storedProc”)) storedProc = (String)id.getValue();

if (id.first(“inputVariable”)) inputVariable = (String)id.getValue();

try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(dburl, userId, passWord);
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

CallableStatement stmt = null;
try
{
stmt = conn.prepareCall(storedProc);
stmt.setString(1,inputVariable);
stmt.registerOutParameter(2,OracleTypes.CURSOR);
stmt.registerOutParameter(3,java.sql.Types.INTEGER);
stmt.registerOutParameter(4,java.sql.Types.VARCHAR);
stmt.execute();
ResultSet rs = (ResultSet) ((OracleCallableStatement)stmt).getCursor(2);
errorCode = stmt.getInt(3);
errorText = stmt.getString(4);
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

while( rs.next() )  
  {  
   rowcount++;  
   IData result = IDataFactory.create();  
   IDataCursor resultCursor = result.getCursor();  

   for( int i=1; i<=colCount; i++ )  
     {  
      if( rsmd.getColumnType(i) == java.sql.Types.BINARY || rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY || rsmd.getColumnType(i) == java.sql.Types.BLOB || rsmd.getColumnType(i) == OracleTypes.CURSOR )  
        {  
         resultCursor.insertAfter(rsmd.getColumnName(i), rs.getBytes(i));  
        }  
      else  
        {  
         resultCursor.insertAfter(rsmd.getColumnName(i), rs.getString(i));  
        }  
     }  
   resultCursor.destroy();  
   al_results.add(result&