Right now I am proving that I can do what I am trying to do so I have haven't added the error checking in the code in places where I am sure there will not be an issue (assuming the SQL works correctly). That will be a future pass through the code for cleanup. Right now there is a // WIP - Add error checking after the SQL that I deleted from the original email.

Yes it is the same parameter. There are only 5 records in SCFCONFIG and none of them have blank for a value. If the parm was wrong, it should have returned SQLCOD = 100 - record not found. Instead it returns 0 - successful.

I did not see anything in the joblog but I will check again in a little while (temporarily yanked off on a support issue). However, if there was an error in the joblog, I would have expected an error in SQLCOD or SQLSTATE and both are normal.



-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Thursday, May 12, 2022 1:07 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL in RPGLE problem

First, I wouldn’t code a exec sql without checking at least the SQLCODE after it.

select;
when sqlcode = *zero;
return rtnValue;
when sqlcode = 100;
return ‘*NOTFOUND’;
other;
// do some error logging
return ‘*ERROR’;
endsl;

Something like that.

Second, are you sure, that you are using the same parameter? Did you debug the srvpgm procedure?

*caller for srvpgms is normally OK - but that also means, that each call to the srvpgm proc is opening the data path again.

Did you check the job log?

Von meinem iPhone gesendet

Am 12.05.2022 um 17:45 schrieb smith5646midrange@xxxxxxxxx:
Apologies in advance that this email is so long. I'm just trying to provide all of the detail in one shot.

I have two *PGMs and a *SRVPGM with SQL that are acting weird and I’ve exhausted my knowledge for possible causes. Both PGMs are created with activation group *NEW and the SRVPGM is create with activation group *CALLER.

The one oddity is the file that I am fighting with (SCFCONFIG) is an alias (DDMF) pointing to another machine. This is the command on machine MIDOHIO2 that created the alias. Note that the PF that is points to is on MIDOHIO.
CREATE ALIAS $SCAMS/SCFCONFIG FOR MIDOHIO.$SCAMS.SCFCONFIG; I am
trying to use the alias / DDMF route because the machine that the PF is on will vary because there are two different “host systems” and building SQL strings with variables and then preparing them got to be a royal pain.

The below process is being executed on MIDOHIO2.

Program 1 calls function RetrieveConfigValue in the SRVPGM with a parm of ‘JobQ’.
jobq = RetrieveConfigValue('JobQ'); This properly returns a value
of ‘*INTERACT’ (i.e. not running in batch mode).

Because it is interactive, Program 1 now calls Program 2.
Program 2 calls function RetrieveConfigValue in the SRVPGM with a parm of ‘JobQ’.
jobq = RetrieveConfigValue('JobQ'); This now returns a value of
blanks. Note that the call and parm are identical.

Here is the function.
dcl-proc RetrieveConfigValue export;
dcl-pi *n char(500);
@key char(10) const;
end-pi;

dcl-s rtnValue char(500);

exec sql
select value
into :rtnValue
from scfConfig
where key = :@key;

return rtnValue;

end-proc RetrieveConfigValue;

The only differences in the SQLCA are shown below. The top line is the successful call. The bottom line is the failed call. The results are the reverse of what I would expect to see. :(
SQLERRP OF SQLCA = 'QSQFETCH'
SQLERRP OF SQLCA = ' '

SQLERP OF SQLCA = 'QSQFETCH'
SQLERP OF SQLCA = ' '

SQLERRD OF SQLCA(3) = 1
SQLERRD OF SQLCA(3) = 0

SQLERRD OF SQLCA(4) = 500
SQLERRD OF SQLCA(4) = 0

SQLERRD OF SQLCA(5) = 100
SQLERRD OF SQLCA(5) = 0

SQLERRD OF SQLCA(6) = -134613260
SQLERRD OF SQLCA(6) = 0

SQLERR OF SQLCA = ' █ █4 À7964'
SQLERR OF SQLCA = ' '

SQLER3 OF SQLCA = 000000001.
SQLER3 OF SQLCA = 000000000.

SQLER4 OF SQLCA = 000000500.
SQLER4 OF SQLCA = 000000000.

SQLER5 OF SQLCA = 000000100.
SQLER5 OF SQLCA = 000000000.

SQLER6 OF SQLCA = -134613260.
SQLER6 OF SQLCA = 000000000.

When I debug the process and stop it at the point where the second execution of the sql returns blanks and use STRSQL, I get ‘*INTERACT’ as expected.

If I change SCFCONFIG to be a PF on MIDOHIO2, both calls to RetrieveConfigValue return ‘*INTERACT’.

Also, if I change the SQL to hardcode the machine and library like this, it also works the first time but not the second.
exec sql
select value
into :rtnValue
from midohio.$scams.scfConfig
where key = :@key;

I am so confused!!!!

Any thoughts?

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-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 RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-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-2022 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.