problems with concat and parameter markers

[Martin Evans]

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

[Serge Rielau]

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/

[Martin Evans]

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

[Serge Rielau]

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/

[Martin Evans]

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