Skip to main content
B
billp84606427
Participating Frequently
April 18, 2023
Question

ColdFusion refuses to print the string "00"

  • April 18, 2023
  • 1 reply
  • 612 views

I have a table with the column 

StatusSubStr char(2)

In it are many instances of "00"

I want it to print in a table cell with another string from the table

StatusStr char(2)

which contains either 1 or 2 number characters

#FQ.StatusStr#.#FQ.StatusSubStr#

 

prints as an example 1.01 or 1.10

but when StatusSubStr = "00"

it prints 1 or 2

it only prints the string StatusStr

They were both integer fields in the database and I tried every kind of cast and concatenation I could find doing them both in mariadb before returning the results and in Coldfusion with the results.  I finally changed the database to char(2) on both fields with alot of confidence that it would work.  I filled all the StatusSubStr directly with "00" because some were null, and with lpad(statussubstr, 2, "0") so that the field has every record filled with 2 characters.  And cold fusion still refuses to print the string "00". 

 

Any help would be super appreciated.  This piece should have taken me less than 1/2 hour and I've been "messing" (the good word) with it for over an hour and a half.  Thank you in advance.

 

 

    This topic has been closed for replies.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 18, 2023

    The conversion from "00" to "" is likely to have happened in the database. It is unlikely for ColdFusion to do that. To check this, just dump the query. For example, by placing the following code after the query:

     

    <cfdump var="#FQ#"> <cfabort>

     

    Is the query receiving the FQ.StatusSubStr values as "00"?

     

    If not, was the query cached perhaps? Could you share the query code? 

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 18, 2023

    A test in ColdFusion 2021 Update 6:

    <cfscript>
    myQuery = queryNew("id,StatusStr,StatusSubStr","Integer,integer,varchar", 
                [ 
                        {id=1,StatusStr=1,StatusSubStr="00"}, 
                        {id=2,StatusStr=2,StatusSubStr="01"}, 
                        {id=3,StatusStr=3,StatusSubStr="02"},
                        {id=4,StatusStr=4,StatusSubStr="00"}
                ]); 

    writeDump(myQuery);
    
</cfscript>

<cfoutput query="myQuery">
	#StatusStr#.#StatusSubStr# <br>
</cfoutput>

     

    The output is:

     

     

     

    B
    billp84606427Author
    Participating Frequently
    April 18, 2023

    Thank you.  I was pretty sure it was happening in CF but your test was a good idea.  The result of the dump is:

    so you can see it's returning the string "00"

    The query is:

    <cfquery name="FQ">
    SELECT id
        , StatusName
        , StatusStr
        , StatusSubStr
     FROM AdminStatus ORDER BY cast(StatusSeq as int), StatusSubSeq;
    </cfquery>
    pretty straight forward with StatusStr and StatusSubSeq both char(2) in the database
    the data in the cell is again, very straight forward.
    <td>
            #FQ.StatusStr#.#FQ.StatusSubStr#
    </td>
    I have tried adding the decimal point to the value StatusSubStr to no avail.  
    I am desperate enough now to make it 3 column with  the decimal point in it's own column but I'm not sure that is going to  work either.  I've tried bringing back the nulls that were the original source of the problem and using isnull() to test and if it is then "00" and that doesn't work.  This is why I like Strongly Typed languages.  I hate spending trial and error loops to defeat implicit type conversion which is what I assume it is doing. Actually next I'm going to change back to int's for the datatypes in the database and see if I can use numberformat() in the cell.  Incidentally someone else wrote this and I'm trying to maintain it and the number of stupidities is amazing.  No CSS table definitions, all in line.  No use of dates, in one table he entered the date as a varchar with no formating help so there is everywhich way of date, to  over come that he created a "datecode" where today "04/18/2023" is stored as an integer 20230418.  This was not written in the days before date datatypes were not in languages.  It is only 7 years old.  Sorry for the rant.  Anyway thank you very much for helping and I look forward to hearing from you and at some point having this solved.
     
     
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices