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.