Re: How to lock a record in a table using a query, VB6 and Oracle as a database

Gaz wrote:
> Hi,
>
> Spent the last week searching for this solution here is my problem....
>
> I basically want to to be able to search for a record and then view it
> but if i view it i want to lock it so no one else can edit it. This is
> becuase i have got a browse and edit mode in my software. The problem
> is it doesnt want to lock and i can update the record from the
> database.
>
> Im using VB6 and adodb as a connection string ive tried opening the
> record for update but cant seem to get it too work and its doing my
> head in.
>
> All i want is to be able to parse a SQL string through and then lock
> the tables/records it relates to is this possible??
>
> Here is my code.....
>
> set rst = new adodb.recordset
> rst.Source = "Select t1.col2, t2.col1 " & _
> "From tableone t1,tabletwo t2 " & _
> "Where (t1.id=t2.id) and (t1.id=" & strID
> & ")"
> .Open , gcnnLive, adOpenKeyset, adLockOptimistic
>
> the query itself works and it populates my txtboxes but no data is
> bound, ive tried using the ado control for VB6 and that shows the data
> but doesnt lock it either im desperate can anyone help me or offer
> advise??
>
> Cheers
>
> gaz

Experiment with this code to see what it does:

    Dim strSQL As String
    Dim snpData As New ADODB.Recordset

    gcnnLive.BeginTrans

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  T1.COL2," & vbCrLf
    strSQL = strSQL & "  T2.COL1" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  TABLEONE T1," & vbCrLf
    strSQL = strSQL & "  TABLETWO T2" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  T1.ID=T2.ID" & vbCrLf
    strSQL = strSQL & "  AND T1.ID" = strID & vbCrLf
    strSQL = strSQL & "FOR UPDATE"

    snpData.Open strSQL, gcnnLive, adOpenKeyset, adLockOptimistic Stop

    snpData.Close
    gcnnLive.CommitTrans

Note the Stop statement in the above. When the program reaches that point, execute the following using SQLPlus: SELECT
  *
FROM
  V$LOCKED_OBJECT; You should see two entries in this view that were created by the SELECT statement in your VB program. Using SQLPlus, try to view the record that was selected in the VB program - you should have no problem viewing the record. Now, try to update the record - SQLPlus should hang until the CommitTrans is processed in the VB program.

Keep track of the number of BeginTrans and corresponding CommitTrans/RollbackTrans that the program encounters - every BeginTrans must eventually have either a CommitTrans or RollbackTrans or unexpected things will happen in the program. Also, your program should specify CommandTimeout on the database connection (gcnnLive) so that locking errors can be returned by the database to your program in a timely manner, if another session already locked the rows in the table.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Oct 02 2006 - 06:35:25 CDT