Re: Stored Procedure with Temporary Table

Matt,
I'm guessing that you are using an External Stored Procedure and not an
SQL Stored Procedure correct? You'd almost have to be from what I've
read. So if the temporary file creation was in the RPG program that is
used as the external stored procedure then any time you recompile the
stored procedure the temporary table structure would get updated properly.
Then the other RPG service programs you call can write to that temporary
table because they are in the same job. I use your same structure in my
customer search procedure except I'm holding my data in dynamic arrays
instead of a temporary table. I think this performs better for my
purposes, not sure about yours.

I am also at V5R4 for now, and have an rpgle program using SQLCLI to call
this procedure, and our java guys are now calling it from their windows
system.

Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



Matt Lavinder <mlavinder@xxxxxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/24/2011 11:13 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Stored Procedure with Temporary Table






I have done this sort of thing before (especially in MS SQL Server). I
didn't use this approach as I was trying to integrate the existing RPG
procedures into the process. That said, if I combine your suggestion with
Charles's suggestion of wrapping the procedures in UDF's, then I have a
strategy that would work for me. But creating wrappers for the RPG
procedures just adds extra steps and complexity to accomplish the same
task.

What is the benefit to coding the whole thing in SQL? I am asking
because
I probably don't fully understand the benefits and pitfalls or both
approaches.


On Tue, May 24, 2011 at 10:51 AM, Schutte, Michael D <
Michael_Schutte@xxxxxxxxxxxx> wrote:

In a previous post you said that your stored procedure was calling an
SQLRPGLE service program. What I'm suggesting is removing the need of

the

service program and just put the code in the stored procedure. See the
procedure here.

http://code.midrange.com/ab75e05ad1.html



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [

mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Matt Lavinder
Sent: Tuesday, May 24, 2011 9:38 AM
To: RPG programming on the IBM i / System i
Subject: Re: Stored Procedure with Temporary Table

It is important to understand that everything is working at this point

so

my initial problem has been resoled. This is now a discussion of best
practices and recommendations. That being said...

Are you against writing the whole thing in the stored procedure?

I can give you a sample of where I'm doing the same thing.

I think that is what I am doing. You can send me an example of what you
are
talking about so I can see if your approach is different, but my stored
procedure creates the temporary table, populates it, and returns it all
within the same module. If you are talking about using an SQL procedure
instead of a System procedure, I can't do it that way. Part of

populating

my temporary table is to "massage" the data a little using some existing
RPG
logic. Here's my stored procedure in a nutshell:

1. Initialize module and create my temporary table
2. Use initial SQL query to pull the data I need.
3. Loop through that cursor. Format data in certain columns using

existing

RPG procedures.
4. Once a row is processed, use INSERT statement to add it to temporary
table.
5. Finally, do final query on temporary table and return result set.

That last step is where my issue was. When I ran in Run SQL Scripts, I
would run the procedure and it would return the wrong columns. But if I
queried the temporary table directly, it had been created correctly.

It also is worth mentioning that this procedure is being written to be
called by a PHP script.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only

for

the person or entity to which it is addressed. Any unauthorized review,

use,

disclosure or distribution is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and

destroy

all copies of the original message and any attachments.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.