Thanks for your reply Vern
I'm beginning to think that I have no option but to change my thought process as to how to update the control file
It looks like sql variables are NOT the direction that I can go
Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone
-------- Original message --------
From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Date: 1/7/21 20:08 (GMT-05:00)
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: Problem with SQL variables
Just arose out of the depths of work - I have a feeling this is related
to connection to the database - when you STRSQL you are getting a new
connection, right? I just created one of these in STRSQL - gave it a
value. Tried to use it in ACS - not null. Then in ACS I set it to a
different value, it was then not null, had that value.
Went back to STRSQL, displayed it, it had the value I set it to in the
first STRSQL session (I'd exited from the first one).
So the value in STRSQL was preserved between evocations. Just looked at
some google hits, says that the scope is to one SQL session, other
session can USE the variable (I think that means set it, maybe drop it).
But the value from another "session" is not visible.
So the question is, just what is an "SQL session"? It looks like it is
not the job level - when I set the variable in STRSQL, then display it,
then exit and come back in, the variable is null.
This fits behavior I've but not followed up in ACS - I have a couple
variables I use for stuff, when I have set them and then are done, I
close the connection, maybe by exiting. When I come back, they have no
value, I have to reset them.
Just confirming Charles' thoughts - now what to do about it? Keep
everything within the same connection. Because it seems each RUNSQL is
in a separate connection, this might happen -
1. Create Variable in one RUNSQL - works fine
2. Set it in another RUNSQL - probably works - the definition is
available to everyone - THAT scope is global!
3. Try to use in in RUNSQL or RUNSQLSTM or whatever - NULL!
So what about embedded in RPG? Is the connection established at the
start, so whatever you do there will work? Each EXEC SQL is done within
the connected session?
I did catch a google hit about this, in SQL Server - usually I don't
trust how other RDBMS work, but many things ARE the same. And it said
connection and session basically and 1 to 1, each describes a different
layer or function, we might say.
CL just ain't gonna work that I can see!
Regards
Vern
On 1/7/2021 4:22 PM, Alan Shore via MIDRANGE-L wrote:
Well - I changed my CLLE program to use the following for the insert
RUNSQL SQL('insert into NBJDTA.HRCTRLFP +
values ( +
nbjdta.HIRADINTERFACENAMESHPD, +
nbjdta.HIRADFILENAMESHPD, +
nbjdta.HIRADAS400FILENAMESHPD, +
nbjdta.HIRADBATCH#SHPD, +
nbjdta.HIRADSTATUSSHPD, +
nbjdta.HIRADBATCHTIMESTAMPSHPD, +
nbjdta.HIRADODITIMESTAMPSHPD +
)') COMMIT(*NONE)
(I found out that If I'm populating all the columns (which I am) there is no need to specify the coulmns in the insert
However, it still fails with the following error
Null values not allowed in column or variable HRINERFNAM.
The sound you hear is me banging my head against the brick wall
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, January 7, 2021 4:49 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: Problem with SQL variables
I'm thinking that session may not the whole job...
Looks like RUNSQLSTM may be OPM has is RUNSQL...unless they are calling something ILE behind the scenes.
That should rule out activation groups.
But perhaps RUNSQLSTM starts/ends a session as does RUNSQL.
idk..never really made much use of global variables...
Charles
-snip-
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
