• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Need assistance passing a CF session variable to a SQL stored procedure

Hello Experts!

I have a stored procedure that requires a session variable to run.

1) How do I pass the variable from a cfm page?
2) How do I display the stored procedure's output?

The sp output is ever changing so I can't build an HTML table and hardcode column names as they may change over time. I was hoping for a simple info dump between cfoutput tags.

The SP is already done and works flawlessly in CQL, I just need to display its fruits in CFM.

Thanks in advance!
0
CementTruck
Asked:
CementTruck
  • 9
  • 8
1 Solution
 
CementTruckAuthor Commented:
(The SP is already done and works flawlessly in CQL) I meant SQL - Sorry.
0
 
_agx_Commented:
1) How do I pass the variable from a cfm page?

Call the procedure with cfstoredproc and pass it in as a parameter.

        <cfstoredproc procedure="yourProcedureName" datasource="....">
             <cfprocparam type="IN" value="#session.yourVariable#"
                           cfsqltype="(your type)">
             <cfprocresult  name="theProcResult">
         </cfstoredproc>

              2) How do I display the stored procedure's output?

You can do it dynamically just like w/a cfquery.  Use cfprocresult to capture the output in a query. Then loop through the query columnList to display the results dynamically
         
          <!--- create array of column names --->
         <cfset colArray= listToArray(theProcResult.columnList)>
         <table>
         <cfoutput query="theProcResult">
          <tr>
                  <!--- Requires CF8+ --->
                 <cfloop array="#colArray#" index="colName">
                     <td>#theProcResult[colName][currentRow]#</td>
                 </cfloop>
           </tr>
        </cfoutput>
         </table>
0
 
CementTruckAuthor Commented:
@_agx_,

We're trying to phase out CF and so we have not spent the $ to continue to upgrade it. We don't have CF8, and if I'm not mistaken, probably have CF6. "Array" is not on the list of delimiters for my cfloop. What else can I try in it's place?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
_agx_Commented:
Then use either a "list" loop

              <cfloop list="#theProcResult.columnList#" index="colName">
                     <td>#theProcResult[colName][currentRow]#</td>
                 </cfloop>

... or a from/to loop:
              <cfloop from="1" to="#arrayLen(colArray)#" index="x">
                     <td>#theProcResult[colArray[x]][currentRow]#</td>
                 </cfloop>

                > We're trying to phase out CF and so we have not spent
                > the $ to continue to upgrade it.

If you have existing apps that would be too expensive to rewrite, might look into the free alternative cfml engines like Railo or OpenBD.  You'd get the benefits of the newer features without the cost of upgrades.
0
 
CementTruckAuthor Commented:
@_agx_,

The variable is being passed along just fine. I am now just having issues with the display portion.

I did a cfdump and it did bring back all the info I require, but it isn't in a format I can use.
0
 
CementTruckAuthor Commented:
@_agx_,

I'll try your list loop idea.
0
 
_agx_Commented:
> but it isn't in a format I can use.

How so?
0
 
CementTruckAuthor Commented:
@ _agx_,

CFDUMP:
Although the output has all the details I require the columns are not in the right sequence. I need the left column the be the userid column (which is somewhere in the middle column at this point) and the rest of the info from the stored procedure can follow horizontally.

If I could format this the way I need to (color, width, height etc.) the output would be perfect.

LOOP LIST:
Same as above, plus the loop list idea has no table header info.

---------------------------------------

If I can just format these to my liking I think we can call this project good.
0
 
_agx_Commented:
Yeah, columnList is annoyingly always alphabetical.  (I wonder who thought that was a good idea).  

I don't know if CF6 supports it, but try dumping #getMetaData(theProcResult)#.  It should return an array of column props in the sql order: column name, type, ...  If not, try this function from cflib http://cflib.org/udf/getQueryColumns.  Hopefully one of them should work under 6...
0
 
_agx_Commented:
Let me know which one works, then we can work on column headers.
0
 
_agx_Commented:
I can't build an HTML table and hardcode column names as they may
        change over time
         ...
         If I could format this the way I need to (color, width, height etc.) the
         output would be perfect.


Well the html settings have to come to come from somewhere ;-) Unless you're generating them dynamically inside the proc - you must hard code. ie Set up a structure with the settings to handle columns you DO know about. The rest will just fall through to the defaults.

             <cfset prop.columnA.width = 100>
             <cfset prop.columnA.color = "ff0000">
             <cfset prop.columnB.width = 75>
             <cfset prop.columnB.color = "ffff00">
              ....

              <cfoutput query="...">
                    <cfloop list="#columnNames#" name="colName">
                          <cfif structKeyExists(prop, colName)>
                               <td bgColor="#prop[colName].color#" ... >
                          <cfelse>
                               ... column not found. use defaults ....
                          </cfif>
                    </cfloop>
              </cfoutput>
0
 
CementTruckAuthor Commented:
@_agx_,

Thanks for working on solutions for me into the wee hours. Upon returning to work I was pleasantly greeted by this plethora of responses.

To recap:
list loop and from-to loop - both display items from the db. - formatting and column order still required.

#getMetaData(theProcResult)# - surprisingly, this shows me that items #1 and #2 (Columns titled "Fullname" and "UserID") are the first ones that are returned by the query, yet cfdump, list loop and from-to loop all show both these columns scattered throughout the output (?). Fullname is an amalgam of columns titled Firstname and Lastname. I'm wondering if I sneakily retitle these two columns as 1Fullname and 2RecID in the stored procedure that they'll be displayed in the right order (0-9, A-Z)?
0
 
CementTruckAuthor Commented:
@_agx_,

I am going to have to think about changing my online persona handle to "LoTech" ;) . I changed the column names to aaFullname and aaUserID, and I'll be damned if that didn't handle that issue beautifully. Now for the table headers etc.
0
 
_agx_Commented:
>  yet cfdump, list loop and from-to loop all show both these
       > columns scattered throughout the output

I think cfdump may do the alphabetical thing as well.  Which makes sense for debugging. It's easier to find column names that are in order. Good thing there's getMetaData for when you need to preserve the sql order :)

I'm pleased but surprised getMetaData works under CFMX. IIRC it runs an array of structures, "name" being the column name. So just loop through it using the "name" key.

I can't test this right now, but something like this should work for the data and headers. Assuming no typos ;-)


          <!--- get array of column names --->
         <cfset colArray= getMetaData(theProcResult)>
         <table>
          <!--- headers ---->
          <tr>
                 <cfloop from="1" to="#arrayLen(colArray)#" index="x">
                     <td>#colArray[x].name#</td>
                 </cfloop>
          </tr>
          <!--- data --->        
         <cfoutput query="theProcResult">
          <tr>
                 <cfloop from="1" to="#arrayLen(colArray)#" index="x">
                     <cfset colName = colArray[x].name>
                     <td>#theProcResult[colName][currentRow]#</td>
                 </cfloop>
           </tr>
        </cfoutput>
         </table>
0
 
_agx_Commented:
>  I'm wondering if I sneakily retitle these two columns as 1Fullname
     > and 2RecID in the stored procedure that they'll be displayed in the right
     > order (0-9, A-Z)?

Smart thought.  That should work. But as long as getMetaData works in MX you don't need it  :)  You just have to remember cfdump and columnList use alpha order and getMetaData another.  For this scenario, getMetaData's order will do nicely.
0
 
CementTruckAuthor Commented:
You, Sir, are "The Man!!! (no offense if you're female - insert appropriate title and salutations here to keep me in your good graces).

I have completed the project and have you to thank for it. On to other projects...cheers!
0
 
_agx_Commented:
Thanks. Glad I could help knock this item off your to-do list :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now