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-