Hi!

Using VB4, RDO 1.0b, ODBC32, Oracle 7.1 Netware, we have run into the following problem:

Is it not possible using RDO/Oracle to traverse a big rdoResultset, and for each row open a
new rdoResultset for edit and update some columns?
(Yes, I know this is "bad" client/server programming, and that a stored procedure would
perform better)

We have tried ODBC drivers from Intersolv, Visigenic, Oracle.

=>The Oracle driver would'n even try. (Driver not capable).

=>Using the Visigenic driver, the program will stop after looping 99 times, with a run-time
error '40002' S1010:[Microsoft][ODBC Driver Manager]Function sequence error.
I have not investigated this further.

=>The Intersolv driver will eventually generate run-time error '40002'
S1000:[INTERSOLV][ODBC Oracle Driver]ORA-01000: Maximum open cursors exceeded
(max_open_cursors in initora7.ora is set to 100,
and increasing this parameter to maximum (255) will only
make us wait a little longer until the problem arises.)

Is there a bug in RDO/ODBC when attempting to close an rdoResultset,
so that the cursor is not being released?

We have even tried to call the SQLFreStmt directly with all possible parameters.

===> Does anyone have any solution / workaround / experience to this problem?

Below is a piece of sample code (30 lines + comments) to reproduce the problem.
It will not compile directly, because i've been modifying it a bit in notepad,
but the principles will show.
TableA should contain a few thousand rows. TableB could be a look-up table.

Private Sub Command1_Click()
    Dim gEn As rdoEnvironment
    Dim gGn As rdoConnection
    Dim po As rdoResultset, ko As rdoResultset
    Set gEn = rdoEngine.rdoEnvironments(0)
    With gEn
        .CursorDriver = rdUseOdbc 'rdUseServer 'rdUseIfNeeded
        'Must use urUseOdbc here, or else only ForwardOnly/ReadOnly will be allowed?
    End With

'*** Open the Oracle 7.1 database
    '==> Using the 32-bit ODBC-driver from INTERSOLV,
    'conn$ = "DSN=ORAINT7;SRVR=ORA7;UID=INFOSOFT;PWD=ISW"
    '==> Using 32-bits driver from Microsoft/Visigenic
    conn$ = "DSN=ORAGE7;SERVER=ORA7;UID=INFOSOFT;PWD=ISW"
    '==> using personal oracle for Win95
    'conn$ = "DSN=2:;SRVR=PO7;UID=SCOTT;PWD=TIGER"

    Set gCn = gEn.OpenConnection(conn$, rdDriverComplete, False, conn$)
    'rdoErrors(0) shows "Driver not capable", but it seems to work ok

'EXAMPLE:
    s$ = "SELECT * FROM tableA"
    i = 1
    'open a cursor pointing to appx. 3500 rows
    Set po = gCn.OpenResultset(s$, rdOpenStatic, rdConcurRowver)
    Do While Not po.EOF
        s$ = "SELECT * FROM tableB where <tableB.key> = " & <tableA.column>
        gCn.BeginTrans
        'make a lookup om another table
        Set ko = gCn.OpenResultset(s$, rdOpenStatic, rdConcurRowver)
        If Not ko.EOF Then
            i = i + 1
            Command1.Caption = i 'show progress...
            DoEvents
            ko.Edit
            ko(0)= ko(0) 'simulate update of one column
            ko.Update
            '***  using INTERSOLV ver. 2.12.0000 driver: when i=1153
            '***  Run-time error '40002'
            '***  S1000:[INTERSOLV][ODBC Oracle Driver]ORA-01000:
            '***  Maximum open cursors exceeded
            '***  open_cursors in initora7.ora is set to 100
        End If
        'Have also tried ODBC api to free cursor
        'res& = SQLFreeStmt(ko.hstmt, 1) '0=SQL_CLOSE, 1=SQL_DROP
        ko.Close 'close the cursor!
        gCn.CommitTrans
        po.MoveNext
        '***  using VISIGENIC ver. 2.00.0000 driver: when i=99
        '***  Run-time error '40002'
        '***  S1010:[Microsoft][ODBC Driver Manager]Function sequence error
    Loop
    po.Close 'close the cursor!
    gCn.Close 'close the connection
End Sub

--
_____________________________________________________________________
 Morten Rudlang, INFO-SOFTWARE AS, Postboks 22 Furuset, N-1001 OSLO
 Tlf. 22 30 90 74 (direkte), or    22 30 90 50 (beskjed)