Coldfusion cfquery: running mySql user-defined functions

CF10, mySql 5

I was creating a very complex sql query for my app with lots of conditional code and realised it would be simpler to have some mySql functions to abstract some of the complexity. This was new territory for me.

One immediate gotcha was with sqlYog: If you are testing your function in sqlYog and get a 1305 error: FUNCTION not found, make sure you are clicking the button to Execute ALL queries, not just your one test SELECT query. Doh!

Related to this, if you combine all the statements below into a single cfquery statement your CF server must be configured to run multiple queries. Since that is not a great idea from a security point of view the solution is to run each one separately as shown below.

This is what I eventually found to work (a highly simplified example to demonstrate the syntax):

<cfquery datasource="#request.dsn#" >

DROP FUNCTION IF EXISTS my_func;

</cfquery>

Define the function:


<cfquery datasource="#request.dsn#">
    CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30))
       RETURNS VARCHAR(100)
    BEGIN
       DECLARE res VARCHAR(100);
       SET res = CONCAT(str1, ' ', str2);
       RETURN res;
    END
</cfquery>

Test just the function:

<cfquery datasource="#request.dsn#" NAME="recordset1">

SELECT my_func('abc1','def2') as myString;

</cfquery>
<cfdump var="#recordset1#">
<!--- myString = "abc1 def2" --->

Use in an SQL SELECT:

<cfquery datasource="#request.dsn#" NAME="recordset2">

SELECT 
members.memberid, 
my_func(members.firstname, members.lastname) as myFullName 
FROM members;

</cfquery>
<cfdump var="#recordset2#">
<!--- Outputs myFullName like "Murray Hopkins"  --->

Same thing using CFSCRIPT:

<cfscript>	
	
	sql = "DROP FUNCTION IF EXISTS my_func;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30)) RETURNS VARCHAR(100) BEGIN DECLARE res VARCHAR(100); SET res = CONCAT(str1, ' ', str2); RETURN res; END";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "SELECT my_func('abc','def') as myString;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	qryObj = qry.execute();		
    writedump(qryObj.getresult());

	sql = "SELECT members.userid, my_func(members.firstname, members.lastname) as myFullName FROM members;";
	qry = new Query( sql = sql, dsn=request.dsn );
	qryObj = qry.execute();		
    writedump(qryObj.getresult());	

</cfscript>

I hope this saves someone some time.

Go well,
Murray