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

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 Received on Sun Apr 20 2003 - 18:22:35 CDT