Using cfstoredproc to return query data

Hi,

I'm somewhat new to stored procs on mysql, and to cfml. My task is to translate a query from cfml to a stored proc in our mysql db. Just to get a proof of concept working for myself, for now I'm just trying to return the pk from a short list of records.

The stored proc as it appears i my db:

BEGIN

	SELECT 
    	id as return_id
    
    FROM donations;
    
END

Select allOpen in new window

This is in PHPMyAdmin on a lucee/hostek server, so it might look like it's missing the params. I have set the params in the GUI as:

direction: OUT
name: return_id
type: INT
length/values: 11

Select allOpen in new window

The cfml which calls this stored proc is:

<cfstoredproc datasource="pagrorg_landingpages" procedure="getDonations" debug="yes" returncode="yes"  >
	<cfprocresult name="results" />
	<cfprocparam cfsqltype="CF_SQL_INTEGER" type="out" variable="return_id" dbvarname=@return_id />
</cfstoredproc>


<cfoutput>
	<cfdump var="#results#" />
</cfoutput>

Select allOpen in new window

The issue is that i'm getting the following error/stacktrace calling this code:

Lucee 5.1.0.34 Error (database)
Message	
Detail	No value specified for parameter 1
SQL	{? = call getDonations(?) }
DatabaseName	MySQL
DatabaseVersion	5.7.10
DriverName	MySQL Connector Java
DriverVersion	mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a )
Datasource	pagrorg_landingpages
Stacktrace	The Error Occurred in
C:\home\nagrprod.com\wwwroot\scheduledTasks\cfstoredproc.cfm: line 4 
2: <cfprocresult name="results" />
3: <cfprocparam cfsqltype="CF_SQL_INTEGER" type="out" variable="return_id" dbvarname=@return_id />
4: </cfstoredproc>
5: 
6: 

Java Stacktrace	lucee.runtime.exp.DatabaseException: 
 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) 
 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) 
 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) 
 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) 
 	at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2205) 
 	at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2185) 
 	at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2115) 
 	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1158) 
 	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:823) 
 	at lucee.runtime.tag.StoredProc.doEndTag(StoredProc.java:574) 
 	at scheduledtasks.cfstoredproc_cfm$cf.call(/scheduledTasks/cfstoredproc.cfm:4) 
 	at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:888) 
 	at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:833) 
 	at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:828) 
 	at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:62) 
 	at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44) 
 	at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2293) 
 	at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2284) 
 	at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2252) 
 	at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:891) 
 	at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:102) 
 	at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:62) 
 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) 
 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) 
 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) 
 	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) 
 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) 
 	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) 
 	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) 
 	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) 
 	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) 
 	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) 
 	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) 
 	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) 
 	at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:476) 
 	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) 
 	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:802) 
 	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1410) 
 	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) 
 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 
 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 
 	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 
 	at java.lang.Thread.run(Thread.java:745) 
 
Timestamp	2/28/17 11:06:21 AM MST

Select allOpen in new window