Locking records with a database application?

[bjackson]

Here's the setup:&nbsp;&nbsp;This problem concerns locking a given record from the application.&nbsp;&nbsp;The application is written in C++ (MFC).&nbsp;&nbsp;The database in question is an Oracle 8i database.&nbsp;&nbsp;The primary problem seems to be that the Oracle ODBC driver doesn't support dynamic or dynaset type recordsets.&nbsp;&nbsp;We can only select stuff as forward only, and static.&nbsp;&nbsp;It also doesn't seem to support the pessimistic locking functionality of CRecordset.<br><br>User A opens a record with the intent of updating them.&nbsp;&nbsp;While User A is making modifications to the record, User B opens the same record with the intent of updating it.&nbsp;&nbsp;User A finishes his modifications, and commits the record.&nbsp;&nbsp;User B finishes his modifications, and commits the record, therby nuking everything User A has just saved.<br><br>I can construct my SQL statement using the &quot;FOR UPDATE OF ColumnName NOWAIT&quot; clause, and this _kind of_ works.&nbsp;&nbsp;The drawback is that the application doesn't realise that the record is locked until User B tries to commit the record, at which point Oracle begins to make noise about the record being locked.&nbsp;&nbsp;At this point, User B has to refetch the record, and consequently loses all of his modifications.&nbsp;&nbsp;What I'm looking for is a way for User B to know that somebody else is modifying the record, and he can't play with it right now (i.e. it's read only).<br><br>Any thoughts?&nbsp;&nbsp;I can't believe that I'm the first person who's had to deal with this, but I haven't been able to find a single thing that addresses this issue!&nbsp;&nbsp;&lt;grrrrrrr!&gt;<br> <p>Beau Jackson<br><a href=mailto:bjackson@bloomington.in.us>bjackson@bloomington.in.us</a><br><a href= > </a><br>

 

[palbano]

Dear Beau,<br><br>If you are using Oracle as your database then you are stuck with whatever Oracle provides as an interface to their product. If they don't have a suitable ODBC or even better an OLE DB driver you can use then you have to use whatever they do provide, probably a proprietary C library interface.<br><br>Good luck<br>-pete<br>