RE: SQL in RPGLE problem -- RPG400-L

I set the breakpoint immediately after the SQL statement. Note the line after I included the SQLCA values because I also said that at that point (using F21 in the debugger), I get the correct value using STRSQL.

And, the error checking would not have helped with this problem because SQLCOD = 0;

Also to add another piece to the confusion...I just did a test where I changed the SQL to a CHAIN and it worked for both calls. There is something messed up with the SQL logic that is not messed up with the CHAIN logic. I opened a ticket with IBM.

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

Where did you check the SQLCA? I hope directly in the service program (using a break point after the exec sql) and not in your programs - because every module has its own SQLCA.

Because of that you should always have some sort of SQL error checking in your code - don’t delay it - it would be easier, if you have it right now.

My 2ct - just debug into the service procedure and check everything there - I’m sure you will find a reason.

Regards,
Daniel

Von meinem iPhone gesendet

Am 12.05.2022 um 21:11 schrieb smith5646midrange@xxxxxxxxx:

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

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