[jira] [Commented] (CONNECTORS-1078) Update documentation regarding JDBC, MySQL and CONCAT

["Karl Wright (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CONNECTORS-1078?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14181102#comment-14181102 ] 

Karl Wright commented on CONNECTORS-1078:
-----------------------------------------

r1633760 (trunk)
r1633761 (dev_1x)

> Update documentation regarding JDBC, MySQL and CONCAT
> -----------------------------------------------------
>
>                 Key: CONNECTORS-1078
>                 URL: https://issues.apache.org/jira/browse/CONNECTORS-1078
>             Project: ManifoldCF
>          Issue Type: Improvement
>          Components: Documentation
>            Reporter: Jens Jahnke
>            Assignee: Karl Wright
>            Priority: Minor
>              Labels: concat, jdbc, mysql
>             Fix For: ManifoldCF 1.8, ManifoldCF 2.0
>
>
> A little late but here is my solution to the mysql concat problem from the mailing list for the documentation. ;-)
> When using the generic database connector with a mysql connection you have to be aware of the behaviour of the {{concat}} command if you combine multiple columns.
> For example the following query will not work if any value is {{NULL}}:
> {code:sql}
> SELECT id AS $(IDCOLUMN),
> CONCAT("http://my.base.url/show.html?record=", id) AS $(URLCOLUMN),
> CONCAT(name, " ", description, " ", what_ever) AS $(DATACOLUMN)
> FROM accounts WHERE id IN $(IDLIST)
> {code}
> You'll have to use the {{ifnull}} operator to circumvent the issue.
> {code:sql}
> SELECT id AS $(IDCOLUMN),
> CONCAT("http://my.base.url/show.html?record=", id) AS $(URLCOLUMN),
> CONCAT(name, " ", IFNULL(description, ""), " ", IFNULL(what_ever, ""))
> AS $(DATACOLUMN)
> FROM accounts WHERE id IN $(IDLIST)
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)