Re: displaying sql procedure result set in msquery and/or excel

On 09-Feb-2015 12:10 -0600, Steve Richter wrote:

I am having a problem getting consistent results when calling
an SQL procedure and displaying its result set in MSquery or Excel.
On some of the PCs I run the code on, it works fine. On others, the
procedure runs, but no results are displayed. What is the
difference?

I would suggest to review the joblog of the server job processing the client request; the joblog for which query\SQL debug messaging was turned on via either QAQQINI or STRDBG UPDPROD(*YES) prior to the CALL being tested.

Each client can establish some various environmental attributes which can influence the results of a query. For that simple SELECT * query naming just a file with nothing else, things like library list and isolation level for example may matter for finding and accessing the data, but not things like sort sequence that affect the actual data selection. I would expect that the physical source file is not journaled, so running without WITH NC clause coded might result is inconsistencies, but I would expect any client to /see/ the sqlcode=-7008 and report an error vs a "successful" query without any data\results.

<<SNIP>>
from QRPGLESRC A ;
<<SNIP>>

If the query need not run with isolation nor FOR UPDATE, then explicitly coding WITH NC FOR FETCH ONLY after the correleation name and before the semicolon as statement terminator might be worthwhile to avoid issues with either.

The table-reference is unqualified. Tracing or other means to verify the source of the data is appropriate. Perhaps the library list is not consistent between each requester, and the default SCHEMA is *LIBL.?

OPEN C1;
SET RESULT SETS CURSOR C1 ;
END

FWiW: One thing I would consider doing, is change the SET RESULT SETS statement to include WITH RETURN TO CLIENT; the default for the WITH RETURN clause is TO CALLER for which only intermediate callers would have visibility to the CURSOR.

create the sql procedure:

RUNSQLSTM SRCFILE(QGPL/QSQLSRC) SRCMBR(ODCTESTER)
<<SNIP>>

Note: The default for that command is COMMIT(*CHG); uncommitted changes are implicitly rolled back when the job making the request ends. Be sure the expected version of the routine exists and is committed [not locked]. Another default is NAMING(*SYS), for which the default SCHEMA of *LIBL is expected; see above about unqualified file name being referenced by the query.