Highlighted

How to display/use a Stored Procedure on my cfm page?

Contributor ,
Jun 28, 2018

Copy link to clipboard

Copied

I created the following stored procedure to summarize counts by week:

CREATE DEFINER=`user`@`%` PROCEDURE `TT_by_Week`()

BEGIN

SET SESSION group_concat_max_len = 1000000;

DROP TABLE IF EXISTS tmp_results;

CREATE TEMPORARY TABLE tmp_results AS

SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS ttDate, ttCategory, ttNum, Count(ttNum) tickets

FROM v_closed_tickets

WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')

GROUP BY td_type, ttDate

ORDER BY td_type, ttDate ASC;

SELECT CONCAT('

SELECT td_type, ',tickets_by_dates,'

FROM tmp_results

GROUP BY td_type WITH ROLLUP

'

)

INTO @query

FROM

(

SELECT GROUP_CONCAT(CONCAT('IFNULL(COUNT(CASE WHEN ttDate=''',actual_date,''' THEN tickets END), ''-'') AS "',col_name,'"')) tickets_by_dates

FROM (

SELECT actual_date, DATE_FORMAT(actual_date,'%m/%d') AS col_name

FROM (SELECT DISTINCT ttDate AS actual_date FROM tmp_results) AS dates

) dates_with_col_names

) result;

PREPARE statement FROM @query;

EXECUTE statement;

DEALLOCATE PREPARE statement;

END

When I call that Stored Procedure using cfdump on my cfm page, I get this:

2018-06-28_8-42-31.jpg

Questions:

  1. In general, how do you use a "stored procedure" in ColdFusion? (or reference the data?)
  2. Why are the values showing "Binary" and not my actual quantities?
  3. How do I use the correct values to create a regular html table or cfreport?

Here is my cfm page so far:

2018-06-28_14-55-34.jpg

I need my cfm page to display results in a table like this:

2018-06-28_8-13-27.jpg

I was able to figure this out! Here is the final working cfm page:

<!--- This retrieves the resultset from Stored Procedure "NewProc_Closed_TT_by_Week" --->

<cfstoredproc procedure="NewProc_Closed_TT_by_Week" datasource="care">

<cfprocresult name="rsNewProc_Closed_TT_by_Week" resultset="1">

</cfstoredproc>

<!--- This displays the resultset in a table--->

<cfif IsDefined("rsNewProc_Closed_TT_by_Week.ColumnList")>

<table width="100%" border="1">

<tr style="background: rgba(255,153,51,0.2);">

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col" delimiters=",">

<th align="center" nowrap><cfoutput>#toString(col)#</cfoutput></th>

</cfloop>

        </tr>

<cfoutput query="rsNewProc_Closed_TT_by_Week">

<tr>

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col">

<td align="center" nowrap>#toString(rsNewProc_Closed_TT_by_Week[col][CurrentRow])#</td>

</cfloop>

</tr>

</cfoutput>

</table>

</cfif>

Views

257

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to display/use a Stored Procedure on my cfm page?

Contributor ,
Jun 28, 2018

Copy link to clipboard

Copied

I created the following stored procedure to summarize counts by week:

CREATE DEFINER=`user`@`%` PROCEDURE `TT_by_Week`()

BEGIN

SET SESSION group_concat_max_len = 1000000;

DROP TABLE IF EXISTS tmp_results;

CREATE TEMPORARY TABLE tmp_results AS

SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS ttDate, ttCategory, ttNum, Count(ttNum) tickets

FROM v_closed_tickets

WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')

GROUP BY td_type, ttDate

ORDER BY td_type, ttDate ASC;

SELECT CONCAT('

SELECT td_type, ',tickets_by_dates,'

FROM tmp_results

GROUP BY td_type WITH ROLLUP

'

)

INTO @query

FROM

(

SELECT GROUP_CONCAT(CONCAT('IFNULL(COUNT(CASE WHEN ttDate=''',actual_date,''' THEN tickets END), ''-'') AS "',col_name,'"')) tickets_by_dates

FROM (

SELECT actual_date, DATE_FORMAT(actual_date,'%m/%d') AS col_name

FROM (SELECT DISTINCT ttDate AS actual_date FROM tmp_results) AS dates

) dates_with_col_names

) result;

PREPARE statement FROM @query;

EXECUTE statement;

DEALLOCATE PREPARE statement;

END

When I call that Stored Procedure using cfdump on my cfm page, I get this:

2018-06-28_8-42-31.jpg

Questions:

  1. In general, how do you use a "stored procedure" in ColdFusion? (or reference the data?)
  2. Why are the values showing "Binary" and not my actual quantities?
  3. How do I use the correct values to create a regular html table or cfreport?

Here is my cfm page so far:

2018-06-28_14-55-34.jpg

I need my cfm page to display results in a table like this:

2018-06-28_8-13-27.jpg

I was able to figure this out! Here is the final working cfm page:

<!--- This retrieves the resultset from Stored Procedure "NewProc_Closed_TT_by_Week" --->

<cfstoredproc procedure="NewProc_Closed_TT_by_Week" datasource="care">

<cfprocresult name="rsNewProc_Closed_TT_by_Week" resultset="1">

</cfstoredproc>

<!--- This displays the resultset in a table--->

<cfif IsDefined("rsNewProc_Closed_TT_by_Week.ColumnList")>

<table width="100%" border="1">

<tr style="background: rgba(255,153,51,0.2);">

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col" delimiters=",">

<th align="center" nowrap><cfoutput>#toString(col)#</cfoutput></th>

</cfloop>

        </tr>

<cfoutput query="rsNewProc_Closed_TT_by_Week">

<tr>

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col">

<td align="center" nowrap>#toString(rsNewProc_Closed_TT_by_Week[col][CurrentRow])#</td>

</cfloop>

</tr>

</cfoutput>

</table>

</cfif>

Views

258

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 28, 2018 0
Contributor ,
Jun 29, 2018

Copy link to clipboard

Copied

I was able to figure this out! Here is the final working cfm page:

<!--- This retrieves the resultset from Stored Procedure "NewProc_Closed_TT_by_Week" --->

<cfstoredproc procedure="NewProc_Closed_TT_by_Week" datasource="care">

<cfprocresult name="rsNewProc_Closed_TT_by_Week" resultset="1">

</cfstoredproc>

<!--- This displays the resultset in a table--->

<cfif IsDefined("rsNewProc_Closed_TT_by_Week.ColumnList")>

<table width="100%" border="1">

<tr style="background: rgba(255,153,51,0.2);">

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col" delimiters=",">

<th align="center" nowrap><cfoutput>#toString(col)#</cfoutput></th>

</cfloop>

        </tr>

<cfoutput query="rsNewProc_Closed_TT_by_Week">

<tr>

        <cfloop list="#rsNewProc_Closed_TT_by_Week.ColumnList#" index="col">

<td align="center" nowrap>#toString(rsNewProc_Closed_TT_by_Week[col][CurrentRow])#</td>

</cfloop>

</tr>

</cfoutput>

</table>

</cfif>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 29, 2018 0