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

From: The NewsBrowser <nbr_at_newsbrowser.com>
Date: Tue, 4 Apr 2000 00:27:20 +0100
Message-ID: <8cb9c8$64a1v$1@fu-berlin.de>

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 Mon Apr 03 2000 - 18:27:20 CDT