[CONNECTORS-1078] Update documentation regarding JDBC, MySQL and CONCAT

• Type: Improvement
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: ManifoldCF 1.8, ManifoldCF 2.0
• Component/s: Documentation
• Labels: concat jdbc mysql
• Assignee: Karl Wright
• Reporter: Jens Grassel
• Created: 22/Oct/14 18:08
• Updated: 23/Oct/14 07:47
• Resolved: 23/Oct/14 07:47

Description

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:

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)

You'll have to use the ifnull operator to circumvent the issue.

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)