SQL0418N error: Untyped parameter markers cannot be used in some cases???

[Joost Kraaijeveld]

Hi all,

In my application that is generated by Clarion an SQL0418N ("A statement
contains a use of a parameter marker that is not valid.") occurs. In the
explanation section it says "in some cases as the sole argument of a scalar
function". The parameter marker is used in the UCASE function.

Is this such "some case"??

The full error provided by Clarion's trace:

Preparing Statement 1ae2350 : SELECT A.LANDCODE, A.SOORTLAND, A.LANDNAAM,
A.LANDCODE_CBS FROM MUNTSYS.LANDEN A WHERE {fn UCASE( A.LANDCODE)} >= {fn
UCASE(?)} ORDER BY {fn UCASE( A.LANDCODE)} Time Taken:0.00 secs
Setting number of rows to fetch to 20 for Statement 1ae2350 Time Taken:0.00
secs
Binding Column 1 to C type CHAR for Statement 1ae2350 Time Taken:0.00 secs
Binding Column 2 to C type UTINYINT for Statement 1ae2350 Time Taken:0.00
secs
Binding Column 3 to C type CHAR for Statement 1ae2350 Time Taken:0.00 secs
Binding Column 4 to C type SSHORT for Statement 1ae2350 Time Taken:0.00 secs
Binding ? 1 for input with C type CHAR as 1 for Statement 1ae2350 Time
Taken:0.00 secs
Executing prepared Statement 1ae2350
Error Occurred: 42610[IBM][CLI Driver][DB2/LINUX] SQL0418N A statement
contains a use of a parameter marker that is not valid. SQLSTATE=42610
--
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: [phone]
fax: [phone]
e-mail: J.***********@A skesis.nl
web: www.askesis.nl

[Douglas Doole]

The problem is in UCASE(?). If you read the rules for function resolution in
the SQL Reference, you'll see that they are very sensitive to the data
types of the parameters. Unfortunately, a parameter marker doesn't have a
type when it is precompiled, so DB2 doesn't know what type to use and you
get the error.

The solution is to use a CAST around the parameter marker to tell DB2 what
type to expact. For example:
UCASE(CAST(? AS CHAR(10))
UCASE(CAST(? AS VARCHAR(50))
The Db2 knows what type to use for the parameter marker and the function can
be properly resolved.

Note that the type in the CAST and the actual type bound in to the parameter
marker do _not_ have to be identical - they just have to be compatible. So,
if I have UCASE(CAST(? AS VARCHAR(50)), I can bind in a VARCHAR(50) or a
CHAR(10) or any other type that is compatible with a VARCHAR(50). If I try
to bind in a VARCHAR(51), however, I'll get a string too long error.

Hope this helps.

--
_______________ _______________ _______________ _______________ _________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support