473,834 Members | 1,811 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problems with concat and parameter markers

Hi,

I'm getting:

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUX] SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

for some SQL like this:

insert into db2inst1.transa ction (account_id, date_time_utc,
transaction_typ e_id, transaction_sta tus_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatch ed on ', ?), '
repayment'), ?)'

I believe this is down to the parameter marker in the concat function
call and DB2 being unable to identify the correct concat function
because the parameter marker type is not known at compile time and DB2
supports overloaded functions so it needs to know the type to locate
the correct concat function. I've tried casting the parameter marker
like:

cast(? as varchar(128))

but this does not help. I cannot seem to find the right cast to match
the concat function.

Any ideas?

Martin
--
Martin J. Evans
Wetherby, UK
Jun 8 '06 #1
4 21010
Martin Evans wrote:
Hi,

I'm getting:

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUX] SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

for some SQL like this:

insert into db2inst1.transa ction (account_id, date_time_utc,
transaction_typ e_id, transaction_sta tus_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatch ed on ', ?), '
repayment'), ?)'

I believe this is down to the parameter marker in the concat function
call and DB2 being unable to identify the correct concat function
because the parameter marker type is not known at compile time and DB2
supports overloaded functions so it needs to know the type to locate
the correct concat function. I've tried casting the parameter marker
like:

cast(? as varchar(128))

but this does not help. I cannot seem to find the right cast to match
the concat function.

concat() is a built-in function and DB2 can derive the type from the
literal. What happens when you type SYSIBM.CONCAT ?

This works on CLP:
db2 => values concat('Unmatch ed on ', ?);
SQL0313N The number of host variables in the EXECUTE or OPEN statement
is not equal to the number of values required. SQLSTATE=07004

Can you post the real SQL? (You noted "some SQL like this" which leads
me to believe we're seeing a tainted version here)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #2
On Thu, 08 Jun 2006 16:15:14 -0400, Serge Rielau <sr*****@ca.ibm .com>
wrote:
Martin Evans wrote:
Hi,

I'm getting:

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUX] SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

for some SQL like this:

insert into db2inst1.transa ction (account_id, date_time_utc,
transaction_typ e_id, transaction_sta tus_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatch ed on ', ?), '
repayment'), ?)'

I believe this is down to the parameter marker in the concat function
call and DB2 being unable to identify the correct concat function
because the parameter marker type is not known at compile time and DB2
supports overloaded functions so it needs to know the type to locate
the correct concat function. I've tried casting the parameter marker
like:

cast(? as varchar(128))

but this does not help. I cannot seem to find the right cast to match
the concat function.

concat() is a built-in function and DB2 can derive the type from the
literal. What happens when you type SYSIBM.CONCAT ?

This works on CLP:
db2 => values concat('Unmatch ed on ', ?);
SQL0313N The number of host variables in the EXECUTE or OPEN statement
is not equal to the number of values required. SQLSTATE=07004

Can you post the real SQL? (You noted "some SQL like this" which leads
me to believe we're seeing a tainted version here)

Cheers
Serge


Well you are right, this is not EXACTLY the same SQL - I removed one
other parameter in the concat() i.e. it was:

concat('Unmatch ed on ', ?, ' some text')

Interestingly if I create a table:

create table fred (a char(100))

and run the following perl:

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

it works. But I run the following perl it fails (yet with another
error):

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?,' some
text'))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

error is:

DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N A
statement
contains a use of a parameter marker that is not valid. SQLSTATE=42610

so may be it is something to do with the number of arguments to concat
i.e. 2 are ok and 3 are not.

Martin
--
Martin J. Evans
Wetherby, UK
Jun 8 '06 #3
Martin Evans wrote:
On Thu, 08 Jun 2006 16:15:14 -0400, Serge Rielau <sr*****@ca.ibm .com>
wrote:
Martin Evans wrote:
Hi,

I'm getting:

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUX] SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

for some SQL like this:

insert into db2inst1.transa ction (account_id, date_time_utc,
transaction_typ e_id, transaction_sta tus_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatch ed on ', ?), '
repayment'), ?)'

I believe this is down to the parameter marker in the concat function
call and DB2 being unable to identify the correct concat function
because the parameter marker type is not known at compile time and DB2
supports overloaded functions so it needs to know the type to locate
the correct concat function. I've tried casting the parameter marker
like:

cast(? as varchar(128))

but this does not help. I cannot seem to find the right cast to match
the concat function.

concat() is a built-in function and DB2 can derive the type from the
literal. What happens when you type SYSIBM.CONCAT ?

This works on CLP:
db2 => values concat('Unmatch ed on ', ?);
SQL0313N The number of host variables in the EXECUTE or OPEN statement
is not equal to the number of values required. SQLSTATE=07004

Can you post the real SQL? (You noted "some SQL like this" which leads
me to believe we're seeing a tainted version here)

Cheers
Serge


Well you are right, this is not EXACTLY the same SQL - I removed one
other parameter in the concat() i.e. it was:

concat('Unmatch ed on ', ?, ' some text')

Interestingly if I create a table:

create table fred (a char(100))

and run the following perl:

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

it works. But I run the following perl it fails (yet with another
error):

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?,' some
text'))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

error is:

DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N A
statement
contains a use of a parameter marker that is not valid. SQLSTATE=42610

so may be it is something to do with the number of arguments to concat
i.e. 2 are ok and 3 are not.

Martin
--
Martin J. Evans
Wetherby, UK

CONCAT is a BINARY function.
If you want to concat more than two pieces either use infix notation:
'HELLO' || 'WORLD' || '!'
or
CONCAT('Hello', CONCAT('WORLD', !'))

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #4
On Thu, 08 Jun 2006 17:55:08 -0400, Serge Rielau <sr*****@ca.ibm .com>
wrote:
Martin Evans wrote:
On Thu, 08 Jun 2006 16:15:14 -0400, Serge Rielau <sr*****@ca.ibm .com>
wrote:
Martin Evans wrote:
Hi,

I'm getting:

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUX] SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884

for some SQL like this:

insert into db2inst1.transa ction (account_id, date_time_utc,
transaction_typ e_id, transaction_sta tus_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatch ed on ', ?), '
repayment'), ?)'

I believe this is down to the parameter marker in the concat function
call and DB2 being unable to identify the correct concat function
because the parameter marker type is not known at compile time and DB2
supports overloaded functions so it needs to know the type to locate
the correct concat function. I've tried casting the parameter marker
like:

cast(? as varchar(128))

but this does not help. I cannot seem to find the right cast to match
the concat function.
concat() is a built-in function and DB2 can derive the type from the
literal. What happens when you type SYSIBM.CONCAT ?

This works on CLP:
db2 => values concat('Unmatch ed on ', ?);
SQL0313N The number of host variables in the EXECUTE or OPEN statement
is not equal to the number of values required. SQLSTATE=07004

Can you post the real SQL? (You noted "some SQL like this" which leads
me to believe we're seeing a tainted version here)

Cheers
Serge


Well you are right, this is not EXACTLY the same SQL - I removed one
other parameter in the concat() i.e. it was:

concat('Unmatch ed on ', ?, ' some text')

Interestingly if I create a table:

create table fred (a char(100))

and run the following perl:

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

it works. But I run the following perl it fails (yet with another
error):

use DBI;
$dbh = DBI->connect("dbi:D B2:xxx", "db2inst1", "easysoft") ;
$sql = q/insert into fred (a) values(concat(' hello ',?,' some
text'))/;
$sth = $dbh->prepare($sql );
$sth->execute("fred" );

error is:

DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N A
statement
contains a use of a parameter marker that is not valid. SQLSTATE=42610

so may be it is something to do with the number of arguments to concat
i.e. 2 are ok and 3 are not.

Martin
--
Martin J. Evans
Wetherby, UK

CONCAT is a BINARY function.
If you want to concat more than two pieces either use infix notation:
'HELLO' || 'WORLD' || '!'
or
CONCAT('Hello' , CONCAT('WORLD', !'))

Cheers
Serge


Serge,

Thanks - I should have realised that. I was porting some sql from
mysql to db2 and mysql supports concat(x,y,z,.. .) and didn't notice
concat was a 2 op function in db2. What put me on the wrong tack was
searching for the error code and seeing loads of other people getting
the same error with ucase(?).

Thanks again.

Martin
--
Martin J. Evans
Wetherby, UK
Jun 9 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
5121
by: alederer | last post by:
Hallo! I have a table tstest(ts char(13) for bit data constraint a unique). This column is filled in a trigger with generate_unique(). In a application (CLI), I have the values of this column as timestamp representation: e.g.: select timestamp(ts) from tstest 1
3
3169
by: Nile | last post by:
I am having a problem with my application that uses ODBC. I am doing the following: * prepare a statement * Bind * execute The problem is that my application needs to accept only insert queries with parameter markers since it later writes to the database depending on the values in the SQL query.
1
14246
by: Joost Kraaijeveld | last post by:
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:
1
2055
by: Mike | last post by:
Envirnoment is UDB 7.2 fp9 on AIX. I'm familiar with using "dynexpln" for quickly comparing access plans for directly executable queries. dynexpln docs suggest it cannot work with sql that contains parameter markers. But, when there is no Control-centre (i.e. text only access via korn-shell, no x-windows), how can I use db2 on aix command line programs
0
1541
by: thomasb | last post by:
Have for some time tried to understand how parameter markers can be used in DB2 for z/OS (ver. 7) to avoid full prepare. Have tired with things like SELECT .. FROM .. WHERE COL = ? And used DB2 utilities to understand if fill prepare are avoided. Have not seen any positive result. Maybe there is something wrong in our environment?
1
2897
by: m | last post by:
Maybe this has been asked and answered - but I have been looking through old archives for quite some time.... Typical Java app running from Websphere connected to DB2 V8.2FP15 on Linux using JDBC Type 4 drivers. Programmers use mostly PREPARE/EXECUTE logic for security and 'other' reasons - but some SQL executes MUCH slower than same SQL using plain old SQL with values then the SQL with variable markers. Worst case 20 seconds vs. 1...
7
7217
by: =?ISO-8859-2?Q?Gregor_Kova=E8?= | last post by:
Hi! Is it possible to use parameter markers like this: 1.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%?%' If I now set parameter 1 to '' (empty string) I don't get any rows back, but if I run: 2.) SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%%' I get back data I expect.
2
3571
by: PaulR | last post by:
Hi, (DB2 LUW v8.2) When using parameter markers how does the optimizer evaluate filter factors? - and is it able to make use of distribution stats. for parm. markers? The reason I ask, is we have heavily skewed data in places and we need
1
2326
by: uwcssa | last post by:
I am runnig v95. I have a query "select * from A, B where B.b<2000" I create a MQT as "select * from A,B where B.b<2000" (identitcal to the query), and the optimizer will use this MQT to answer this query. However, if I submit the query using paramter marker "select * from A, B where B.b<?", and later fill in this paramter using value "2000", the optimizer will never use this MQT to answer the query.
0
9799
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10799
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10515
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10554
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5629
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4428
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3985
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3084
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.