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


  1.  Home
  2. RPG400-L
  3. November 2021

Re: CRUD stored procedure question

Hi Kevin,

I managed to track it down in the "IBM SQL Reference V7R3.pdf" manual:

Chapter 7 Statements > UPDATE > SET assignment clause > expression > “References to host variables” on page 152 > under the "Non-Java variable references" it says

"Each host-identifier must be declared in the source program. The variable
designated by the second host-identifier is called an indicator variable and must have
a data type of small integer. Indicator variables appear in two forms: normal
indicator variables and extended indicator variables."

And further down it says

"Extended indicator variables are limited to input host variables. Their purposes are:
v Specify a non-null value. A 0 (zero) or positive value specifies that the associated
host identifier provides the value of this host variable reference.
v Specify the null value. A -1, -2, -3, -4, and -6 value specifies the null value.
v Specify the DEFAULT value. A -5 value specifies that the target column for this
host variable is to be set to its default value.
v Specify an UNASSIGNED value. A -7 value specifies that the target column for
this host variable is to be treated as if it hadn't been specified in the statement.

Extended indicator variables are only enabled if requested."

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /

On 11/23/2021 4:05 PM, K Crawford wrote:
Birgitta, one part of your example confuses me and I can not find any
examples of it.
Update YourTable
Set YourCol1 = Param1 Ind1,
YourCol2 = Param2 ind2,

Do you have a link to a manual that shows how/what the Ind1 is doing? I
have not found anything with Ind1 (smalInt) after the assignment of the
values.

TIA
Kerwin


On Tue, Nov 23, 2021 at 1:42 PM Birgitta Hauser<Hauser@xxxxxxxxxxxxxxx>
wrote:

Embedded SQL or SQL PL doesn't matter.
You have to define an Indicator Variable for each Column and initialize it
with -7.
... and then change the Indicator-Variable to 0 if a value is passed.
In the update-Statement you specify the indicator variable after the
Parameter-Value.
Something like this:

Declare Ind1 SmallInt Default -7;
Declare Ind2 SmallInt Default -7;
....
Declare IndN SmallInt Default 7;

Set Ind1 = Case When Param1 is Not NULL Then 0 Else -7 End,
Ind2 = Case When Param2 is Not NULL Then 0 Else -7 End;
...
IndN = Case When ParamN is Not NULL Then 0 Else -7 End;

Update YourTable
Set YourCol1 = Param1 Ind1,
YourCol2 = Param2 Ind2,
...
YourColN = ParamN Ind2;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tim
Fathers
Sent: Dienstag, 23. November 2021 10:54
To: 'RPG programming on IBM i'<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: CRUD stored procedure question

Birgitta, I'm assuming this is in embedded SQL right? Is there a way to do
this in a pure SQL stored procedure?

Tim.

________________________________
From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Birgitta
Hauser<Hauser@xxxxxxxxxxxxxxx>
Sent: 23 November 2021 06:05
To: 'RPG programming on IBM i'<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: CRUD stored procedure question

You may define your stored procedures with Default values NULL. When ever a
NULL value is passed the appropriate column (value) is not passed.
Instead of using dynamic SQL, you may also use extended indicators.
An indicator variable can be set to -1 if an NULL value has to be updated,
but If you pass -5 the default value is used instead of a NULL value and if
you pass -7 the column value is just ignored.
So my set all indicator variables to -7 first and only for the values
passed
switch the indicator variable to 0.
In this way you are even able to work with static SQL for the update.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles
Wilt
Sent: Montag, 22. November 2021 23:44
To: RPG programming on IBM i<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: CRUD stored procedure question

Seems like an ideal place to use parms with DEFAULT NULL for all non-key
parms..

You'll need to use
PARAMETER STYLE SQL
or PARAMETER STYLE GENERAL WITH NULLS

Personally, I build all my stored procs with PARAMETER STYLE SQL.

Lastly, I hope that your "CRUD" procedure does more than just
Create/update/Delete a row in a particular table.
IMHO, "CRUD" procedures are pretty much worthless.

Rather than CRUD_INVHDR() and CRUD_INVDTL(), I have business routines
INVOICE_Create()
INVOCIE_AddLineItem()

AddlineItem() would for instance, add a record to INVDTL, but also perhaps
update the INVENTORY table or perhaps update a invoice total column in
INVHDR.

HTH,

Charles

On Mon, Nov 22, 2021 at 3:18 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
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email:RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email:RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email:RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email:RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email:RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:https://amazon.midrange.com



As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.