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

turns out this problem was caused by client access. The client access I was
running was V7 R1 SI00000. ( run CWBCKVER on the PC )

IBM support sent me to
http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html to
get the latest version.

For general troubleshooting of ODBC issues the ODBC administrator has some
good options. Go to the diagnostic tab and check "enable the STRDBG
command" and "print joblog at disconnect".



On Mon, Feb 9, 2015 at 6:18 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.