Re: [EXTERNAL] Re: Problem with SQL variables

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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-