× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Tim,

While our implementation strategies may differ, I think there is a good
case to be made for keeping database-related logic in the same address
space as the DBMS. Keep it separate from logic that is controlling the user
interface.

Our local IBM i professionals group recently listened to a presentation
from developer who implemented business logic in Java. A year later he was
asked to implement the same logic in PHP. He finally wised up and
implemented the logic in IBM i stored procedures and user-defined functions.

Who knows if the async-await keywords that were added to Node.js are the
final landing point as far as syntax is concerned. Maybe all of that logic
will need to be replaced 1 year from now.

Some values in a database are derived from calculations that must access
other values that are stored in the database.Add data validations. Add
referential integrity constraints. Add cascading deletes when parent
records are deleted. Add not allowing parents records to be deleted when
child records exist.

We recently deployed a system where schools enter requests for
"transportation" for field trips and similar activities. The request not
only entails field-level validation before a row can be inserted, but
inserting a new row into the transportation-request table triggers an
asynchronous process that adds records that delineate an itinerary (bus
stop locations), adds bus records for the number of buses that will be
required (based on the number of students, the amount of gear that they
will be traveling with, and the capacities of the buses), invokes a google
web service that estimates the number of miles that will be traveled (for
billing purposes), adds rows to a table that is used to distribute costs
across GL account numbers. The process might entail sending a text message
or email message to trip sponsors.

I'm suggesting that database events (reads, writes, updates, and deletes)
often trigger other database events and logic that depends on values stored
in the database. I'm suggesting that's the type of logic that should NOT be
placed in a Node.js process, nor any other software component that is
handling the user interface.




On Tue, Mar 20, 2018 at 11:02 AM, Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

"I'd be curious to learn what you end up with concerning deploying of
compiled RPG objects."
I hadn't got round to tackling that one yet, I was hoping that the RPM
announcement might help in that respect, so that it might be possible to
package source and binaries up in RPM. The client I'm looking at this for
has no change management whatsoever except what I've done for then in
BitBucket and does everything by hand so even having an automated build
that created a save file they could transfer to their live machine by hand
would be a considerable help!

"Could you post an example of how you do this?"
So, first off I'm referring to business logic in the sense of basic
interactive "CRUD+" stuff, not batch processes and, in the applications we
most frequently do, the business logic is usually making calculations over
sets of data or enforcing business rules so it's nothing fancy or clever
and mostly stuff that can be done in a single SQL statement. The CRUDdy
parts are broken out into individual stored procedures, plus usually an
additional one for the paged queries and any that make sense as standalone
APIs, like CUST_SP_Block(...), CUST_SP_UnBlock(...),
CUST_SP_AddAddress(...) etc., etc.. So each SP is usually quite compact,
any shared logic, like validation is written as a separate SP and called
from where it's needed (like insert, update or just on its own if the
client wants to validate etc.). Our webservice simply executes the stored
procedure POSTed to (after authority checking of course!) and passes the
parameters in the request body, for example:
/web-service/CUST_SP_Query --> executes call CUST_SP_Query(PAGE => 1,
PAGE_SIZE => 15, SEARCH_CRITERA => "Smith")
/web-service/CUST_SP_Add --> executes call CUST_SP_Add(....)
/web-service/CUST_SP_Delete....etc.
/web-service/CUST_SP_Block
/web-service/CUST_SP_AddAddress

The webservice does nothing more than provide an interface between the
RESTful(ish!) API and the stored procedures and the request body always has
the same format {"inputParms": {"PAGE": 1, "PAGE_SIZE": 15,
"SEARCH_CRITERA": "Smith"}}, where the input parameters match those of the
stored procedure. The output is also always the same structure, basic data
about the SP called, the input and output parameters and then an array of
result sets, each containing the column meta-data and the rows themselves.
This makes life very easy for the client, because you always know what
shape the data will be so you can often make quite generic code to
manipulate and display it. It also makes it very easy to add new web
services, just write a SP and it's automatically available. as an API
endpoint.

We consider that the client of the API is dumb and shouldn't manipulate
the data other than for presentation purposes. This means, for example, if
the database contains a net and VAT amount, then the SQL will calculate the
gross amount if it's needed, rather than have the *script client do this.
Similarly, if the client needs subtotalling then this is also done in SQL
not by the client summing the data themselves. Also, most interpretation of
the data is done concretely in the server side SQL, so if "older than 30
days" means overdue, then the SQL will look up the definition of overdue
and provide a flag in the data that the UI will use.

When it comes to updating/inserting/deleting rows in the database, this is
again encapsulated into a stored procedure which uses the "begin atomic"
statement to make sure that multiple updates are encapsulated and done
safely.

Where I've written "client" above, you could read "server" if your server
was a RESTful NodeJs application, for example, firing basic SQL queries at
the database. I would argue that the NodeJs server side code should not
manipulate the data at all either but the SQL should do it. Therefore each
RESTful endpoint in NodeJs just executes a stored procedure, which if you
boil that down further you come to the approach above because you might as
make it generic then.

Another advantage I see with this way is that it is more secure. If your
server can fire arbitrary SQLs at the database then it has to have
sufficient authority to the database and thus any compromise of the server
gives a free reign to the intruder. By encapsulating all of the logic in
stored procedures and securing the stored procedures correctly the user id
under which the web service is running only has to have *USE rights to the
SPs and nothing else, the SPs can run under *OWNER, meaning you never have
to give the webservice access to the raw database.

"I wouldn't have such a distaste for this approach if SQL stored
procs were easier to debug. I am a big fan of step debuggers."
I guess you already know this, but SPs are just C programs under the
covers, so the ordinary debugger can be used to step through the SQL code
or the underlying C code, you have to use a service job though. In any
case, I always develop and test my SQL components interactively before
pasting them into the SP body, which cuts down on the debugging effort in
the first place, I can't remember the last time I actually had to debug a
SP.

So far our applications haven't required us to use RPG stored procedures
for the backend, apart from in the encrypting and decrypting of user
tokens, which uses the system APIs. In terms of complicated business logic
that you would see as being problematic done in SQL, give me an example and
I'll try to explain how I'd deal with it (or suddenly realise I never
thought it it 😲!)



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.