By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,276 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,276 IT Pros & Developers. It's quick & easy.

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

P: n/a
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: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.***********@Askesis.nl
web: www.askesis.nl
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.