Re: Stored Procedure with Temporary Table

Have you considered making the RPG functions callable as UDFs?

Not really. But only because my results are going to be counted by hundreds
and not by thousands. So optimization hasn't been a very big concern for
me. BUT it is definitely something I will think about in the future.

My motto: "If your using a cursor, you're probably doing something
wrong." Sometimes there's no other choice, but for RPG (or other
procedural) programmers used to record at a time, cursors tend to be
their first choice and they shouldn't be.

I am not sure I follow this because I thought anytime you read data in SQL
there is a cursor involved somewhere. It may be a forward-only cursor that
preforms very quickly, but still a cursor. So how can you read records
without a cursor? Maybe I am just showing my ignorance here...

Are you referring to a preference for native file access to SQL?


On Tue, May 24, 2011 at 10:20 AM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:

Have you considered making the RPG functions callable as UDFs?

Then you could create a view that used the UDFs and simply return the
data from that.

Performance would be much better than your current process...inserting
a row at a time is a particularly poor performer...blocked inserts
would be better....especially when pair with blocked fetches...

My motto: "If your using a cursor, you're probably doing something
wrong." Sometimes there's no other choice, but for RPG (or other
procedural) programmers used to record at a time, cursors tend to be
their first choice and they shouldn't be.

Charles


On Tue, May 24, 2011 at 9:38 AM, Matt Lavinder
<mlavinder@xxxxxxxxxxxxxxxxxxx> wrote:

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.

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