Re: Locking records in DB2

    Hi Ron,

    I completely agree with Paul.  Record locking has no place in any
interactive program, green screen, GUI, or web.  I certainly understand that
if the tradeoffs are concurreny vs data integrity, data integrity has to
win, although I've seen even commercial programs that, intentionally or not,
ignore data integrity.

    I'm involved in a project that will have a large number of essentially
CRUD apps.  I'm greedy; I want both concurrency and integrity.  After a
search for tools that met my criteria, I ended up with my own framework
based on major modifications to SQL2Java, my own controller code and
CachedRowSets.  As it happens, I just finished final testing Tuesday morning
on what I call "SQL maintenance for non-weenies."

    Here's how it works:

    Auto-gen bean and manager once per table.  You can gen a list of tables
or everything in the database at once.  The bean has a no-arg constructor
and auto - obtains a reference to its singleton manager.  Compile the bean
and manager ( javac *.java ).

    In a program, get a bean instance and a holder instance.  The holder
contains a boolean to check for success/failure, a CachedRowSet, a bean
instance, a DataSource, a reference to an SQLException, and rhree error
Strings.  The programmer has to load only the bean instance and the
DataSource.  Then, get an initialized RowSet, using the provided method.

    From that point on, a program has only to load the bean with input -
keys or data to be inserted/modified.  Everything else is handled by the
framework: generating SQL, data retrieval/update, and loading the bean with
retrieved info.

    Here's the entire API for the maintenance manager:

  public static void deleteRow( DBMaintHolderCRS holder )
        deletes a row.

  public static void getInitializedRowSet( DBMaintHolderCRS holder )
        Called once; the returned RowSet is kept in the holder.

  public static void insertForGeneratedKey( DBMaintHolderCRS holder )
        Inserts row/returns the generated key in the appropriate bean field.

  public static void insertStandard( DBMaintHolderCRS holder )
        Insert for standard, non-auto key rows.

  public static void selectRow( DBMaintHolderCRS holder )
        gets a row, with data returned in the bean.

  public static void updateRow( DBMaintHolderCRS holder )
        update a row.

    Notice that the programmer doesn't have to deal with SQL or use
try/catch blocks; no exceptions are thrown.  Instead, any errors and/or
exceptions are returned in the holder.

    And, optimistic concurrency is thrown in for free.  If the data in the
database doesn't match the original inquiry, an error is sent back in the
holder for deletes and updates.

    Now comes the developer's dilemma in the current environment.  If you
think about it, in terms of time saved, this module would quickly pay for
itself at almost any price.  I believe open source has its place and I'm a
strong believer in and contributor to the community ( check my background;
there's a reason I received the Java Community Award at J198.)

    At the same time, I believe a professional should be compensated for
his/her work.  BTW, If anyone wants to debate, my first question will be
"Are all of your/your company's programs open sourced?  Even if yes, did you
get paid for developing it?"  That's even more the case for a developer like
me, who gets no salary; if I don't bill or sell, I don't eat.

    What I see today is "gotta be open source" even if weeks are spent
getting the thing to work.  I've considered opening what I call "The
Pizzaria" on conceptGO's site, with the idea that the software there would
be about the price of a pizza ( ~$20 ) per in-house developer.  But, given
the environment, I don't know that it's worth the effort.  We may just end
up keeping products like this for our own competitive advantage while people
ask "Where's an open source product that..."


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: "Paul Morgan" <pmorgan@xxxxxxxxxxxxxx>
To: <java400-l@xxxxxxxxxxxx>
Sent: Tuesday, October 04, 2005 3:08 PM
Subject: Re: Locking records in DB2


> Ron,
>
> You might consider an optimistic update.  Read the record for display but
> don't lock it.  When the update occurs you check that the fields you are
> updating on the record haven't changed since the original read (by using
the
> old values as well as a key in the update's selection clause).  If the
> record hasn't changed the update occurs.  If there has been a change the
> update will fail.  When it fails you read the record for display again
with
> an error message and allow the user to attempt to change the record again.
>
> Paul
>
> -- 
> Paul Morgan
> Senior Programmer Analyst - Retail
> J. Jill Group
> 100 Birch Pond Drive, PO Box 2009
> Tilton, NH 03276-2009
> Phone: (603) 266-2117
> Fax:   (603) 266-2333
>
> Ron wrote
>
> > Do you have a better suggestion than the record lock then in a web app?
> > That's not being sarcastic either, I'm open to suggestions :)
>
>