Troubleshooting
Problem
This document discusses how to avoid getting an error when using a variable length character in a user-defined function or stored procedure.
Resolving The Problem
The following is true for Stored Procedures as well.
The following IBM SQL/400 User-Defined Function (UDF) is created with one input parameter defined as CHAR(3):
Create Function
UDF_ERROR_MESSAGE_TEST
(Parm_1 CHAR(3))
RETURNS INT
LANGUAGE SQL IS DETERMINISTIC
SPECIFIC S00001562S
BEGIN
RETURN 1;
END
Invoking the function by running the following command fails with SQL0204:
SELECT UDF_ERROR_MESSAGE_TEST ('AAA') FROM library/file;
This is due to the nature of function signatures and data type promotion.
When a variable of 'AAA' is passed, it is passed as VARCHAR. However, the function is created as CHAR,3. Therefore, the signature does not match and causes the SQL0204. The signature is created by default from the qualified function name combined with the number and data types of the input parameters.
To correct this problem, do one of the following:
Create Function
UDF_ERROR_MESSAGE_TEST
(Parm_1 CHAR(3))
RETURNS INT
LANGUAGE SQL IS DETERMINISTIC
SPECIFIC S00001562S
BEGIN
RETURN 1;
END
Invoking the function by running the following command fails with SQL0204:
SELECT UDF_ERROR_MESSAGE_TEST ('AAA') FROM library/file;
This is due to the nature of function signatures and data type promotion.
When a variable of 'AAA' is passed, it is passed as VARCHAR. However, the function is created as CHAR,3. Therefore, the signature does not match and causes the SQL0204. The signature is created by default from the qualified function name combined with the number and data types of the input parameters.
To correct this problem, do one of the following:
| o | To leave the Function defined as CHAR, invoke the function by running the following statement: SELECT UDF_ERROR_MESSAGE_TEST (cast('AAA' as CHAR(3)) ) FROM library/file; |
| o | Declare the UDF as VARCHAR. This is noted in the SQL Reference manual located at https://www.ibm.com/docs/en/i/7.5?topic=reference-sql under the chapter called Statements, in the section CREATE FUNCTION - Notes - Choosing data types for input parameters or in the chapter called Language Elements, in the section Functions - Function resolution and Method of finding the best fit. Other Reference material: Create function - https://www.ibm.com/docs/en/i/7.5?topic=statements-create-function Function resolution - https://www.ibm.com/docs/en/i/7.5?topic=functions-function-resolution |
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i3HAAQ","label":"IBM i Db2-\u003ESQL Programming"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
28062202
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->SQL Programming
Software version:
All Versions
Operating system(s):
IBM i
Document number:
640285
Modified date:
01 November 2024
UID
nas8N1016843
Manage My Notification Subscriptions