Re: lock Oracle db objects, but only a lock for others?

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sun, 20 Apr 2003 20:27:34 -0700
Message-ID: <3EA36526.9CE20AAC@exxesolutions.com>

Bob Kilmer wrote:

> "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3EA32BBB.1A412583_at_exesolutions.com...
> > Eric Fortin wrote:
> >
> > > "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> > > news:3EA31A5F.C26DAB1A_at_exesolutions.com...
> > > > > > On Error Goto
> > > > >
> > > > > What is the correct way to Error Handle in VB6?
> > > >
> > > > To handle the errors in the back-end, or middle-tier ifone exists: Not
> in
> > > the
> > > > front-end. VB should only be used to display messages returned from
> the
> > > serer.
> > >
> > > >
> > > > > >
> > > > > > If things like this have been written expect that the entire code
> base
> > > > > needs to
> > > > > > be rewritten.
> > > > > >
> > > > >
> > > > > Can you please give an example of a correct construction for VB6 and
> > > Oracle?
> > > > >
> > > > > (or is it the order, begin trans followed by error handling)
> > > > > (or is it that vb6 should not be used with Oracle)
> > > > >
> > > > > I guess I'm not clear on how it should be done.
> > > > >
> > > > > Thanks.
> > > >
> > > > No problem. I teach an entire certificate program, three quarters, at
> the
> > > > University of Washington on the subject. So I'll just include all of
> that
> > > > information here in this usenet post
> > > > (http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp)
> just
> > > > kidding. Seriously this is not
> > > > something that can be done generically. Just to give you an example of
> > > what I
> > > > would consider minimal information required to construct Oracle error
> > > handling
> > > > ... I would say that you must understand all of the following:
> > > >
> > > > TooManyDays EXCEPTION;
> > > > EXCEPTION WHEN TooManyDays THEN
> > > > EXCEPTION WHEN NO_DATA_FOUND THEN
> > > > EXCEPTION WHEN OTHERS THEN
> > > > PRAGMA EXCEPTION_INIT
> > > > RAISE;
> > > > RAISE_APPLICATION_ERROR(-20001, 'Date Can Not Be In The Future');
> > > > AUTONOMOUS TRANSACTIONS
> > > >
> > > > And likely I'll think of something I missed as soon as press the SEND
> > > button.
> > > >
> > > > Daniel Morgan
> > > >
> > >
> > > On error goto is in the VB6 code
> > >
> > > if that exception is thrown in the db and not handled in the VB app, the
> vb
> > > app will get a terminating ora-440 error. (Then, especially if no begin
> > > trans, all the sql up to that point will be auto committed and the sql
> after
> > > that 440 error will never be executed)
> > >
> > > Here is the Oracle Doco ( bottom of the page): on how to do it with Ora
> OLE
> > > DB.
> > >
> > >
> (http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm#
> > > 1006186)----code sample:Dim Oracon As ADODB.Connection
> > > Dim recset As New ADODB.Recordset
> > > Dim cmd As New ADODB.Command
> > > Dim param1 As New ADODB.Parameter
> > > Dim param2 As New ADODB.Parameter
> > > Dim objErr As ADODB.Error
> > > Dim Message, Title, Default, EmpNoValue
> > >
> > > Message = "Enter an employee number (5000 - 9000)"
> > > Title = "Choose an Employee"
> > > Default = "7654"
> > >
> > > On Error GoTo err_test
> > >
> > > EmpNoValue = InputBox(Message, Title, Default)
> > > If EmpNoValue = "" Then Exit Sub
> > > If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654
> > >
> > > Set Oracon = CreateObject("ADODB.Connection")
> > > Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
> > > "Data Source=exampledb;" & _
> > > "User ID=scott;" & _
> > > "Password=tiger;"
> > >
> > > Oracon.Open
> > > Set cmd.ActiveConnection = Oracon
> > > Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
> > > EmpNoValue)
> > > cmd.Parameters.Append param1
> > > Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
> > > cmd.Parameters.Append param2
> > >
> > > ' Enable PLSQLRSet property
> > > Cmd.Properties ("PLSQLRSet") = TRUE
> > >
> > > cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
> > > Set recset = cmd.Execute
> > >
> > > ' Disable PLSQLRSet property
> > > Cmd.Properties ("PLSQLRSet") = FALSE
> > >
> > > MsgBox "Number: " & EmpNoValue & " Dept: " &
> recset.Fields("deptno").Value
> > >
> > > Exit Sub
> > >
> > > err_test:
> > > MsgBox Error$
> > > For Each objErr In Oracon.Errors
> > > MsgBox objErr.Description
> > > Next
> > > Oracon.Errors.Clear
> > > Resume Next
> > >
> > > ----End code sample.
> > >
> > > Is this incorrect?
> >
> > Properly handled errors in the database never produce errors in a
> front-end.
> > That you are handling errors as you are indicates the database is not
> handling
> > them: Nothing more.
> >
> > Daniel Morgan
> >
> >

>

> I only mentioned error handling at all (On Error Goto!) to emphasize that it
> is so difficult in this app to be sure that a lock is on or off, it is
> necessary to be ready for an error. Whether it is handled in VB or Oracle or
> by careful routing of the spaghetti or at all is beside my point.

>

> The code I walked into looks something like the following. Even if you have
> never heard of Oracle, you can see that this is a bad idea.

>

> (If I might borrow the VB code context of an earlier post)

>

> '(__calling code__)
> Set Oracon = CreateObject("ADODB.Connection")
> Oracon.ConnectionString = "blah, blah, not important"
> Oracon.Open
> Set cmd.ActiveConnection = Oracon
> cmd.ActiveConnection.BeginTrans
> cmd.CommandText = _
> "{Begin Select colName From tblName For Update; End;}"
> blnLockIsOn = True
> 'Do stuff
> 'call other code

>


> '(__called code__)

> 'rollback to end "select for update" lock
> 'so this routine can access the data
> If blnLockIsOn then
> cmd.ActiveConnection.RollbackTrans
> blnLockIsOn = False
> End If
> '
> 'Do stuff,
> 'including selects, updates, inserts, whatever
> '
> cmd.ActiveConnection.BeginTrans
> cmd.CommandText = _
> "{Begin Select colName From tblName For Update; End;}"
> blnLockIsOn = True
> 'return to caller

>


> '(__back to calling code__)

> 'do stuff

> 'call other code

> 'setting and removing locks all the while until
> 'eventually, somewhere, hopefully.
> If blnLockIsOn then
> cmd.ActiveConnection.RollbackTrans
> blnLockIsOn = False
> End If

>

> So, given that this is bad, I want to know how best to lock what needs to be
> locked, if it needs to be locked at all (if not, I don't), in such a way as
> to assure reasonable security (or concurrency, or whatever - you Oracle
> types can help me out here) for the data while simplifying the code and code
> maintenance until such time as this company Does The Right Thing and
> rewrites the entire code base, carefully coached by duely qualified Oracle
> gurus. That is all.

>

> I feel like, on the whole, I am getting the run around here. I can
> appreciate that I may be ignorant of the scope of my ignorance, but I have
> to start somewhere, and that has been by reading as much of the plentiful
> documentation available to me as I can and posting what I had hoped would be
> taken as a well worded question. Since I cannot hire a consultant and
> because the company is not likely to hire an Oracle consultant soon, at
> least not for this problem, one that they have been willing to live with, I
> will continue reading. If anyone here has any more helpful pointers, such as
> the list noted by Daniel, and the "Oracle Concurrency Model" noted by Simeon
> Rice, I'll make good use of it.

>

> Thanks,
> Bob Kilmer

Handle it by not interacting directly with tables but rather by sending and receiving records via procedures and REF CURSORS. Also have an out parameter from the procedure returning a message indicating anything from the number of rows returned to an error message to display to the user. Received on Sun Apr 20 2003 - 22:27:34 CDT