[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

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”)  

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

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

       someVar_2 = oResult1(“column_nme”)          

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.
  • 3
2 Solutions
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.


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")
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

      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)
                   set param = cmd.CreateParameter("param"&cStr(counter), paramval(2), paramval(1), 4000, paramval(0))
            end if
            cmd.Parameters.Append param


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

      set cmd = Nothing
      set param = Nothing
end sub
"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.
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?
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 .

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now