Re: Error message retrieving data from Oracle database using subquery via Microsoft rdo

Another suggestion could be to try using Oracle's ODBC driver, instead of Microsoft's. Don't be put-off by people's previous comments as to which is better; from my experience, Oracle's ODBC drivers have come a long way in the last 12-14 months.

The pace of development can be witnessed by the number of releases of Oracle's ODBC driver software.

When it all comes down to it I like to let Oracle handle as much of the middleware as possible because no one else knows the optimal way to interface with the server.

When you've looked at the SQL generated by their ODBC middleware as I have you can get an idea of the intelligence i.e. hints, queries that their ODBC drivers make user of. As ODBC is not trivial, it could well be that your connection problems are caused at the MS ODBC driver - Oracle RDBMS interface.

P.S. Test a range of ODBC drivers - I have found memory leaks in two releases in the 8.0.* range.

Hope that helps,

David P.

Oracle Certified DBA.

The NewsBrowser wrote:
>
> Hello David,
>
> Thanks for your response.
>
> I've tried both the suggestions you made; ensuring there's a primary
> key on the student_id table and that there are no locks on the tables
> during selection (I've specified that the results set should be
> read-only, thereby removing the need for any lock). Unfortunately,
> I still have the problem as reported below. Any further suggestions?
>
> Akin
>
> email: ak_soto at yahoo dot co dot uk
>
> DNP wrote in message <38E671BA.6D8A_at_btinternet.com>...
> >If practicable, try putting a Primary Key on Student_ID using the
> >following :-
> >
> >(from a SQL*Plus session logged in as the owning schema of the table
> >'Modules'
> >
> >ALTER TABLE Modules ADD CONSTRAINT Stude_Prim_Key PRIMARY
> >KEY(Student_ID);
> >
> >
> >This lets your middleware infer (usually automatically) which column can
> >and will be used as a 'keyset' i.e. a unique key for your purposes.
> >
> >Another thing to look at is locks. Try and take no explicit locks when
> >you open the RDO resultset. Rdo should compare the 'as now' versus 'when
> >it was read' column values for tuples which you want to update. This way
> >it catches the 'lost update' problem, by informing you that the data
> >changed since you last read it (thus preventing your update causing a
> >'lost update'.
> >
> >
> >David P.
> >
> >Oracle Certified DBA
> >
> >
> >===============================================================================
> ====
> >
> >
> >The NewsBrowser wrote:
> >>
> >> All,
> >>
> >> I have a Microsoft Visual Basic application which connects to an Oracle 7.3
> >> database using RDO. When I attempt to retrieve data using the following
> >> statement (which constructs a query with a subquery):
> >>
> >> SqlStr = "SELECT Module_Code FROM Modules "
> >> SqlStr = SqlStr & "WHERE Module_Code NOT IN "
> >> SqlStr = SqlStr & "(SELECT Module_Code FROM Student_Modules "
> >> SqlStr = SqlStr & "WHERE Student_ID = " & StudentID & ")"
> >>
> >> (i.e. "SELECT Module_Code FROM Modules WHERE Module_Code NOT IN
> >> (SELECT Module_Code FROM Student_Modules WHERE Student_ID =
> <StudentID>))
> >>
> >> Set rdoRSet = rdoConn.OpenResultset(SqlStr, rdOpenDynamic, rdConcurLock)
> >>
> >> I get an error with the following message:
> >>
> >> S1C00: [Microsoft][ODBC driver for Oracle] Cannot use Keyset-driven
> cursor
> >> on join, with distinct clause, union, intersect orminus or on read-only
> >> result set
> >>
> >> So I try changing the type of recordset to rdOpenStatic, and I get this error
> >> instead:
> >>
> >> S1C00: [Microsoft][ODBC driver for Oracle] Only Keyset-driven cursors are
> >> updateable
> >>
> >> Does anyone have any idea how to get round this problem?
> >>
> >> Thanks in advance,
> >>
> >> ---
> >> Akin
> >>
> >> email: ak_soto at yahoo dot co dot uk
Received on Tue Apr 04 2000 - 13:26:14 CDT