Try to explicitly cast IFS_PATH_NAME to (VAR)CHAR(100) in your select
statement.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"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)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Montag, 24. Oktober 2022 09:12
To: midrange-l@xxxxxxxxxxxxxxxxxx
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
(INPUT_IFS_DIRECTORY VARCHAR(100),
SCAN_STRING CHAR(50))
RETURNS TABLE(STMF_NAME VARCHAR(100),
LINE_NUMBER DECIMAL(5, 0),
LINE VARCHAR(100))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
-- 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
FROM TABLE(IFS_OBJECT_STATISTICS(
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
FROM TABLE(IFS_READ(
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 ;
END ;
select * from
table(MSDDCB.Scan_IFS_for_String(
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
Don
--
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,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
