Skip to main content
R
richardELeach
Inspiring
July 2, 2010
Question

why won't zero work here?

  • July 2, 2010
  • 4 replies
  • 1036 views

Running this loop for my query through ColdFusion will not yield any results for the value of 0 (zero), however the values 1 and 2 work as expected. When I run this same query (minus the CF code, just using the numbers 0, 1, and 2) through the database directly I get my expected results for 0. Anyone know what I need to do to get results for 0?

<cfloop list="0,1,2" delimiters="," index="i">

    <cfquery name="jobIdList" datasource="#request.DSN#">
        select distinct jobid
        from jobstatus
        where jobstatus=<cfqueryparam value="#i#" cfsqltype="cf_sql_integer">
        order by jobid
    </cfquery>

</cfloop>

Thanks in advance,

Rich

    This topic has been closed for replies.

    4 replies

    R
    Reed_Powell-ttnmOb
    Inspiring
    July 13, 2010

    Is the list of values in your CFLOOP list ("0,1,2") static?  Unless you have a reason to worry about injection, just replace the CFQUERYPARAM with a val() function call and see if that works.

    -reed

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    July 9, 2010

    Check whether the list in your code contains the letter O instead. Also make sure cfsqltype matches the datatype for the jobstatus column (there might be subtle considerations like signed/unsigned integers).

    ilssac
    ilssac
    Inspiring
    July 2, 2010

    There is no obvious problem with your code.  What are the expected results?  What results are you getting?

    And the bonus question:

    Why are you using a loop to create three queries rather then a single query with an IN clause?


    I.E.

       WHERE jobstatus IN (<cfqueryparam value="0,1,2" list="yes" cfsqltype="cf_sql_integer">)

    Providing the actual SQL generated by your code that was sent to the database as well as some idea of your database design would also be useful additions to a question like this.

    R
    richardELeachAuthor
    Inspiring
    July 2, 2010

    <cfloop list="0,1,2" delimiters="," index="i">

         <cfquery name="jobIdList" datasource="#request.DSN#">
             select distinct jobid
            from jobstatus
            where  jobstatus=<cfqueryparam value="#i#" cfsqltype="cf_sql_integer">
             order by jobid
        </cfquery>

    </cfloop>

    There is a database column that actually contains a value from 0 to 5 (in my loop I only need the values 0, 1, 2). In this case 0 is a genuine value and needs to be considered as such by CF. When I run the above CF code I get an empty string returned from CF during the iteration of the loop when 0 is the index value. When I run the code below directly against my PostgreSQL DB

    select distinct jobid
    from jobstatus
    where  jobstatus=0
    order by jobid

    I get 5 records returned, which is my expected result set.

    The IN clause is purposely being omitted as additional processing is needed in the CF template. For simplicity's sake I deleted the rest of the code so as to focus solely on the query issue.

    Thanks so much for your help,

    Rich

    ilssac
    ilssac
    Inspiring
    July 2, 2010

    richardELeach wrote:

    When I run the above CF code I get an empty string returned from CF during the iteration of the loop when 0 is the index value.

    How did you determine that you got an "empty string returned from CF"?  Was it from the database side or the application server side?

    When I ran a similar query against the cfartgallery embedded derby database, I get the expected results.

    Message was edited by: Ian Skinner  Ok, that image is worthless.

    R
    richardELeachAuthor
    Inspiring
    July 2, 2010

    ... sorry, forgot to mention client is on CF8


    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices