Ado MoveFirst method problem with ORAOLEDB v.9.2.01 provider

Hi all,
I am using an old ASP technology with ORAOLEDB.Oracle v.9.2.01 provider, ADO connection with CursorLocation = adUseServer and ADODB.Recordset. In the last release we updated ORACLE 8i to 9.2.03 and in one of asp pages I am having a problem with recordset.MoveFirst method. The page is a report that contains a couple passes of the same recordset. The related code is following:
 
<!-- #include file="..\scripts\connect.asp" -->
DIM oConn
DIM oResult
DIM oResult1
DIM oResult2
‘Connect to Oracle DB with CommandType= adCmdStoredProc
call exec_connect(oConn)  

‘Execute stored procedure with refcursor output -oResult.
call exec_procedure(oConn, oResult, sSPname, sParameters)  

‘Read SP output that contains 2 refcursors
Set oResult1 = oResult   ‘contains 107 records
Set oResult2 = oResult.NextRecordset()   ‘contains 2 records

‘1st Pass
Do While not oResult1.EOF  
       someVar = oResult1(“column_nme”)  
       …
      oResult1.MoveNext
Loop
oResult1.MoveFirst

‘2nd pass
Do While not oResult1.EOF  
       someVar = oResult1(“other_column_nme”)  
       …
      oResult1.MoveNext
Loop
oResult1.MoveFirst

‘3rd pass
Do While not oResult2.EOF  
 
      Do While not oResult1.EOF  
            someVar_1 = oResult1(“other_column_nme”)  
            …
            oResult1.MoveNext
      Loop
      oResult1.MoveFirst

       someVar_2 = oResult1(“column_nme”)          
      oResult2.MoveNext
Loop


Only the first pass works properly in this sample. After oResult1.MoveFirst I am not able to read oResult1(“column_name”) value and all following logic is corrupted. This code works fine for smaller oResult1 that contains less then 100 records. Also it was in production with Oracle 8i without any visible problems.  
Does anyone know about this ORACLE 9i bug? Any suggestions about the code will be appreciate.
LVL 4
mikoshaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
Lets see the code in exec_procedure.  Most likely you are using default value for CursorType or it is set to adOpenForwardOnly, 0 (Default).  You should use adOpenStatic, 3 instead.

Leon



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikoshaAuthor Commented:
Thanks for your response and you are right I am using default rs.CursorType =adOpenForwardOnly that theoretically allows you to perform MoveNext, MoveFirst and MoveLast operations.
Also, I tried to change it to adOpenDynamic, adOpenStatic. The result is confusing :  on the report page itself debug message tells me that it is still adOpenFirwardOnly. Here are the Connection.asp code. Am I doing something wrong or rs.execute just ignores my settings.

sub exec_connect(cn)
      set cn = Server.CreateObject( "ADODB.Connection" )
      cn.Provider = "OraOLEDB.Oracle"
      cn.ConnectionString = Session("sApplicationConnectionDat")
      cn.Open
end sub
sub exec_procedure(cn,rs,proc_nme,parameter)
      Dim cmd, param
      Dim parameters
        Dim paramcount, counter
      Dim paramval
      Dim sCommandText

      set cmd = server.CreateObject ("ADODB.Command")
      set cmd.ActiveConnection      = cn
            cmd.CommandText                  = proc_nme
            cmd.CommandType                  = adCmdStoredProc

      parameters=SPLIT(parameter,"^")
      paramcount=ubound(parameters)
      for counter = 0 to paramcount
            paramval = SPLIT(parameters(counter),"~")

            if paramval(1) = 2 then
                  set param = cmd.CreateParameter("param"&cStr(counter), paramval(2), paramval(1), 4000)
            else
                   set param = cmd.CreateParameter("param"&cStr(counter), paramval(2), paramval(1), 4000, paramval(0))
            end if
            cmd.Parameters.Append param

      next

      set rs = server.CreateObject ( "ADODB.Recordset" )
      rs.CursorType = adOpenStatic
      set rs = cmd.execute            

      set cmd = Nothing
      set param = Nothing
end sub
0
SethiCommented:
"The result is confusing :  on the report page itself debug message tells me that it is still adOpenFirwardOnly." --> This is because Oracle unlike SQL Server does not support Dynamic recordset and when you try using this type, Oracle provider will return ForwardOnly cursor type. Please check the Oracle documentation for the types supported by Oracle and then use that type.
0
mikoshaAuthor Commented:
Look what I've found at http://www.stanford.edu/dept/itss/docs/oracle/9i/win.920/a95498/using.htm#1000497

"Setting ADO Rowset property CursorType to adOpenKeyset or adOpenDynamic is not supported by Oracle Provider for OLE DB. OraOLEDB does not support either of the two as Oracle supports Statement Level Read Consistency, which ensures that the data returned by a query contains only committed data as of the time the query was executed. CursorType values adOpenStatic and adOpenForwardOnly are supported by OraOLEDB."

So, why I am not able to reset to  adOpenStatic as well?
0
mikoshaAuthor Commented:
The problem solved by changing the connection location to adUseClient that sets default cursorType to adOpenStatic. Eventually now I have to keep two different connections (server and client) in application  wich is not a big deal.

Thanks everybody for your time and help .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.