Re: Stored Procedure with Temporary Table -- RPG400-L

I disconnected from the SQL Session I had open and reconnected. I am not
sure if this creates a new job when it reconnects or if it connects to a
pool of existing jobs. I thought it created a new job.

I may trying recreating the issue later this week, and if I do, I will
definitely post the steps here.

For the record, we are running V5R4 - as that could certainly make a
difference.

On Tue, May 24, 2011 at 9:38 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:

Just try to end your job or run your program within another job. My be then
your problem is solved.
Stored Procedures run in the default activation group and a default
activation group can only be ended with the end of the job.
In this way you may still running the old program/stored procedure version.

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!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
Im
Auftrag von Matt Lavinder
Gesendet: Tuesday, 24. May 2011 15:00
An: RPG programming on the IBM i / System i
Betreff: Re: Stored Procedure with Temporary Table

I should have made it clear that I had already recompiled the module on
numerous occasions with no luck. Thus the reason for my email in the first
place...

But I had an idea. I was getting a warning that the column definitions for
my temporary table could not be found (which isn't unusual- it is a
temporary table). Usually I just ignore these. But I suspected it
mattered
in this case and the program was holding on to the old definition. So I
created a CL program that uses RUNSQLSTM to create my temporary table and
then compiles my module. This way it finds the definition of my table in
QTEMP. This seemed to fix the issue.

I have never ran into this before. Usually those warnings can be ignored
and
I have no issues. The only thing I am doing differently here is I am using
a service program. So maybe (most likely?) it has something to do with
that. I have my reasons for using a service program, but I could adjust my
approach if returning result sets from service programs is not recommended.

Anyone have any additional thoughts? I'd like to have a better
understanding of the issue.

On Tue, May 24, 2011 at 7:38 AM, Vern Hamberg <vhamberg@xxxxxxxxxxx>
wrote:

Neill

I think you have hit it - when you compile embedded, yes, it IS changed
to native code - no AS IF about it. All those "exec sql"s are changed to
program calls. And there are a bunch of I-specs generated. Those I-specs
contain the fields (columns) in any tables.

This is all visible in the compile listing, if you have one.

So this makes sense, to recompile the module.

Vern

On 5/23/2011 5:00 PM, Neill Harper wrote:

Try recompiling the module that contains the SQLRPGLE procedure. I have
heard of in the past but never actually seen issues relating the

embedded

SQL and *

It's almost as if the embedded SQL is compiled to native code and * is

taken

as whatever the columns are when the program is compiled.

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

rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Matt Lavinder
Sent: 23 May 2011 22:06
To: rpg400-l@xxxxxxxxxxxx
Subject: Stored Procedure with Temporary Table

I have a SQLRPGLE procedure (service program) that I am calling from a
stored procedure. I declare a global temporary table, do an SQL to

insert

data, process the data and modify the data, do a select all on the

temporary

table, and use SET RESULT SETS to set the cursor to be returned from

the

stored procedure to that final select.

It was all working fine until a changed the layout of the temporary

table.

When I test the procedure in RUN SQL Scripts, it returns the results

without

my new column. If I then query my temporary table directly from the

same

session, I see the new column in the temporary table itself. So it

appears

that the table is defined correctly, but somewhere the procedure is

using

a

cached(?) copy of the table definition when it returns the results. I

have

never seen this before.

Any thoughts?

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

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