Martin Evans wrote:[color=blue]
> On Thu, 08 Jun 2006 16:15:14 -0400, Serge Rielau <srielau@ca.ibm.com>
> wrote:
>[color=green]
>> Martin Evans wrote:[color=darkred]
>>> 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.[/color]
>> 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[/color]
>
> 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[/color]
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/ 
