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 have written the steps up below. If you want to run SQL on the IBM I from
Excel I think it is useful to follow along just to see how it works. The
data connection from Excel to the 400 works very well.
code the sql procedure:
CREATE PROCEDURE QGPL/ODCTESTER()
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE C1 CURSOR FOR
select A.SRCDTA, A.SRCSEQ, A.SRCDAT
from QRPGLESRC A ;
OPEN C1;
SET RESULT SETS CURSOR C1 ;
END
create the sql procedure:
RUNSQLSTM SRCFILE(QGPL/QSQLSRC) SRCMBR(ODCTESTER)
on the PC run ODBC administration and make sure there is a "user dsn" that
connects to your IBM i.
To add a new DSN:
- click add
- select "client access ODBC driver (32-bit), click Finish
- on general tab, enter the DSN name and the IBM i system
- on the server tab check connection type, use ODBC access mode. Call
allowed.
- click OK
Make sure that "call allowed" is checked in the connection type section of
the server tab.
Now find MSQUERY on your PC. It comes with Office.
c:\program files\microsoft office 15\root\office15\msqry32.exe
or in an office 2010 install
C:\Program Files (x86)\Microsoft Office\Office14\msqry32.exe
double click msqry32.exe to run it.
click file, execute sql
click data sources and select the user dsn for the ibm i and click OK
enter the SQL statement: call qgpl.odctester( ) and click execute
On 2 different PCs the procedure runs correctly and the output rows are
displayed.
But on 2 others I have access to the result is "executed sql statement
successfully",
but there are no rows displayed.
Why the difference?
To run the ODCTESTER procedure in Excel do the following:
Save the following HTML source to the c:\users\your user name\my
documents\my data sources folder as a .odc file. You need to change the
DSN= in the ConnectionString element.
Save it as file name odctester.odc
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="
http://www.w3.org/TR/REC-html40";>
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=SourceType content=OLEDB>
<xml id=docprops>
<o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="
http://www.w3.org/TR/REC-html40";>
<o:Description>call procedure tester. Return result set from
as400.</o:Description>
<o:Name>odctester</o:Name>
</o:DocumentProperties>
</xml>
<xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="
http://www.w3.org/TR/REC-html40";>
<odc:Connection odc:Type="ODBC">
<odc:ConnectionString>DSN=S067454R;</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText>call qgpl.odctester( )</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
</head>
</html>
Once the .ODC file is saved open Excel. New workbook. Data tab. Existing
connections.
Find the ODCTESTER connection file and click open. The result set of the
ODCTESTER procedure should display as a table in excel.
midrange.com
