From: "The NewsBrowser" <nbr@newsbrowser.com>
Subject: Re: Error message retrieving data from Oracle database using subquery via Microsoft rdo
Date: 2000/04/04
Message-ID: <8cb9c8$64a1v$1@fu-berlin.de>#1/1
References: <8c5ih6$4j2ok$1@fu-berlin.de> <38E671BA.6D8A@btinternet.com>
X-Trace: fu-berlin.de 954804425 6432831 212.159.77.211 (16 [18325])
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,microsoft.public.vb.database.odbc,microsoft.public.vb.database.rdo,microsoft.public.vb.general.discussion


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@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