Try to explicitly cast IFS_PATH_NAME to (VAR)CHAR(100) in your select
Mit freundlichen Grüßen / Best regards
Modernization ? Education ? Consulting on IBM i
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Montag, 24. Oktober 2022 09:12
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL Error
I am on V7R3 and current on PTF's as of 2 weeks ago.
I was running a script in iACS Run SQL Scripts and received the following
error which makes not a lot of sense to me - any suggestions ?
SQL State 22001
Vendor Code -404
Message (SQL0404) Value for column or variable STMF_NAME too long. Cause ...
An Insert, Update, Merge, Set, BValues Into or Get Diagnostics statement
specifies a value that is longer than the maximum length string that can be
stored in STMF_NAME. The length of STMF_NAME is 100 and the length of the
string is 57. Recovery ... Reduce the length of the string from 57 to a
maximum of 100 and try the request again.
So that is totally backwards ?
Here is the script
CREATE OR REPLACE FUNCTION MSDDCB.Scan_IFS_for_String
RETURNS TABLE(STMF_NAME VARCHAR(100),
LINE_NUMBER DECIMAL(5, 0),
MODIFIES SQL DATA
-- Temporary table to return data
CREATE OR REPLACE TABLE QTEMP/RETURN_TABLE (STMF_NAME VARCHAR(100),
LINE_NUMBER DECIMAL(5, 0),
LINE VARCHAR(100)) ;
DELETE FROM QTEMP/RETURN_TABLE ;
FOR SELECT PATH_NAME AS IFS_PATH_NAME
START_PATH_NAME => INPUT_IFS_DIRECTORY,
OBJECT_TYPE_LIST => '*ALLSTMF')) DO
-- Scan each Stream file using Table Function 'IFS_READ'
-- And insert the results into Temporary Table
INSERT INTO QTEMP/RETURN_TABLE
SELECT IFS_PATH_NAME, LINE_NUMBER, LINE
PATH_NAME => IFS_PATH_NAME))
WHERE UPPER(LINE) like '%' || UPPER(TRIM(SCAN_STRING)) || '%';
END FOR ;
-- Return Temporary Table
RETURN SELECT STMF_NAME, LINE_NUMBER, LINE FROM QTEMP/RETURN_TABLE ;
select * from
INPUT_IFS_DIRECTORY => '/www/zendphp7/htdocs/app1',
SCAN_STRING => 'PHPUSER'));
I will change the length of STMF_NAME to 1000 and run it again but the
messages is strange.
Thanks for any suggestions
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments..
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
Help support midrange.com by shopping at amazon.com with our affiliate link:
As an Amazon Associate we earn from qualifying purchases.