Re: CRUD stored procedure question

[Nathan Andelin <nandelin@xxxxxxxxx>]

It sounds like stored procedures may not be the best interface for what you
are trying to do. If you're considering passing "CRT", "UPD", "DLT" in the
procedure interface, then your stored procedure program would be checking
for those conditions, (i.e. when operation = "CRT", then evoke the "create"
procedure, and such. That's pretty innocuous, but boring...'). And as you
pointed out, the interface can become inordinately complicated when records
contain many columns. If you go with the other suggestion of exporting
procedures interfaces such as Invoice_create() and perhaps supplementing
with procedure interfaces that set field values such as setHireDate(...),
that may also end up being inordinately complicated. You end up needing to
provide documentation for each procedure interface.

You might be better off by implementing data validation and business rules
that pertain to CRUD operations in database triggers. I came up with a
derivative of the idea, which I call database event handlers, which are
summarized here:

http://rd.radile.com/rdweb/info2/ibmiapp04.html




On Mon, Nov 22, 2021 at 3:19 PM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

> I am wondering how other people do CRUD stored procedures.
> We have some CRUD stored procedures and I am wondering how you guys handle
> the Update part.  We are using RPG.  Creating a procedure with the input
> parms of an action code 'CRT', 'UPD', 'DLT' and all of the columns to the
> table.  The CRT and DLT are easy.  But the UPD.  The caller may not know
> all of the values of all the columns.  They know the keys and the values
> they want updated.  How do you handle the values that are not to be
> changed?
> We came up with if the column is char if the value is a pipe  change to
> blanks if blanks no change. other values then change it.  Same type of
> thing with numeric values -99999999 = zero, zero no change other  values
> then change.
> For a small table this is okay but you get a table with 25+ columns, what a
> programming pain on both sides.
>
> Is there a better way?
>
> --
> KCrawford