BTW - If you prefer to consume REST APIs using Python instead of issuing SQL commands over ODBC, then you could set up an IWS REST API. An IWS REST API can execute SQL queries in two ways.
First, the REST API can contain a method that is a SQL statement. IWS REST APIs can execute server side SQL.
Second, the REST API can use a SQL statement to call a SQL stored procedure. Then you would need to set up a SQL stored procedure as I described in my last email (below).
It is substantially more work to set up the IWS REST API if you have never done it before. It literally takes a few minutes once you get familiar with IWS REST APIs. Tim Rowe from IBM gave an excellent presentation about IWS REST APIs, including creating a method to execute server side SQL, at a recent PowerUP conference. I do not feel at liberty to distribute his presentation document. Maybe contact him and ask for a copy?
Thanks,
Kelly Cookson
Senior Software Engineer II
Dot Foods, Inc.
1-217-773-4486 ext. 12676
www.dotfoods.com
-----Original Message-----
From: OpenSource <opensource-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Kelly Cookson via OpenSource
Sent: Friday, May 21, 2021 11:58 AM
To: IBMi Open Source Roundtable <opensource@xxxxxxxxxxxxxxxxxx>
Cc: Kelly Cookson <KCookson@xxxxxxxxxxxx>
Subject: [EXTERNAL] Re: [IBMiOSS] Queries over ODBC not always the same as in STRSQL?
I was trying to get the number of members in various source physical files.
So I whipped up a little Python script containing the following SQL:
select count(*) from qsys2.syspartitionstat
where table_schema = ? and table_name = ?
If you really want to use a Python script, then maybe your Python script could use SQL to call an IBMi SQL stored procedure over an ODBC connection. The stored procedure would contain the query you want to run.
The code below has not been tested and probably needs tweaking before it works. Treat it like pseudocode. The SQL stored procedure would look something like this:
CREATE OR REPLACE PROCEDURE MYSQLSP
(IN TABLE_SCHEMA_PARM CHAR (10),
IN TABLE_NAME_PARM CHAR (10))
RESULT SETS 1
LANGUAGE SQL
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE C1_GET_COUNT CURSOR WITH RETURN FOR
SELECT COUNT(*) FROM qsys2.syspartitionstat
WHERE table_schema = TABLE_SCHEMA_PARM
AND table_name = TABLE_NAME_PARM;
OPEN C1_GET_COUNT;
RETURN;
END;
The SQL statement to call the stored procedure would look something like this:
CALL MYLIB.MYSQLSP('MySchema','MyTable');
MYLIB is the library where the stored procedure resides. You may need to have MYLIB added as a schema in your ODBC connection. Here's a good Red Book to learn more about stored procedures if you have not used them before:
https://urldefense.com/v3/__https://www.redbooks.ibm.com/redbooks/pdfs/sg248326.pdf__;!!BZnD2a2Aqg!W2X15DzDCprWz4ox7ZSARdXaf_zY2R1fIrcP3Qw_5le7JtRBTVfYn0vN3Inli3de_Q$ .
Your Python program will need to process the rowset returned by the SQL cursor (one row containing a count result).
midrange.com
