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

["Jens Jahnke (JIRA)" <ji...@apache.org>]

Jens Jahnke created CONNECTORS-1078:
---------------------------------------

             Summary: 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
            Priority: Minor


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)