Generic data access procedure on AppServer

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

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:

DEFINE INPUT  PARAMETER ipQuery AS CHARACTER.

DEFINE OUTPUT PARAMETER DATASET FOR dsSysuser.

BUFFER ttSysuser:ATTACH-DATA-SOURCE (DATA-SOURCE srcSysuser:HANDLE).
QUERY qSysuser:QUERY-PREPARE (ipQuery).
DATASET dsSysuser:FILL().
BUFFER ttSysuser:DETACH-DATA-SOURCE().

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?

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?

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.

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).

OUTPUT DATASET dsSysuser BY-REFERENCE

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

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

OUTPUT DATASET dsSysuser BY-REFERENCE

will run a lot faster.

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

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.

"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.

mikefe wrote:
>> RUN sSysuserGetData.p ON hdlAppServer (INPUT "FOR EACH sysuser WHERE sysuser.sysuser = 'joe' ", OUTPUT DATASET dsSysuser).
OUTPUT DATASET dsSysuser BY-REFERENCE
will run a lot faster. 
BY-REFERENCE becomes by value when passed across the AppServer.

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

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.

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.

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.

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.

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).

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!