Tuesday, 10 September 2013

cfstoredproc, cfprocparam issue with mysql order by

cfstoredproc, cfprocparam issue with mysql order by

I've been trying to set a simple cfstoredproc with cfc component which
passes two parameters to mysql stored proc and order by query.
here is the test code:
test.cfm
<cfset sqlN = createObject ("component","cfc.testNews")>
<cfset loadNews = sqlN.getNews(displayFrom=0, sortNewsBy="Date")>
<cfoutput query="loadNews">
#newsID#<br />
</cfoutput>
testNews.cfc
<cffunction name="getNews" access="public" output="false">
<cfargument name="displayFrom" type="numeric" required="true">
<cfargument name="sortNewsBy" type="string" required="true">
<cfstoredproc procedure="spGetNews" datasource="mydatabase">
<cfprocparam value="#displayFrom#" cfsqltype="cf_sql_integer">
<cfprocparam value="#sortNewsBy#" cfsqltype="cf_sql_varchar">
<cfprocresult name="qNews">
</cfstoredproc>
<cfreturn qNews>
</cffunction>
in mysql storedproc i have:
select newsID
from news
order by sortNewsBy desc limit displayFrom,25;
although cf does not throw any error msg, as i can see cfprocparam, more
specifically cfsqltype is causing the issue as it passes nothing!? i've
also tried to do the same with cfquery and cfqueryparam tag on .cfm page
(instead of cfstoredproc and cfc) and the problem still persists.
cfdump on cfm page returns ... order by ? desc limit ?,25;
I know how to make it work with cfquery and cfqueryparam on cfm page but
have no idea what to do with cfprocparam, so is there a workaround for
this issue. I would really appreciate your help on this one.
EDIT here is the entire myslq stored proc
CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetNews`(in displayFrom
int, sortNewsBy varchar (15))
BEGIN
select newsID
from news
order by sortNewsBy desc limit displayFrom,25;
/*In MSSQL Server IFNULL is not supported, use ISNULL instead*/
END

No comments:

Post a Comment