why won't zero work here?

[richardELeach]

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

[richardELeach]

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

[ilssac]

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.

[richardELeach]

<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]

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.

[ilssac]

Lets try this one.

Thats almost visible.

[Dan_Bracuk]

Regarding your query, what does this return when you run it in Cold Fusion:

select count(*)

from jobstatus
where  jobstatus=0

Regarding queries in a loop vs using a list, you might be able to achieve your goal more efficiently if you make a single db query using a list, followed by a loop that contains a Q of Q.

[BKBK]

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).

[Reed_Powell-ttnmOb]

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