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

From: Eric Fortin <emfortin_at_attbi.com>
Date: Sun, 20 Apr 2003 22:30:03 GMT
Message-ID: <LbFoa.545869$L1.159798@sccrnsc02>

"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? Received on Sun Apr 20 2003 - 17:30:03 CDT