ColdFusion is giving error when executing database query

Question

Can somebody please suggest what is wrong with this code?

<cfquery name="getPreviousDBentries" datasource="#application.datasource#">
    select image, image_b,
    image_c,image_d,image_e, image_f from used_listings_v2 where id = '#edit#'
</cfquery>

<cfdump var="#getPreviousDBentries#">
<cftry>
    <cfquery name="updateToNull" datasource="#application.datasource#">
        <cfif len(getPreviousDBentries.image) neq 0>
            Update used_listings_v2 SET image = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_b) neq 0>
            Update used_listings_v2 SET image_b = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_c) neq 0>
            Update used_listings_v2 SET image_c = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_d) neq 0>
            Update used_listings_v2 SET image_d = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_e) neq 0>
            Update used_listings_v2 SET image_e = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_f) neq 0>
            Update used_listings_v2 SET image_f = NULL where id = '#edit#'
        </cfif>
    </cfquery>

<cfcatch>
<cfdump var="#cfcatch#">
</cfcatch>

</cftry>

I am getting correct output when the image column has some value i.e. image, image_b,image_c, image_d,image_e, image_f are not null.

But it's giving me an error saying error executing database query when image, image_b,image_c, image_d,image_e, image_f are null.

Please suggest possible solutions.

Answer 1

The reason why you're getting an error message is because all of the <cfif> conditions are false, it's like executing a <cfquery> with no code inside of it. So you will need to add an additional condition outside the <cfquery> block to test and make sure at least one of your UPDATE commands will execute.

<cfquery name="getPreviousDBentries" datasource="#application.datasource#">
    select image, image_b,
    image_c,image_d,image_e, image_f from used_listings_v2 where id = '#edit#'
</cfquery>

<cfif NOT (len(getPreviousDBentries.image) eq 0> AND 
      len(getPreviousDBentries.image_b) eq 0 AND
      len(getPreviousDBentries.image_c) eq 0 AND
      len(getPreviousDBentries.image_d) eq 0 AND
      len(getPreviousDBentries.image_e) eq 0 AND
      len(getPreviousDBentries.image_f) eq 0)>

    <cftry>
        <cfquery name="updateToNull" datasource="#application.datasource#">
            <cfif len(getPreviousDBentries.image) neq 0>
                Update used_listings_v2 SET image = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_b) neq 0>
                Update used_listings_v2 SET image_b = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_c) neq 0>
                Update used_listings_v2 SET image_c = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_d) neq 0>
                Update used_listings_v2 SET image_d = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_e) neq 0>
                Update used_listings_v2 SET image_e = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_f) neq 0>
                Update used_listings_v2 SET image_f = NULL where id = '#edit#'
            </cfif>
        </cfquery>

        <cfcatch>
            <cfdump var="#cfcatch#">
        </cfcatch>

    </cftry>
</cfif>

Answer 2

@user12031119's answer already explains why the error occurs. As far as the code, instead of constructing multiple queries, a single query would be more maintainable. Use a CASE statement or your database's version of IIF to update the columns only if the value is empty. The exact syntax is dbms specific.

For example, say the image columns are all populated except for "image_d":

Sample

id  | image     | image_b    | image_c  | image_d
1   | arrow.png | up.png     | down.png | (empty string)    

DDL (SQL Server)

CREATE TABLE used_listings_v2 
(
   id int 
   , image varchar(50)
   , image_b varchar(50)
   , image_c varchar(50)
   , image_d varchar(50)
)
;


INSERT INTO used_listings_v2 (id, image, image_b, image_c, image_d)
VALUES (1, 'arrow.png', 'up.png', 'down.png', '')
;

Use a CASE to check the length of the existing values, and set them to NULL only if the contain an empty string (i.e. length = 0).

-- Example using CASE
SET Column = CASE WHEN LEN(Column) = 0 THEN NULL ELSE Column END

-- Example using IIF
SET Column = IIF( LEN(Column) = 0, NULL, Column ) 

So this query will preserve any existing value and only update empty columns to null:

SQL Fiddle

UPDATE used_listings_v2
SET    image = CASE WHEN LEN(image) = 0 THEN NULL ELSE image END
       , image_b = CASE WHEN LEN(image_b) = 0 THEN NULL ELSE image_b END
       , image_c = CASE WHEN LEN(image_c) = 0 THEN NULL ELSE image_c END
       , image_d = CASE WHEN LEN(image_d) = 0 THEN NULL ELSE image_d END
WHERE id = 1

Results

id  | image     | image_b    | image_c  | image_d
1   | arrow.png | up.png     | down.png | (null)    

NB: In the actual cfquery, wrap the #edit# variable in cfqueryparam to help protect the query against sql injection.

Having said that though, having a bunch of similarly named columns all storing the same thing (i.e. image, image_b, image_c, .., image_N) is a strong indicator you need to normalize the model. Storing the images in rows instead of columns, provides much greater flexibility and would simplify tasks like this.