Problem with query result in session variable

[biene22]

Hi,

I put a query result into a session variable.

The result set contains values like "001", but when I dumped the session variable it becomes the value  "1" (numeric).

In the database it is a varchar2 column, but why coldfusion converts it to a number?

How can I tell coldfusion not to convert the string to number? I need the value with leading zeros.

regards Claudia

[Adam Cameron.]

Hi Claudia

Can you please provide us with:

* the DB system you're using (sounds like it's Oracle if you've got VARCHAR2s?);

* the version of CF you're running;

* some self-contained code (including sample data) that demonstrates what you're seeing.

Please ALWAYS provide this sort of information when you post a question: it makes it a lot easier to answer it 😉

I cannot replicate what you're seeing on a hastily thrown-together test rig, running CF9.0.1 & MySQL 5.5

--

Adam

[Dan_Bracuk]

Here it is with oracle

<cfquery name="y" datasource="some oracle db">
select '007' bond
from dual
</cfquery>
<cfset session.x = y["bond"][1]>
<cfdump var="#session.x#">

I see 007 when I run it.

[biene22]

Hi Adam,

sorry for the missing information.

DB-System is Oracle 10gR2 and ColdFusion Version is  7.0.2 Standard Edition.

I try to explain my problem more detailed:

On the first CF page I have a list of data records. If the user wants to see more detailed information of some records, he can mark the record with the help of a checkbox and  the information of the checked record is sent to the action page where the detailed information is shown.

On first execution of the action page

I put the required information of the marked records in an array of structure

<cfif first>

<cfset data = ArrayNew(1)>
        <cfloop  from="1"  to="#form.n#" index="i">
            <cfif isdefined('form.checkbox_#i#')>    
             <CFSET data = StructNew()>            
             <CFSET data.field1 = "#evaluate('form.field1_#i#')#">
             <CFSET data.field2  = "#evaluate('form.field2_#i#')#">                
            </cfif>
        </cfloop>

</cfif>

Then I convert the array to a query and put it in a session variable.  I do this, because later I use this session.dataQuery for some where-conditions or to make a query of this query...

<CFSET session.dataQuery = ArrayOfStructuresToQuery(data)>

<cfdump var="#session.dataQuery#">

At first execution the values are correct. For example value "001" is  shown as "001".

But when the actionpage is executed again (user can sort or filter the data)

value "001" is now "1". And then my where-conditions and so on are wrong.

<cfdump var="#session.dataQuery#">

So, it always happens when the action page is executed again or in other words when the session variable "session.dataQuery" is read second time.

Hope my problem is more clear.

Best regards

Claudia

For completion the function ArrayOfStructuresToQuery:

<cfscript>
function arrayOfStructuresToQuery(theArray){
    var colNames = "";
    var theQuery = queryNew("");
    var i=0;
    var j=0;
    //if there's nothing in the array, return the empty query
    if(NOT arrayLen(theArray))
        return theQuery;
    //get the column names into an array =   
    colNames = structKeyArray(theArray[1]);
    //build the query based on the colNames
    theQuery = queryNew(arrayToList(colNames));
    //add the right number of rows to the query
    queryAddRow(theQuery, arrayLen(theArray));
    //for each element in the array, loop through the columns, populating the query
    for(i=1; i LTE arrayLen(theArray); i=i+1){
        for(j=1; j LTE arrayLen(colNames); j=j+1){
            querySetCell(theQuery, colNames, theArray[colNames], i);
        }
    }
    return theQuery;
}
</cfscript>

[Adam Cameron.]

Hi Claudia.

Can you pls include your form code too.  The code you post should be a complete and working reproduction case of what you're seeing, and we should be able to save it locally and run it and we should see what you're seeing.  What we should NOT be doing is having to write our own code to make your code work.

This should not necessarily be your actual form / action page as they'll - no-doubt - have code that is irrelevant to this: so get rid of that clutter.  Equally, as part of your own troubleshooting you should be defactoring your code as much as poss to isolate elements that don't contribute to the issue, leaving you solely with pared down code that does demonstrate the issue.

--
Adam

[Dan_Bracuk]

If I do this on one page:

<cfscript>
session.x = QueryNew("y","varchar");
QueryAddRow(session.x, 1);
QuerySetCell(session.x, "y", "002", 1);
</cfscript>

<cfdump var="#session.x#" metainfo="no">

and this on another

<cfdump var="#session.x#" metainfo="no">

I see the leading zeros on both pages.

[insuractive]

This is a little off topic, but I saw your code and wanted to make sure you were aware that you could use the Form scope with the structure-key notation to accomplish the dynamic variable setting without the use of the Evaluate() method - always a good thing when you can avoid evaluate().

e.g.

<CFSET data.field1 = form["field1_#i#"]>

instead of

<CFSET data.field1 = "#evaluate('form.field1_#i#')#">

Obviously not a huge security risk here since your variable "i" is your loop iterator, but I thought I would mention it.

Unfortunately, I don't have access to an oracle DB to test your particaly problem here.