After some program dumps and job logging, here is what I know.
Repeating part of the earlier post...
Program A (our CL program) does a CALLPRC to a "JOB_START" function. This function inserts a record into a local file (let's call it File1). Control returns to Program A.
Program A then calls Program B (customer's program). Program B opens a remote connection and does stuff. Program B ends and returns control to Program A.
Program A does a CALLPRC to a "JOB_END" function. The JOB_END function executes a simple SQL update statement shown below to File1.
update File1
set JobStatus = '*END',
EndDate = Current_timestamp
WHERE ID = :GUID;
I inserted a DSPOBJD *LIBL/File1 just before the CALLPRC to JOB_END. It found the file and dumped the info to *PRINT. The file definitely exists in the expected library which is in the library list.
The update statement returns SQL error -204 - "File1 in *LIBL type FILE" not found. The SQLSTATE is 42704.
If I hardcode the library name (update Library1/File1) the table update is successful.
I recompiled Program A and the service program that contains JOB_START and JOB_END functions so that both were in their own activation group. I got the same result.
So, since the object is found via the DSPOBJD command and the library the DSPOBJD command finds it in is the one that I hardcoded why would the update statement works with the library hardcoded but fail if it is omitted? I can hardcode the library to resolve the issue but I feel like that is just putting a band-aid on the problem to cover it up but not fixing it.
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
Sent: Monday, November 27, 2017 10:10 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL with connections to multiple databases
Activation groups can be weird. I've had similar issues before although it's been a few years.
If both programs run in the same activation group, Program B can mess with connections possibly needed by Program A.
Could re-compile Program A or B to run in a new activation group possibly. Actgrp(*NEW)
I'm also guessing programs are created with CRTSQLRPG or CRTSQLRPGI.
These two compiler parms could possibly have some effect:
Close SQL cursor . . . . . . . .CLOSQLCSR RDB connect method . . . . . . .RDBCNNMTH (This one looks potentially suspect)
Keep in mind this is an educated guess without seeing your programs and without using RPG and embedded SQL daily 😊
Anyone else here using RPG and embedded SQL daily might be able to back up or debunk my response.......
Regards,
Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com
----------------------------------------------------------------------
message: 1
date: Sun, 26 Nov 2017 15:51:29 -0500
from: "John R. Smith, Jr." <smith5646midrange@xxxxxxxxx>
subject: RE: SQL with connections to multiple databases
I can only answer 2 of those 3 questions.
Program B always works. It is when control is returned back to program A and program A tries to update a record that we have a problem.
I don't know if program B closes the connections or now. We do not own program B, the customer does and we can't force all of our customers to do proper cleanup.
How would the activation affect this? I'm sure there is a ton that I do not understand on how programs play within groups.
***Also, one other important piece of info that I omitted is that when program A calls other "program B"s that do not do a remote connection, everything works correctly...program A is able to update the row in the database.
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
Sent: Friday, November 24, 2017 1:42 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL with connections to multiple databases
Perhaps an activation group problem ?
Does program B work if called standalone ?
Are you sure is B closing all his connections ?
Regards,
Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com
----------------------------------------------------------------------
message: 1
date: Wed, 22 Nov 2017 16:15:06 -0500
from: "John R. Smith, Jr." <smith5646midrange@xxxxxxxxx>
subject: RE: SQL with connections to multiple databases
I originally thought my problem was a remote connection not getting closed/disconnected but apparently that is not the problem. I am very confused on what is causing it or how to find it so I am reaching out for ideas. Here is what I know.
-- I am consulting for a company that sells a product and one of their customer is having a problem with it.
-- I do not have access to the customer's machine so I can't debug this.
-- The product's program (call it Program A) is calling a customer's program (call it program B) which is doing a remote database connection.
-- When program B ends, program A calls a function in a service program that attempts to update a record in the database on the local machine but fails with SQL0204 - &1 in &2 type *&3 not found.
-- I have made several "patch versions" of both program A and the service program and have found the following (via DSPJOB and other messages manually and systematically sent to the joblog).
-- The library containing the file to be updated is in the library list.
-- Immediately before the SQL update I added a "EXEC SQL CONNECT".
According to %subst(SQLERRMC:1:18), the database connection is the local machine.
-- I added "EXEC SQL ROLLBACK", "EXEC SQL CONNECT RESET", and another "EXEC SQL CONNECT". I added the rollback because I found on the web that you can't do connection reset with pending updates so I added it just in case that was correct. The additional "EXEC SQL CONNECT" and checking the value again after the "EXEC SQL CONNECT RESET" was a "I'm getting desperate so I'll try anything".
Can someone provide other debugging ideas, commands, or variables in program A or the service program that I can look at to see what is going on? I am completely lost.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
