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

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 Sat Apr 01 2000 - 00:00:00 CST