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


  1.  Home
  2. MIDRANGE-L
  3. January 2021

RE: [EXTERNAL] Re: Problem with SQL variables

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

On Thu, Jan 7, 2021 at 2:27 PM Alan Shore via MIDRANGE-L < midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Thanks for ALL your replies Rob
Im learning a huge $#!+ load at the moment In STRSQL I ran values
nbjdta.HIRADINTERFACENAMEOPENAR by itself It displayed the variable -
but had null value Is that because I am using STRSQL?
It looks like I have to change my insert to what you suggested earlier
in this e-mail to the following, but using the correct column names
Boy - the times when I appear dense, thick, moronic seem to be hitting
me more often nowadays


0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP ( +
0101.00 col1, col2, col3, col4, col5, col6, col6
0102.00 values (
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD +
0108.00 )') COMMIT(*NONE)


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 Rob Berendt
Sent: Thursday, January 7, 2021 4:17 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

I suspect we were both thinking they were more like a *DTAARA. They
obviously are not.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Rob Berendt
Sent: Thursday, January 7, 2021 4:14 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Problem with SQL variables


https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzvalu
estmt.htm

Instead of using SELECT you use VALUES.
Often used for displaying system registers.
values current date
instead of
select current date from sysibm.sysdummy1

But my suggestion to display one with VALUES displayed my ignorance of
sql variables. I just discovered they are only a transitional value
stored in memory in a service program.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 4:08 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do
not click links or open attachments unless you recognize the sender
and know the content is safe.


Thanks for your reply Rob
Oh - you meant SYSIBM.SYSDUMMY1
That's how I can display the contents of the SQL variables I ran the
SQL select * from qsys2.sysvariables where variable_schema='NBJDTA'
This is nice (didn't know about it)
Then you say
Pick one and display the contents with:
Values rob. HIRADAS400FILENAMESHPD

Im not seeing how to do this - In STRSQL, where?

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 Rob Berendt
Sent: Thursday, January 7, 2021 3:40 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

Read the bottom as to your issue but let me ramble first...

I didn't say the file on the insert is a dummy file I said get rid of
the dummy file as in replace
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP (select +
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD from +
0108.00 SYSIBM.SYSDUMMY1)') COMMIT(*NONE)
With
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP ( +
0101.00 col1, col2, col3, col4, col5, col6, col6
0102.00 values (
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD +
0108.00 )') COMMIT(*NONE)

But, let's see if your variables exist:

select *
from qsys2.sysvariables
where variable_schema='ROB'
;

Pick one and display the contents with:
Values rob. HIRADAS400FILENAMESHPD

There's no reason (for this process) to use SYSDUMMY1. It brings
nothing to the party.

WHAT THE ISSUE IS:
<snip>
Session scope: Global variables have a session scope. This means that
although they are available to all sessions that are active on the
database, their value is private for each session.
</snip>

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzcvar
iable.htm If I created that variable with Run SQL Scripts and then I
go into STRSQL and do Values rob. HIRADAS400FILENAMESHPD I get a null
value.

So now you are probably wondering: What is the definition of a
session scope? A job? What?
Good luck with that exercise.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 3:01 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do
not click links or open attachments unless you recognize the sender
and know the content is safe.


Hi Rob
Thanks for your reply
The file that these variables are being entered into is not a dummy
file, but a control file, which is to be used by other programs
However - looking at your code doesn't seem to be that much different
from mine Except in mine - when attempting to insert the SQL variables
- its failing because the variable is null - when according to my
understanding - it shouldn't be


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 Rob Berendt
Sent: Thursday, January 7, 2021 2:55 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

Forget using the dummy file and use the variables directly.
This is a working run sql scripts session:

CREATE or REPLACE VARIABLE rob.HIRADAS400FILENAMESHPD CHARACTER (10);
SET rob.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
values rob.HIRADAS400FILENAMESHPD;
create table rob.HRCTRLFP (
testcolOne char(10)
);
insert into rob.hrctrlfp (
testcolOne)
values (
rob.HIRADAS400FILENAMESHPD
);
select * from rob.hrctrlfp;



Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 2:35 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: Problem with SQL variables

CAUTION: This email originated from outside of the organization. Do
not click links or open attachments unless you recognize the sender
and know the content is safe.


Hi everyone
We are on V7r3
I thought I understood how SQL variables worked Now Im thinking that I
don't Im testing a CLLE program Here is the code that I am having a
problem with

0093.00 /* The file NBJDTA/HRSHIPPEDD is the delta for the Customer
extract */
0094.00 /* This first job initializes the SQL variables for the
Customer extract */
0095.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJA03A)
+
0096.00 COMMIT(*NONE)
0097.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJB03A)
+
0098.00 COMMIT(*NONE)
0099.00 /* The next job inserts the SQL Variables into the control file
*/
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP (select +
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD from +
0108.00 SYSIBM.SYSDUMMY1)') COMMIT(*NONE)
0109.00

Here is HRVARJA03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADAS400FILENAMESHPD CHARACTER (10);
SET NBJDTA.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
CREATE or REPLACE VARIABLE NBJDTA.HIRADSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADSTATUSSHPD = 'N';

Here is HRVARJB03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADINTERFACENAMESHPD CHARACTER (50);
SET NBJDTA.HIRADINTERFACENAMESHPD =
'HIGH RADIUS SHIPMENT EXTRACT';
CREATE or REPLACE VARIABLE NBJDTA.HIRADFILENAMESHPD CHARACTER (100);
SET NBJDTA.HIRADFILENAMESHPD = ' ';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCH#SHPD DECIMAL (12,0);
SET NBJDTA.HIRADBATCH#SHPD =
( NBTYGPL.NEXTNUMBERSQL('HIGH RADIUS INTERFACE'));
CREATE or REPLACE VARIABLE NBJDTA.HIRADFINALSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADFINALSTATUSSHPD = 'U';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCHTIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADBATCHTIMESTAMPSHPD =
CURRENT_TIMESTAMP;
CREATE or REPLACE VARIABLE NBJDTA.HIRADODITIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADODITIMESTAMPSHPD =
CURRENT_TIMESTAMP;

When I test this code (interactively) - both HRVARJA03A & HRVARJB03A
are successful Well - the spool file that is generated shows the
variables have been created and populated However - the insert fails
with Null values not allowed in column or variable HRINERFNAM.
Which is being populated from the variable
nbjdta.HIRADINTERFACENAMESHPD

If I then run the following in a STRSQL session select
nbjdta.HIRADINTERFACENAMESHPD, nbjdta.HIRADFILENAMESHPD,
nbjdta.HIRADAS400FILENAMESHPD, nbjdta.HIRADBATCH#SHPD,
nbjdta.HIRADSTATUSSHPD, nbjdta.HIRADBATCHTIMESTAMPSHPD,
nbjdta.HIRADODITIMESTAMPSHPD from
SYSIBM.SYSDUMMY1
All the SQL variables come back null
Which verifies the error - but it NOT what I expected If anyone can
point out what I am doing wrong - or explain why this wont work - I
would be VERY grateful

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

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

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

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

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

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.