Eric Fortin 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
> >
>
> You cannot be more wrong. I don't care if it's java, c++, vb6, ms.net, or
> some other language, if you throw an exception in the database, an exception
> is going to be thrown in the front end app. If you do not handle exceptions
> in the front end app, that app is going to terminate. (The front end would
> have to let the user know that there was a failure)
>
> By the way, this is not my code, it is Oracle's.
>
> Here is the same syntax (on error goto in vb6) in java (taken from
> developer.java.sun.com)
>
> try
> {
> // some DB work
> } // end try
> catch ( SQLException SQLe) //
> {
> while( SQLe != null)
> {
> // do handling
>
> SQLe = SQLe.getNextException();
> }
> } // end catch
I couldn't be more wrong? Really? I've been doing this, and teaching it, for
more than 30 years and have yet to see an error handled in a database create an
error in a front-end. I don't know what you've been coding but front-ends can
not have an error if an error is properly handled in the back-end or
middle-tier: All they can do is display the message to the user.
Daniel Morgan
Received on Sun Apr 20 2003 - 22:30:35 CDT
