#387 CONCAT unknown function

[Ashley M]

I was trying to use the JDBC 3.0 CONCAT function in a
query.

executing {SELECT {fn CONCAT(UWType, CONCAT(':',
CONCAT(SectionAct, CONCAT(':', ADP_Code))))} FROM
ADPCODE WHERE (DeleteFlag <> 'D' OR DeleteFlag IS NULL)}
using driver <'jTDS Type 4 JDBC Driver for MS SQL
Server and Sybase' v1.0.3>

JDBC exception: java.sql.SQLException: 'CONCAT' is not
a recognized function name.

[Mike Hutchinson]

Logged In: YES
user_id=641437

Ashley,

Concat is only supported as a JDBC Escape i.e. {fn
concat(a,b)}. You have nested concat functions which means
that the inner ones are being passed to SQL Server which
does not recognize them. You could try {fn concat(UWType,
{fn concat(:, {fn concat(SectionAct, {fn concat(:,
ADP_Code)})})})}. JTDS should recognize nested escapes
although this is pushing it a bit!

Alternatively a SQL specific approach would be
UWType+:+SectionAct+:+ADP_Code, which is what the
escaped version should evaluate to.

Mike.

[Alin Sinpalean]

Logged In: YES
user_id=564978

As Mike said CONCAT is a JDBC not SQL Server function. You
have to use {fn CONCAT} for it to work. jTDS will do the
"translations".

Alin.

[Ashley M]

Logged In: YES
user_id=225673

I realize CONCAT is a JDBC function, which is why I enclosed
it in {fn ...}. I guess the problem is one of nested
functions. I'm trying to implement this across several
databases which is the reason for the use of a JDBC
function. Is nested functions specified by the JDBC spec. or
is it a driver implementation question?

[Alin Sinpalean]

Logged In: YES
user_id=564978

Nested or not, JDBC functions have to be escaped; there is
no difference. The JDBC spec doesn't specify nested
functions separately as there's nothing special about them.
Just use the syntax that Mike suggested and you should be ok.

Alin.