RE: Data access across libraries

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

Ahhh.  Herein lies the rub.  The idea that it's easier to embed SQL in the
client.  Of course, with today's architecture, it is easier.  But let's see
if that's not fixable.

Are you using SQL primarily for queries, or are you doing a lot of updates
as well?  This isn't really germane, but it's an issue down the road.

Okay, how do we make it possible to have a client/server environment as
flexible as JDBC?  Well, let's start with a query.  A query is a thing that
takes a set of parameters and returns a collection of data objects.  Let's
abstract that one level:

public interface DataObject {
  void addField(String fieldName, Object field);
}

public interface DataCollection {
  void addData(DataObject data);
  boolean next();
  Object getField(String fieldName);
}

public interface ParmSet {
  void addParm(Object parm);
  Vector getParms();
}

public interface Query {
  DataCollection execute(int type, ParmSet parmSet);
}

Okay, cool.  What's a ParmSet?  For now, just a Vector of parameters.  These
are the things you would plug into your prepared statement.  Now, in your
code, you do the following:

Instead of:

  String mySqlStatement = "SELECT * FROM ITEMFILE WHERE ITEMNUMBER = '" +
item + "'";
  ResultSet rs = statement.executeQuery(mySqlStatement);

You write:

  ParmSet parms = new ParmSet();
  parms.addParm(item);
  DataCollection data = query.execute(1, parms);

The constant "1" identifies the query to run.  Now you need a specific
instance of the Query class.  For testing, you can start by simply executing
your normal JDBC statements:

public DataCollection execute(int type, ParmSet parmSet)
{
  String mySqlStatement = formatSql(type, parmSet);
  ResultSet rs = statement.executeQuery(mySqlStatement);
  return new DataCollection(rs);
}

private String formatSql(int type, ParmSet parmSet)
{
  String sql = null;

  switch type
  {
    case 1:  String item = (String) parmSet.getParms().elementAt(0);
             sql = "SELECT * FROM ITEMFILE WHERE ITEMNUMBER = '" + item +
"'";
             break;
    case 2: (...)
  }

  return sql;
}

Notice what happens here?  Now, with just a little bit of work, you have
moved all of your SQL statements into a single class that can be easily
modified.  The tricky bit is the statement "new DataCollection(rs)", which
creates a DataCollection out of a ResultSet.  This basically puts a
DataCollection wrapper around the ResultSet; I can go into detail about that
code, but it's relatively easy.

So why did I do all this?  Well, the first thing I can do is that I can
easily change my DataCollection object to submit these queries to the AS/400
to speed up the access:

public DataCollection execute(int type, ParmSet parmSet)
{
  String mySqlStatement = formatSql(type, parmSet);
  DataCollection collection = remoteSql.executeQuery(mySqlStatement);
  return collection;
}

The remoteSql object is a new class that takes a formatted SQL statement and
submits it to the AS/400, retrieves the data and places it in a
DataCollection object.  I could also pass this statement to a stored
procedure which executes it on the fly.  Either way, I should see some
immediate speed increases over the client-side SQL.

But more importantly, I can now think about replacing the SQL entirely with
a call to a server.  And I don't even have to do it for all the statements;
I can pick and choose which statements are better served locally (such as a
statement that accesses non-AS/400 data), which are better served by a
stored procedure, and which are better served by true client/server design.
This technique of abstracting and localizing data access will allow you to
both be able to meet market demand while at the same time allowing you to
take advantage of the AS/400's power when needed.

I realize this is a rather high level design, but it will work.  If anybody
is interested in pursuing this concept formally, let me know, and we'll see
if we can't cobble something together and get it up on the PBD website, and
maybe even submit it to JTOpen if they're interested.

Joe


> -----Original Message-----
> From: Buck Calabro
>
> Market forces being what they are, our client code MUST contain
> raw SQL just to deploy it quickly enough.
>
> Nobody thinks this is a good thing, but there isn't a whole lot we can do
> about it.