Tackling Null Values while Inserting data in data base

Question

I have the following cfquery:

<cfquery name="CQuery" datasource="XX.X.X.XXX">
        INSERT INTO DatabaseName 
            (PhoneNumber_vch,
             Company_vch,
             date_dt)

         VALUES(#PhoneNumber#,
            #Company#,
            #Date# )

    </cfquery>

There are null values in case Company name doesnt' exist and I believe becasue of that Iam getting the following error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','. 

The comma , pointed in the error message is after #Company# field. Please let me know is it's because of null values and best approach to fix it?

The values in the PhoneNumber, company and Date are extracted from a XML SOAP response with proper usage of trim function as discussed in my previous post.

Using cfif in coldfusion

Thanks

Arpent

Are you sure phone numbers are numbers and not strings? – Wanettawanfried

Switching to cfqueryparam is definitely the way to go. The cfsqltypes vary depending on the data types of your columns. For dates, use cf_sql_timestamp when dealing with a date and time, or cf_sql_date when dealing with a date only. – Mathura

Answer 1

If you use CFQueryParam like you should on any database SQL that accepts dynamic parameters you can kill two birds with one stone. First and most important, prevent SQL Injection Attacks and second you can use the attribute of the null="" to insert a NULL value into your record.

 <cfquery name="CQuery" datasource="XX.X.X.XXX">
      INSERT INTO DatabaseName (PhoneNumber_vch, Company_vch, date_dt)
      VALUES(
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(PhoneNumber)#" null="#NOT len(trim(PhoneNumber))#" />
           ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Company)#" null="#NOT len(trim(Company))#" />
           ,<cfqueryparam cfsqltype="cf_sql_timestamp" value="#Date#" null="#NOT len(trim(Date))#" />
     )
</cfquery>

Westernism

@Arpent - Keep in mind a null is different than an empty string "". Using cfqueryparam you could simply insert the empty string instead of a null. (At least for the varchar columns). It all depends on your goal, and whether the columns allow nulls. – Mathura

For the timestamp field, I'd change the null attribute to use isDate() on the date variable instead of len(). – Internee

Answer 2

You will want to use <cfqueryparam> to take care of nulls (and injection attacks)

Try

<cfquery name="CQuery" datasource="XX.X.X.XXX">
    INSERT INTO DatabaseName 
        (PhoneNumber_vch,
         Company_vch,
         date_dt)

     VALUES(
        <cfqueryparam value = "#PhoneNumber#" cfsqltype = "CF_SQL_VARCHAR">,
        <cfqueryparam value = "#Company#" cfsqltype = "CF_SQL_VARCHAR" 
          null              = "#IIF(Company EQ "", 1, 0)#">,
        <cfqueryparam value = "#Date#" cfsqltype = "CF_SQL_TimeStamp"
          null              = "#IIF(Date EQ "", 1, 0)#" >
        )

</cfquery>

Also see: cfqueryparam

Wanettawanfried

You probably want a null attribute on the date variable as well. – Internee

Answer 3

You either need to qualify your varchar entries (surround all varchar entries with single quotes, or, better would be change them to cfqueryparams;

<cfquery name="CQuery" datasource="XX.X.X.XXX">
        INSERT INTO DatabaseName 
            (PhoneNumber_vch,
             Company_vch,
             date_dt)

         VALUES(<cfqueryparam value="#PhoneNumber#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Company#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Date#" cfsqltype="CF_SQL_TIMESTAMP"> )

    </cfquery>

Billibilliard