I got it to work!
Thank you!
From: Jerry Forss
Sent: Thursday, February 19, 2026 7:17 AM
To: 'Daniel Gross' <daniel@xxxxxxxx>
Cc: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: RE: External Re: Count records in SQL
I am also trying this but returns nothing.
SQLFile = File(FileIdx);
SQLLibrary = FileLibrary(FileIdx);
// Declare Sql Statement
Exec SQL Declare LockCursor Cursor For
SELECT Object_Schema, Object_Name, Lock_Scope, Job_Name
FROM QSYS2.OBJECT_LOCK_INFO
WHERE SYSTEM_OBJECT_SCHEMA = :SQLLibrary
And SYSTEM_OBJECT_NAME = :SQLFile;
Now, when I do a WRKOBJLCK it says there are no locks, but if I press F6 for member locks it shows many.
Am I missing something?
From: Daniel Gross <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
Sent: Thursday, February 19, 2026 7:09 AM
To: Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>
Cc: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: External Re: Count records in SQL
Most probably the environment differs - like the transaction isolation level (between eSQL and STRSQL).
You can try different clauses to solve the problem.
Add "FOR READ ONLY" to your SQL statement
Add "WITH NC" to your SQL statement
Add "USE CURRENTLY COMMITTED" to your SQL statement
Read about all 3 clauses here:
->
https://www.ibm.com/docs/en/i/7.6.0?topic=queries-select-statement
HTH
Daniel
Am 19.02.2026 um 13:45 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>:
Hi Daniel
I am checking to see if there are any locks on the files using QSys2.Objecy_Lock_Info and getting a lock wait on a DtaQ in the target library, SqlSTT of 01687.
Do you have any idea why using that would be locking?
When I run it under strsql there are no issues.
// Check If File Is Locked
SqlSelect = 'SELECT Object_Schema, ' +
'Object_Name, '+
'Lock_Scope, ' +
'Job_Name ' +
'From QSYS2.Object_Lock_Info ' +
'Where OBJECT_SCHEMA = ' + SQ + %Trim(FileLibrary(FileIdx)) + SQ + ' And ' +
'OBJECT_NAME = ' + SQ + %Trim(PmKey3) + SQ;
// Prepare Sql Statement
Exec SQL Prepare LockCmd From :SqlSelect;
// Declare Sql Cursor
Exec SQL Declare LockCursor Cursor For LockCmd;
// Run Sql Statement
Exec SQL Open LockCursor;
// Process Data Set
DoU Done;
// Get Employee Data
Exec Sql Fetch Next From LockCursor Into : Object_Schema,
: Object_Name,
: Lock_Scope,
: Job_Name;
// If Eof Of Data or Unknown Error get out
If (SQLSTT <> SQLOK)
And (SQLSTT <> SQLCmpError);
Leave;
EndIf;
From: Daniel Gross <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
Sent: Friday, February 13, 2026 12:25 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>
Subject: External Re: Count records in SQL
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi Jerry,
of course, the easiest way to count records is a static SQL like:
exec sql select count(*)
into :nCountVar
from ...
But as I have seen, you have a dynamic SQL to tables in different schemas. And that's not possible in static SQL without creating aliases first.
But with dynamic SQL you can't have an INTO clause in your SELECT statement - so a possible solution would be:
SqlSelect = 'select count(*) ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
SqlSelect = 'values ('+SqlSelect+') into ?';
exec sql prepare stmSelectCount from :SqlSelect;
exec sql execute stmSelectCount using :nCountVar;
You have to wrap your SELECT in a VALUES/INTO statement with a dynamic parameter marker. Then prepare and execute it.
The solutions using GET DIAGNOSTICS after the OPEN doesn't help, as the database does not always return the exact number of rows in the cursor - it can either be estimated or (as this might be an ASENSITIVE or SENSITIVE cursor) it can change during the fetch loop.
But if you want to read all the records anyway, you can do the following:
SqlSelect = 'SELECT count(*) over(), ' +
'C6DcCd, '+
'C6CvNb, '+
'C6AcDt, ' +
'C6FnSt,' +
'C6B9Cd ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
exec sql prepare stmSqlSelect from :SqlSelect;
exec sql declare csrSqlSelect INSENSITIVE cursor for stmSqlSelect;
exec sql open...
exec sql fetch csrSqlSelect into :nCountVar, ...;
This way you have to actual number of rows with every fetch in the variable. As the cursor is declared as INSENSITIVE, the rows won't change, even when new rows matching the criteria are inserted during runtime.
COUNT(*) OVER()
is the simpliest form of an OLAP expression (aka window function). It's a very powerful feature of modern SQL and massively underrated and underutilized.
->
https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications
In essence, you can calculate typical aggregates on every level of your select statement without using GROUP BY. Like having a grouped counter and row number over groups of rows or creating running totals, ranks and a lot more.
If you need the row count and the records, I would do it with OLAP. If you only need the count, the VALUES INTO solution is good.
What is the purpose of the program? Or better what doesn't do with the fetched rows?
If you need more help, feel free to contact me via the list or in private.
HTH
Daniel
Am 12.02.2026 um 21:16 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>:
I have a SQL
SqlSelect = 'SELECT C6DcCd, ' +
'C6CvNb, '+
'C6AcDt, ' +
'C6FnSt,' +
'C6B9Cd ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
Instead of reading through the cursor, I want the number of records found.
How do I do that?
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Subject to Change Notice:
WalzCraft reserves the right to improve designs, and to change specifications without notice.
Confidentiality Notice:
This message and any attachments may contain confidential and privileged information that is protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s) and should "only" pertain to "WalzCraft" company matters. If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this email or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this email in error, please notify the sender immediately and permanently delete this email. Thank You
WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
www.walzcraft.com<
https://www.walzcraft.com> Phone: 1-800-237-1326
Subject to Change Notice:
WalzCraft reserves the right to improve designs, and to change specifications without notice.
Confidentiality Notice:
This message and any attachments may contain confidential and privileged information that is protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s) and should "only" pertain to "WalzCraft" company matters. If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this email or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this email in error, please notify the sender immediately and permanently delete this email. Thank You
WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
www.walzcraft.com<
https://www.walzcraft.com> Phone: 1-800-237-1326
midrange.com
