Re: Foolish question regarding ref cursors

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 15 Mar 2004 13:03:10 -0600
Message-ID: <u8yi2szob.fsf@standardandpoors.com>

On Fri, 12 Mar 2004, rscrawfordDUCK_at_mossREMOVEWATERFOWLroot.com wrote:
> Once this stored procedure has been called, I can refer to the
> ref cursor in Cold Fusion to get the results and use Cold
> Fusion to build a web page using the data.
>
> If I add the line "CLOSE courseInfo" or "CLOSE studentInfo", I
> get an error message when I try to call the stored procedure
> from Cold Fusion.
>
> Is there something more I should be doing?

I see. You need to close the cursor from Cold Fusion.

At lease in Java, thats how it is done. Maybe Cold Fusion has an analogy? In the following code snippet (I cut it a bit to remove some of our homegrown stuff so it won't be exactly accurate) of a java method, we call a stored Procedure, iterate over the results and then close the cursor, from the client code. The proc just returns a ref cursor.

  public Collection findAll(            

	)
       Collection mResults = new ArrayList();
       OracleCallableStatement mCallableStatement = null;
       OracleResultSet mResultSet = null;

    try {         

        String mSqlString;         

        StringBuffer mSqlStringBuffer = new StringBuffer();         

        mSqlStringBuffer.append("{ ?= ");

        //the finder methods will always return a cursor

	    mSqlStringBuffer
		.append(" call ")
		.append("P_ACCOUNT")
		.append(".")
		.append("FIND_ALL");
	    mSqlStringBuffer.append("()}");

	    mSqlString = mSqlStringBuffer.toString();

	Connection mConnection = Connection.getVendorConnection(oConnection);
	    //2
	    mCallableStatement = (OracleCallableStatement)mConnection.prepareCall(mSqlString);

	    // there is only one out arg always and it's a ref cursor
	    // returnArgList is used for processing a result set
        mCallableStatement.registerOutParameter(1, OracleTypes.CURSOR);       
	    
	    mCallableStatement.execute();


	    mResultSet = (OracleResultSet)
		mCallableStatement.getObject(1);
	    
	    // iterate over the result set, create new DAO and call setter on every parameter in out arg list
	    while(mResultSet.next()) {
		PAccount wPAccount = new PAccount(oUserSessionContext);

	    
		wPAccount.setAccountId(
		   mResultSet.getLong("A_ACCOUNT_ID")
		 );
        
                   wPAccount.setNm(
		   mResultSet.getString("A_NM")
		 );
          
                 if(mResultSet.wasNull()) {
                    
                wPAccount.setNm(new String(""));
                 }
              
                   wPAccount.setDsc(
		   mResultSet.getString("A_DSC")
		 );
          
                 if(mResultSet.wasNull()) {
                    
                wPAccount.setDsc(new String(""));
                 }
              

	     mResults.add(wPAccount);   
	    }//while			      

	} catch(Exception e) {
	    log.error(e, e);
	    throw Exception(e);
	} finally {
            try {
               mResultSet.close();
               mCallableStatement.close();
               mConnection.close();
            } catch (SQLException e) {
               log.error(e, e);
            }
        } // finally
	return mResults;

    }

-- 
Galen Boyer

Received on Mon Mar 15 2004 - 13:03:10 CST