Skip to main content
L
Leonard_B
Inspiring
April 19, 2012
Answered

Insert zero(0) into an integer db field

  • April 19, 2012
  • 2 replies
  • 732 views

Hello,

Using:

- CF8 (8.0.1.195765)

- MySQL (5.5.19)

I am attempting to update numeric values into a MySQL DB with all fields are set to integer. The update process is being done via a web form.

If any number other then zero(0) is entered in the respective form field, the update process completes as expected. However, if a zero(0) is entered then the DB field is set to null rather then inserting the value of zero(0).

I have checked to ensure the value of zero(0) is in fact being passed from the form and evaluated as isNumeric. What am I missing here? Below is the <cfquery> update process I am using.

Thanks - Leonard B

<cfquery name="rsUpdate" datasource="*****">
Update run_counts Set 
fire = nullif(<cfqueryparam value="#form.fire_count#" cfsqltype="cf_sql_integer">,'')
, ems_rescue = nullif(<cfqueryparam value="#form.ems_rescue#" cfsqltype="cf_sql_integer">,'')
, hazardous_conditions = nullif(<cfqueryparam value="#form.hazardous_conditions#" cfsqltype="cf_sql_integer">,'')
, service_calls = nullif(<cfqueryparam value="#form.service_calls#" cfsqltype="cf_sql_integer">,'')
, good_intent = nullif(<cfqueryparam value="#form.good_intent#" cfsqltype="cf_sql_integer">,'')
, false_calls = nullif(<cfqueryparam value="#form.false_calls#" cfsqltype="cf_sql_integer">,'')
, other = nullif(<cfqueryparam value="#form.other#" cfsqltype="cf_sql_integer">,'')
, als_runs = nullif(<cfqueryparam value="#form.als_runs#" cfsqltype="cf_sql_integer">,'')
, bls_runs = nullif(<cfqueryparam value="#form.bls_runs#" cfsqltype="cf_sql_integer">,'')
</cfquery>
    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    It sounds like the result of implicit conversion. You are comparing two different different data types: integer and varchar. MySQL must convert them to a single data type for comparison. So it converts the empty string ""  to zero: ie IFNULL(0, 0) Hence the null result.  However, why not just use cfqueryparam's null attribute?

    ie   SET false_calls = <cfqueryparam value="#form.false_calls#"

                                                    cfsqltype="cf_sql_integer"

                                                    null="#not len(form.false_calls)#">

    2 replies

    L
    Leonard_BAuthor
    Inspiring
    April 20, 2012

    Thanks cfSearching

    That was the solution --

    Leonard B

    -
    -__cfSearching__-Correct answer
    Inspiring
    April 20, 2012

    It sounds like the result of implicit conversion. You are comparing two different different data types: integer and varchar. MySQL must convert them to a single data type for comparison. So it converts the empty string ""  to zero: ie IFNULL(0, 0) Hence the null result.  However, why not just use cfqueryparam's null attribute?

    ie   SET false_calls = <cfqueryparam value="#form.false_calls#"

                                                    cfsqltype="cf_sql_integer"

                                                    null="#not len(form.false_calls)#">

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