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

Yes,
"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EA365DB.9C3326B9_at_exxesolutions.com...
> 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
>

I'm interested how many of the dba's here agree that the front end developers should stop error handling their code. I'd be interested to hear how many of the end users would agree with this philosophy. Usually developers are flamed because they don't error handle.

Someone should inform oracle to update their java and vb tutorials, too, because that's how I (and others, I assume) learned how to handle the nagging errors that come up when the world doesn't rotate perfectly. I don't mean to be disrespectful, Daniel, if I even sound that way, but I feel damned if I do, damned if I don't, and still see no specificity (assuming the docs are not the best way to do it) how it should be done.

(However, I don't think it's reasonable or practical to require an out error message parameter for when ever an exception is raised. And what about triggers... certainly there has to be errorhandling in the front end insert case (the user has to know that the insert failed). That's why they have raise_application_error.--Also, if your not clear about vb6, without an 'on error goto ' and the db raises an exception, the vb app will terminate with a run time error)

Perhaps someone can supply a very simple vb6 module with the correct way (as the one I posted, from oracle, appears not the best way) Or perhaps Daniel and I disagree on semantics. When I see the on error goto..., as far as the db stuff is concerned I would just present the message returned (in the oracle error object, like the oracle code does), but also clean up the work started in the module but not complete.)

I hear I'm wrong,
I showed you what oracle's doc said.
Please show me what's right.

With oracle, I have to deal with insert, update, delete, procs, functions, and triggers, just the simplest stuff oracle has to offer. Received on Sun Apr 20 2003 - 23:31:36 CDT