Generic data access procedure on AppServer

Posted by Tung on 12-Jul-2010 09:29

Hi, is it possible to write a generic data access procedure to run on Appserver?

If I was using just 10.2B and not Appserver, I could define a temp-table LIKE a table, then the dataset, attach a data source and use a query with QUERY-PREPARE to write whatever query I want. Or even use an include file with named arguments for the DB table, FOR EACH condition, fields to buffer-copy, etc. FOR EACH {&query} NO-LOCK ...

But using Appserver and with no databases connected, I'm thinking I'd have to convert every table into a temp-table and do it manually! You can import a tt.i into a binding source or convert a tt.i into a Business for Logic diagram but you can't convert a database table into a temp-table. I had a look at dynamic temp-tables but again they seem to work best if you're connected to a database.

I know I should just bite the bullet and do it but I thought I'd ask you lot in case I've got it wrong.


All Replies

Posted by Thomas Mercer-Hursh on 12-Jul-2010 11:28

What are you actually trying to accomplish here?  Yes, it is possible to do dynamic queries and dynamic temp-tables so that one piece of code could access multiple tables, but it is going to be a whole lot of programming and a whole lot of run-time effort compared to a simple data access routine built specifically for each table.  Do it for the table and you can make intelligent use of indices, make relational dependency checks, check validity of records to be stored, and actually have some code which it is possible to read and understand and analyze with tools

Posted by Tung on 13-Jul-2010 04:13

I wanted a reusable data access routine to reduce the amount of code and work. I was hoping to avoid converting all of our 190 tables into temp-table include files, which would need to be updated whenever a DB change was made. But as you said, using dynamic queries and temp-tables has higher run-time costs.

I've got it working with this:

RUN sSysuserGetData.p ON hdlAppServer (INPUT "FOR EACH sysuser WHERE sysuser.sysuser = 'joe' ", OUTPUT DATASET dsSysuser).

and in the data access routine:



QUERY qSysuser:QUERY-PREPARE (ipQuery).
DATASET dsSysuser:FILL().

My only issue with doing it this way is the overhead of a prodataset for a small number of records but I can live with it. Is this similar to how you did it?

Posted by Thomas Mercer-Hursh on 13-Jul-2010 11:29

I wouldn't do it at all.  Coding a module like this is a trivial effort compared to the overall effort in maintaining a system and one which is easily automated to a large extent.  With per table code you have a place to check relational consistency and do appropriate validations as well as a place to make any mappings from stored from to the form needed by your business logic.  You also have code which can be analysed for data flows, e.g, by my ABL2UML tool.  With your generic code between the business logic and the database, you have no way of telling what connection any piece of code has to any data in the database.  No way to tell what's used and what isn't.  No way to tell all of the places that touch a particular field or where updates occur versus where data is consumed.  This is worth saving yourself writing a few cookie-cutter routines?

Posted by Admin on 13-Jul-2010 15:07

Agreed! Data Access should be static and allow for customizations. In a good layered application the AppServer does not just send chunks from the DB unfiltered to a client. Where would you cummulate data when there is only a single general purpose data access procedure? Not to speak about validating data before it's written back to the DB.

Good templates, super classes and/or code generation is the key to success.

Posted by Tim Kuehn on 13-Jul-2010 16:01

Tung wrote:

I've got it working with this:

RUN sSysuserGetData.p ON hdlAppServer (INPUT "FOR EACH sysuser WHERE sysuser.sysuser = 'joe' ", OUTPUT DATASET dsSysuser).


will run a lot faster. You'll need to make sure the PDS is cleared before-hand, or in the DA routine.

Posted by Admin on 13-Jul-2010 23:19

>> RUN sSysuserGetData.p ON hdlAppServer (INPUT "FOR EACH sysuser WHERE sysuser.sysuser = 'joe' ", OUTPUT DATASET dsSysuser).


will run a lot faster.

BY-REFERENCE becomes by value when passed across the AppServer.

Posted by Tung on 14-Jul-2010 04:04

In my example I don't think I was clear enough. I'm getting a user's username during login and if there's a sysuser record, it's valid. Are you saying I should be passing the username as an input parameter into sSysuserGetData.p and that program should set up the query like:

QUERY qSysuser:QUERY-PREPARE ("FOR EACH sysuser WHERE sysuser.sysuser = '" + cUser + " ' ").

Then if I wanted a sysuser record using the username and password expiry date, I'd need another procedure sSysuserPasswordExpiryGetData.p that has two input parameters?

I understand now why a generic data access routine is a bad idea but I'm not sure how to do it they way you advised because I don't know what your "cookie-cutter routines" or "templates" look like.

Posted by Thomas Mercer-Hursh on 14-Jul-2010 11:31

"Cookie cutter" depends on what kind of cookies you want ... i.e., once you figure out what pattern works for you, you just apply it over and over again.

One common pattern is a single object which has all the methods needed for all of the kinds of retrievals you might want on a particular table and then a series of small façade procedures.  The facade procedures is what you actually run on the AppServer since we currently can't NEW an object and run a method in the same call and the façade procedure does whatever it needs to do with the parameters (often nothing) and calls the appropriate method.  Depending on the number of objects and AppServers, the object may or may not be already running in the session.

Posted by Tim Kuehn on 14-Jul-2010 16:06

mikefe wrote:

>> RUN sSysuserGetData.p ON hdlAppServer (INPUT "FOR EACH sysuser WHERE sysuser.sysuser = 'joe' ", OUTPUT DATASET dsSysuser).


will run a lot faster.

BY-REFERENCE becomes by value when passed across the AppServer.

Indeed it does - I missed the appserver handle reference...    

Posted by Tung on 15-Jul-2010 07:33

What "appropriate method" does the facade procedure call? I thought Appserver data access procedures would be procedural, not OOABL to NEW object and use its methods.

Posted by Thomas Mercer-Hursh on 15-Jul-2010 11:48

The method for which it is a façade.  I.e., you might have a class or a procedure which had a dozen different ways to find and return either a single record or a set of records according to some criteria or another.  This represents all of the ways that table is used in the entire application.  Each "way" is exposed as a method of the class or an IP on a PP.  The façade procedure is run by the client and news or runs the class or procedure or connects to an already running instance, and then executes the method or IP to obtain the desired record(s) which it then returns to the client and terminates.

Posted by Tung on 19-Jul-2010 11:15

I managed to get my head around the idea (eventually, it's very long winded way of doing something that should be simple!). Is there a preference and/or reason to use class & methods instead of IP & PPs on the server side?

One problem is what if I want to return a few fields, intead of the whole table? I don't see an easy way to do it except using different temp-tables and queries with a FIELDS/EXCEPT list.

Posted by Thomas Mercer-Hursh on 19-Jul-2010 11:41

OO or not OO is a preference.  I think OO has a number of advantages.  As I say in my upcoming Exchange presentation on OO

"It is believed that the OO paradigm improves quality by encapsulating logic in easily tested units, promotes code re-use, provides a mechanism for easy and well-controlled extensibility, reduces production error rates by catching errors at compilation, results in a more maintainable system, reduces large problems to simpler components, and provides a more natural relationship between code and the real world."

But, with AppServer, the façade actually run still has to be a procedure (I think this will change in 11.0).

There is no problem returning a few fields, either for one record or many.  You just need to define an interface for that.

Posted by Admin on 19-Jul-2010 11:44

At runtime there is no fundamental difference between persistent procedures and object instances.

It's more a question of a programming model/paradigm. One big advantage of objects is strong typing and much more compile time validation that elimates many runtime errors.

For returning just a few fields, you may go with the whole TT, but only populate few fields. Check the parameters of the ATTACH-DATA-SOURCE parameter for details (I don't have a reference at hand right now).

Posted by Tung on 20-Jul-2010 06:27

I decided to go with objects on the server side. And instead of using interfaces, I use two input parameters to specify the fields to exclude or include in the ATTACH-DATA-SOURCE method.

Thanks for everyone's comments and stopping me going down the wrong road!

This thread is closed