Re: Executing Stored Procedure that returns result set (in Kiva)

From: James Arvigo <Hooper_X_at_Spam_Rage.com>
Date: Tue, 25 Aug 1998 17:30:40 -0500
Message-ID: <35E33B10.63D6899D@Spam_Rage.com>

Ken,

I agree with you... it's a little annoying to have to do the extra "fancy footwork" as you descrribed in order to return a result set, however, for now... suffice it to say... this works.

Secondly... I'm afraid that returning result sets to a Web App server and then processing the result set in your web app server.... well it's just not 5th grader type of stuff. It's pretty deep and it's about as deep as the relationship between a Web App server and an RDBMS go. So... here's some examples that DO use a cursor as you said, but that I have working on my Netscape Enterprise Server 3.5.1 (a.k.a. LiveWire, a.k.a. "Kiva++").

Good luck!

Here's an example of an Oracle stored procedure I wrote that returns a result set:

PACKAGE types as

type customer_cursor_type is ref cursor return customer%rowtype; type dialup_cursor_type is ref cursor return dialup%rowtype; end types;

CREATE OR REPLACE PROCEDURE
getcustlist(custrows in out types.customer_cursor_type) as begin

open custrows for select * from customer order by cust_id; end getcustlist;

Here's some Server Side JavaScript for Netscape Enterprise Server that uses this result set:
(View it in a monofont like Courrier 10)

function displayQueryResult()
{
var nRetVal; // integer return value var dbProc, dbCurs; // stored procedure accessor, result set accessor var dbConn = project.pool.connection(); // temp database connection

switch (client.qryType)
{

        case "NewCalls":


            // Call a stored procedure that returns a single value


            dbProc = dbConn.storedProc("NewCallsFunc", client.custID,
                                       client.fromDate, client.toDate);
            nRetVal = dbProc.returnValue();
            write("Return Value = " + val + "<br>\n");
            write("<INPUT TYPE=submit NAME=done VALUE='Done'>\n");
            break;

        case "Minutes":


            // Call a stored procedure that returns a loopable result-set


cursor
            dbProc = dbConn.storedProc("GetCustList");


            // Get the returned result set


            dbCurs = dbProc.resultSet();



            // Generate an HTML display table from a DB cursor


            genResultSetTable(dbCurs);  // See function below
            dbCurs.close();  // close the cursor
            break;

        case "Duration":


            // and so forth...


            break;

} // switch

dbConn.release(); // Release the DB connection } // END function()

function genCursorTable(dbCurs)
{
var nRow, nCol;
var sHTML;

// Generate a table header from the cursor column names

    sHTML  = "<TABLE bgcolor=F0F0F0 border=2 cellspacing=1
cellpadding=4>\n";
    sHTML += "<TR bgcolor=669999>\n";
    for (nCol = 0; nCol < dbCurs.columns(); nCol++ ) {
        sHTML += "<TH height=32>" + dbCurs.columnName(nCol) + "</TH>\n"; }

sHTML += "</TR>\n";
write(sHTML);

// Loop through the cursor, draw each row. while (dbCurs.next())
{

        // Display row contents
        for (nCol = 0; nCol < dbCurs.columns(); nCol++ )
        {


            // Display column value, unless == NULL


            if (dbCurs[nCol] != null)
                sHTML += "<TD height=24>" + dbCurs[nCol] + "</TD>\n";
            else
                sHTML += "<TD><br></TD>\n";
        }  // for
        sHTML += "</TR>\n";
        write(sHTML);

    } // while
    sHTML = "</TABLE></P>\n";
    sHTML += "<INPUT TYPE=submit NAME=done VALUE='Done'>\n";     write(sHTML);
} // END genCursorTable()

--
James Arvigo

SQL Server & Oracle DBA
Software & Intranet Developer
Thrifty Call, Inc. via The Maxim Group
Austin, Texas *----------------------------------------------------------
EMAILS:
Work: JamesA -AT- ThriftyCall.com
Home: JArvigo -AT- Hotmail.com *
( Sorry I had obfuscate the emails... *sigh* )

Kenneth Lee wrote:

> I've seen this question posed a couple of times, but I can't quite get
> the solutions to work.  I want to execute a stored procedure that
> returns a result set back to Kiva (aka Netscape Application Server).
> I'm fairly new to Oracle, having come from a Sybase shop.  In Sybase,
> I was used to having a stored procedure return a result set from a
> procedure simply by performing a select in the proc.  However, what
> I've been reading so far on this newsgroup is that I have to do some
> sort of fancy footwork with declaring a package and a cursor and what
> not.  Is there no easier way?  And furthermore, it appears that the
> return value for most of these solutions is a cursor, which I am not
> so certain will work, but of this I'm uncertain.  Could someone
> explain to me like a 5th grader how I should do this?
>
> Thanks,
> Ken

Received on Tue Aug 25 1998 - 17:30:40 CDT