473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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.transaction (account_id, date_time_utc,
transaction_type_id, transaction_status_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatched 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 20559
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.transaction (account_id, date_time_utc,
transaction_type_id, transaction_status_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatched 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('Unmatched 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.transaction (account_id, date_time_utc,
transaction_type_id, transaction_status_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatched 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('Unmatched 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('Unmatched 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:DB2: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:DB2: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.transaction (account_id, date_time_utc,
transaction_type_id, transaction_status_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatched 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('Unmatched 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('Unmatched 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:DB2: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:DB2: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.transaction (account_id, date_time_utc,
transaction_type_id, transaction_status_id, description, amount)
values (?, { fn now() }, ?, ?, concat('Unmatched 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('Unmatched 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('Unmatched 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:DB2: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:DB2: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
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...
3
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...
1
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...
1
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...
0
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...
1
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...
7
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
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...
1
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

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.