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



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.



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.