VB4 + RDO + ODBC + ORACLE = Problems? 
Author Message
 VB4 + RDO + ODBC + ORACLE = Problems?


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,
    '==> Using 32-bits driver from Microsoft/Visigenic
    '==> 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

    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>
        '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...
            ko(0)= ko(0) 'simulate update of one column
            '***  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!
        '***  using VISIGENIC ver. 2.00.0000 driver: when i=99
        '***  Run-time error '40002'
        '***  S1010:[Microsoft][ODBC Driver Manager]Function sequence error
    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)

Mon, 08 Mar 1999 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. BIIIIIIIG Problem using Oracle / ODBC / RDO

2. Problem using VB 4.0, RDO 1.0b, Visigenic 32bit ODBC and ORacle 7.2

3. Problem with Oracle Rdb, RDO, ODBC and Dates

4. VB 5.0, RDO, ODBC, Oracle stored procedure problem...

5. Problem with ODBC + RDO + Oracle


7. Maximum open cursors -problems with VB4, RDO, Oracle71 and ODBC

8. Problems of performance with VB4 and ORACLE ODBC

9. VB4/ODBC/Oracle Problem

10. VB4 ODBC Oracle/Rdb tranaction problem

11. VB4 Oracle/Rdb ODBC transaction problem

12. VB4-32bit/Win95 Oracle ODBC problems.


Powered by phpBB® Forum Software