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

I am trying to do something similiar from ODBC. A question arises from the example below:

GetCustList seems to be called without a parameter, yet it returns a customer_cursor_type variable. Is this right?

Thanks Much,
Randy Brokaw
rpbrokaw_at_tanningtech.com

James Arvigo wrote in message <35E33B10.63D6899D_at_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 Mon Aug 31 1998 - 14:21:00 CDT