I'm having a problem with my CF syntax for a MySql stored procedure. I try the following:

<CFQUERY NAME="getGroups" DATASOURCE="mysqldsn">
call getGroups()
</CFQUERY>

The error I receive is as follows:
General error: PROCEDURE mySqlDB1.getGroups can't return a result set in the given context

This stored procedure works when I run it using MySql Administrator, so I wonder if there is there a problem with my syntax, or do I need to use the DSN-less connection as described in HostMySite support? Has anyone had any experience using Cold Fusion with MySql stored procs and if so could you post some code snippets of what works for you?

Thanks very much for any input.

Try the CFStoredProc tag as below.

<cfstoredproc procedure="NameOfStoredProcedure" DataSource="#variables.dsn#">

This works in SQL Server (here at hms) just fine.

HTH

Oh Yeah....There is a parameter tag also.

Hi Ted--

Thanks for the suggestion.

I tried the following syntax per your suggestion:

<cfstoredproc procedure = "getGroups" dataSource = "mysqldsn" >
<cfprocresult name = RS1>
</cfstoredproc>

which gives the following error:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.
Null Pointers are another name for undefined values.

I wonder if there is something obvious (to someone other than me, heh) that I a missing?

Have you tried running a stored procedure, with the cf tag, that does not return a result. You might just try a small dummy sp that inserts a single record or something to see if the problem is outside of Coldfusion.

Otherwise ???

Hi Ted---

I tried as you suggested--running a stored proc that performs a table insert, but does not return a result set. It was successful. I used the following code:

<cfquery name="testSP" datasource="mysqldsn" >
call mysqldb.testProc();
</cfquery>

For stored procs that return a result set, I was now successful using the DSN-less code posted in HMS support:

<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver}; SERVER=[my server IP address]; PORT=3306; DATABASE=mysqldb; USER=testuser; PASSWORD=password; OPTION=3;");

st = con.createStatement();
rs = st.ExecuteQuery("call mysqldb.getGroups();");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
</cfscript>

The CF script is a bit more verbose than than using CFQUERY or CFSTOREDPROC, but it's a workable solution for now.

Thanks for your help.

Good news that it worked. Too bad you needed the 'long' solution.

As I mentioned, my solution is wordy, but it works...I created a custom tag, saved as MySql.cfm which has the following code:

<CFPARAM NAME="ATTRIBUTES.ProcName" TYPE="STRING" DEFAULT="">
<CFPARAM NAME="ATTRIBUTES.Params" TYPE="STRING" DEFAULT="">
<CFPARAM NAME="ATTRIBUTES.isResultSet" TYPE="STRING" DEFAULT="0">
<CFPARAM NAME="ATTRIBUTES.DSN" TYPE="STRING" DEFAULT="mysqldsn">

<CFIF ATTRIBUTES.isResultSet IS "0">

<CFQUERY NAME="#ATTRIBUTES.ProcName#" DATASOURCE="#ATTRIBUTES.DSN#">
CALL #ATTRIBUTES.ProcName#( #PreserveSingleQuotes( ATTRIBUTES.Params )# );
</CFQUERY>

<CFELSE>

<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver}; SERVER=[Server IP Address]; PORT=3306; DATABASE=[databaser]; USER=[username]; PASSWORD=[password]; OPTION=3;");
st = con.createStatement();
rs = st.ExecuteQuery("call mydate.#ATTRIBUTES.ProcName#( #PreserveSingleQuotes( ATTRIBUTES.Params )#);");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);

</cfscript>
<cfset "CALLER.#ATTRIBUTES.ProcName#" = q>

</CFIF>

I then call the custom tag using the following syntax:

<CF_MySql ProcName="someQuery" Params="#ID#, '#SomeOtherParam#', '#etc#', '#etc2#'" isResultSet="1">

Probably overly complex, as I'm sure I'm just missing something on CFSTOREDPROC MySql syntax...but the end result is a custom tag that gets the job done.

So I had my handy MySql custom tag and had basically called this issue closed for the last 2 months. But the other day, I really needed to cache some lengthy queries, and the DSN-less connection code was not going to make that easy. So I once again dug into this issue.

After many discussions with HMS tech support and some testing using my own VPS, it turned out that my client's hosted solution was using the MySql 3.x drivers. I requested HMS install the 5.0 jdbc connector per an Adobe tech note (http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=6ef0253) and now my stored procedures (using CFQUERY syntax) work like a charm.

Thanks to all the folks at HMS that helped on this issue.

Thanks for posting the update. Good to know and something to look for in the future.

Thanks