How do I Trim a value coming from a CFC?

[solaced]

Hi!

I have a simple CFC which gets records from a database.

I have then created a ColdFusion page and used <cfobject> to make an instance of the CFC. I then use this instance to populate a <cfselect> form element.

The problem is that the field has too many spaces in it so I need to Trim it.

Here is my code:

<cfobject component = "globalFilters" name="globalFilters">

<cfinvoke component="#globalFilters#" method="filters_Status" returnvariable="rsStatus">

<cfselect name="Status"

query="rsStatus"

value="#Trim(Status)#"

display="Status">

</cfselect>

Unfortunately it CF says "Status" is not defined and fails. But if I remove the Trim method then it works fine.

How do I trim the value in the select list?

[Adam Cameron.]

Rather than trimming it after the fact, why don't you just not add the spurious whitespace in the first place?  Aren't you attempting to treat the symptom, not the problem?

--
Adam

[Dan_Bracuk]

Once you get rsStatus, loop through it and use QuerySetCell to trim the status values.

[solaced]

I cant remove whitespace at the database level as i have no control over the sql

[BKBK]

You could already remove the extra spaces in the query in the method filters_Status of globalFilters.cfc. Many  SQL dialects support the trim function.

[solaced]

The CFC function executes a stored procedure so i cant do anything with the sql code

[Adam Cameron.]

I cant remove whitespace at the database level as i have no control over the sql

Sure, but your proc is called in your function. And then your function returns it. So your function can do the trimming before you return it to your calling code.

And can't you tell your DBAs they've written a sloppy proc?  I can't see how it's what they want it to do, so they'd probably be pleased to hear about it.

Also I note you're returning rsStatus from your method call, but then referring to it as status. That's why you're getting the error saying status doesn't exist.

This should have been easy for you to spot: the first thing you should do when you are trying to use a variable that doesn't exist is to look for it. And the first port of call in this situation is to a) look at your code; b) start dumping stuff out, looking for unexpected stuff.

--

Adam

[BKBK]

Adam Cameron. wrote:

Also I note you're returning rsStatus from your method call, but then referring to it as status. That's why you're getting the error saying status doesn't exist.

There is a misunderstanding here. Solaced was hoping he could apply trim(status) within the context of a query, that is, trim(rsStatus.status). Apparently, ColdFusion just expects an existing column name.

[Adam Cameron.]

Sorry you're right. I blanked out that it was a <cfselect>.

And as you allude to, this is the problem: CF is expecting a column name here, not an expression, which is why the trim() won't work in this context.

So it's back to what I said... either get the proc fixed or trim the values in that column in the function.  Best to get the proc fixed.

Cheers.

--

Adam

[BKBK]

Until you get the stored procedure fixed, there is another solution. It is similar to the one Dan Bracuk suggested.

Leave the existing values of the query intact, and use queryAddColumn to add a new column to the query. Which column? Naturally a column containing the respective trimmed status values. Something like this:

<!--- Store trimmed status values in array --->

<cfset statusTrimmedArr = arrayNew(1)>

<cfset n=0>

<cfloop query="rsStatus">

<cfset n=n+1>

<cfset statusTrimmedArr = trim(rsStatus.status)>

</cfloop>

<!--- Add new column containing trimmed status values to query --->

<cfset queryAddColumn(rsStatus, "statusTrimmed", "varchar", statusTrimmedArr)>

You would then use statusTrimmed in place of status.

[solaced]

I'm a bit of a novice at ColdFusion so not sure how to Trim the column values within the <cfstoredproc> section? This is my CFC

<cfcomponent>

    <cffunction name="filters_FeeStatus4">

    <cfstoredproc datasource="Web" procedure="dbo.spFilters_Status">

    <cfprocresult name="rsStatus" />

    </cfstoredproc>  

    <cfreturn rsStatus>

</cffunction>

The stored procedure only returns one colum called "Status". So I would imagine that to Trim it I have to do something like #Trim(rsStatus.Status)# but where would I do this? Looking at BKBK's example it seems I would have to create a new array to store the recordset's values and then loop through them and trim them. Seems a bit tedious... isn't there anyway I could ask ColdFusion to do the trimming on the fly.

Side Note: Because I am working with stored procedures I don't always know what the output columns from the database will be. Is there any way that allows me to see what columns are going to be returned from within the IDE itself? So when I create my <cfselect>  element and choose the intput to be a query ( <cfselect query="rsStatus" value=  > ) that I will automatically get a code hint saying what columns are in "rsStatus" when I start typing the value= part? Pretty much like Microsoft's Intellisense but I'm not sure if that even does this kind of thing.

[Dan_Bracuk]

You don't do it within the cfstoredproc block of code.  You do it afterwards.

As far as seeing what the proc returns, cfdump is your freind.  You can dump the query object or the columnlist variable.  If you want to make the columnlist easier to read, the ListToArray function is handy.