retrieving recordset-help needed urgent

[mmcg]

Hi all,
I am unable to retrieve recordsets (Connected to Oracle 9i) but the PL/SQL procedure is getting executed(all the delete and update queries are working fine).If I try to access the values in the record set I get an error:
Operation is not allowed when the object is closed.

The part of the code is:

Set Comm = Server.CreateObject("ADODB.Command&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot
RS.CursorLocation = 3
Set comm.ActiveConnection = Conn
/*con -> connection object*/
comm.commandtype = 4
comm.commandtext = "test"
/*test -> PL/SQL procedure*/
Set rs = comm.execute()
response.write rs.eof /* or any code to access the record set gives an error*/
Any help appreciated
Thanx in advance

 

[ToddWW]

Simple script to open recordset.

dim strProvider
dim RS
dim strQuery

strProvider =

Connection String or DSN

Set RS = Server.CreateObject("ADODB.recordset")
RS.CursorLocation = 3
RS.CursorType = 3
strQuery = "SELECT * FROM table"
RS.Open strQuery, strProvider
set RS.ActiveConnection = Nothing

Fully disconnected, static, free moving recordset. Make appropriate changes to CursorLocation, CursorType, and ActiveConnection as needed.

ToddWW

 

[mmcg]

Hi ToddWW,

Recordset retrieved for a normal SQL QUERY but not for a PL/SQL procedure.Can you help me?

 

[mmcg]

Hi all,
Can someone help me with this?

 

[desktophero]

mmcg,

When are you passing the SQL statement to the rs object? In your example, it does not appear that you actually open a recordset.

Where you have 'set rs=comm.execute()', shouldn't there be a statement with the parens?

I suppose I am missing something, since I am not that familiar with Oracle PL/SQL

 

[ros]

I've had the same message from mssql when running a Stored Proc. The solution was to edit the procedure and remove all output except the final table. Use SET NOCOUNT ON to get rid of all "xxx rows affected" lines and remove any print statements in the Procedure.
HTH,
Richie

 

[mmcg]

Hi ros,
My stored proc is as follows:
create or replace PROCEDURE TEST
AS
a number(4);
BEGIN
SELECT empno into a from emp WHERE sal=1950;
END;
/
This is just a simple proc to check how it works.As u said I removed the print statement.How to proceed next?. Where should I use SET NOCOUNT ON?.

 

[mmcg]

Hi ros,
Thanx.But I still have a problem.
My stored proc is as follows:
create or replace PROCEDURE TEST
AS
a number(4);
BEGIN
SELECT empno into a from emp WHERE sal=1950;
END;
/
This is just a simple proc to check how it works.As u said I removed the print statement.How to proceed next?. Where should I use SET NOCOUNT ON?.

 

[mmcg]

Hi desktophero,
I call my stored proc.(test) through the command object as
comm.commandtext = "test"
So, I need not have a statement with the parens in
set rs=comm.execute()
Thanx

 

[mmcg]

Hi ros,
In msmql it is SET NOCOUNT ON
but for oracle what command supports this option?
Any help appreciated.

 

[cmmrfrds]

Here are some ideas to help you figure out what is going wrong. Others have explained how to eliminate multiple recordsets being returned from a stored procedure. Until you figure out how to do it in Oracle here are some general hints. First for debugging reference an ordinal name instead of the actual name - rs(0). Second check the state of the recordset. Third, loop and check for additional recordsets.

Set rs = comm.execute()

if rs.state > 0 then
response.write rs(0) '- print value in first column returned
'- this should tell you what is being returned.
end if

OR TRY looping through all the recordsets returned and
picking out the one you want by something unique like the
first column name. I am not including how to check the fields collection for the name.

WHILE rs.state not = 0
response.write rs(0)
Set rs = rs.NextRecordSet
WEND

 

[codestorm]

Also:
I'm in no way familiar with PL/SQL, but it appears to me (based on MSSQL T-SQL experience) that in the SP you are defining a number called a and selecting a (hopefully) single value into it. However, it doesn't appear you are returning that value from the SP.
I'm totally guessing you might want to add a final line like

select a codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[mmcg]

Hi cmmrfrds,
rs.state gives 0 that is, no recrdset. But if I use a delete or update Query inside the stored proc. it works .

 

[cmmrfrds]

The select must not be finding a record for some reason. Probably change it to know for sure it will return a recordset like.
SELECT count(*) from emp

This snytax may be only loading a variable not a recordset - I don't know Oracle specifics.
SELECT empno into a from emp WHERE sal=1950

 

[ros]

Disclaimer : I don't know oracle
However, it looks to me like your test procedure doesn't return anything, merely sets the varable a.
try:
create or replace PROCEDURE TEST
BEGIN
SELECT empno from emp WHERE sal=1950;
END;

this should return a recordset containing field empno.

 

[BarkingFrog]

This may sound like a dumb question, but I'm going somewhere with it:

How do you know that your &quot;Insert&quot; and &quot;Update&quot; and &quot;Delete&quot; queries are working? Are you looking directly into the database?


Dave

 

[mmcg]

Hi barkingfrog,

yes, I query the db before and after executing the stored proc.

 

[mmcg]

Hi ros,
An INTO a is necessary while querying a db within stored proc.

 

[codestorm]

As queried before, does an Oracle SP implicitly return a declared variable?
That is, is the variable 'a' automatically being returned from the SP call? codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[mmcg]

Hi codestrom,
The variable 'a' contains the result from the query which can be obtained later using recordsets (if in ASP) or called directly (if inside other procedures or within the same).