• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

CF MYSQL insert query error

This must be so simple, and it's driving me mad!
Can you see an error here? Swear it was working, and it just broke!

(it inserts 26 records before throwing error)


INSERT INTO
tbloffmarket
(field_sysid,NumImages,MLSNum,address)
VALUES
(#field_sysid#,#field_186#,'#field_248#','#field_14#');

Open in new window


field_sysid is integer, field_186 is integer, other two are strings.

Error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''','')' at line 5
 
The error occurred in  line 35
33 : (field_sysid,NumImages,MLSNum,address)
34 : VALUES
35 : (#field_sysid#,#field_186#,'#field_248#','#field_14#');
36 : </cfquery>
37 : </cfoutput>
VENDORERRORCODE        1064
SQLSTATE        42000
SQL         INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,,'','');
0
izweig
Asked:
izweig
  • 5
  • 5
  • 4
  • +3
1 Solution
 
Dan CraciunIT ConsultantCommented:
Check if field_186 can be null, cause you're trying to insert an empty value into it.

HTH,
Dan
0
 
becraigCommented:
Are mlsnum and address allowed to be null ?

It seems it has no data to insert for those two columns into that row.
0
 
Dan CraciunIT ConsultantCommented:
Just tested and this is illegal:
INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,,'','');

You need a value for all the fields when using INSERT INTO VALUES. If it's null you have to specifically specify it. So the correct syntax would be

INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,NULL,'','');

Open in new window

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Dan CraciunIT ConsultantCommented:
Quote the numeric value and the query will work even when empty:
INSERT INTO
tbloffmarket
(field_sysid,NumImages,MLSNum,address)
VALUES
(#field_sysid#,'#field_186#','#field_248#','#field_14#');

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Another way is, you should make it conditional:

INSERT INTO
tbloffmarket
(field_sysid<cfif isDefined('field_186') and field_186 neq ''>,NumImages</cfif>,
MLSNum,address)
VALUES
(#field_sysid#<cfif isDefined('field_186') and field_186 neq ''>,#field_186#</cfif>,'#field_248#','#field_14#');

and also check if the field: field_186 is not set a not null in the database table, else it will throw error. check what is the datatype of the column is: if it is numeric, you can use my code and if its varchar, above code by @Dan Craciun will work

regards
0
 
Dan CraciunIT ConsultantCommented:
@myselfrandhawa: MySQL will happily accept quoted values in numeric fields. It will do an automatic conversion.

'' will get converted to 0.
0
 
_agx_Commented:
@izweig - The problem is exactly what Dan explained here.  While you could get around the issue by enclosing all values in quotes, IMO a more robust solution is to switch to using cfqueryparam. For several reasons:

1) Your original query is vulnerable to sql injection. Cfqueryparam helps protect against that. It should always be used on ALL variable query parameters.

2) When executing queries multiple times, it helps boost performance by using bind variables.

Something along the lines of the query below. I don't know the data types of your columns, so I guessed: integer and varchar. Adjust the cfsqltype's if needed.

INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#VAL(field_186)#" cfsqltype="cf_sql_integer">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)

Open in new window


EDIT Note, the exact syntax depends on what you want to happen when one of the numeric values is empty. The example above inserts "0", but you could easily insert NULL instead, using the tag's "null" attribute, ie


   <cfqueryparam value="#field_sysid#"
                   cfsqltype="cf_sql_integer"
                   null="#NOT IsNumeric(field_sysid)#" >
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Thanks Dan for clarification
0
 
izweigAuthor Commented:
Thanks all.
Making the query conditional was the solution.
However all other solutions seemed like they should have worked but were still causing errors.
Many thanks!
0
 
_agx_Commented:
However all other solutions seemed like they should have worked but were still causing errors.

Then there's almost certainly an error in your code, or possibly my (untested) example, because cfqueryparam definitely works :)

Respectfully ... I'd strongly recommend not using that query because it exposes your db to sql injection! If still want help with switching to cfqueryparam, just post the data types of your 4 columns and I'd be happy to post a working example.
0
 
izweigAuthor Commented:
Thanks..
Data types: first 2 are int, second 2 are varchar.
0
 
_agx_Commented:
I think there was just an error in your code. I copied and pasted my 1st example, and it worked fine:

Test Table:
create table tbloffmarket
(field_sysid INT
,NumImages INT
,MLSNum varchar(255)
, address varchar(255)
)

Open in new window


Code:

<cfset yourDSN = "Name of your DSN">
<!--- for testing only --->
<cfset field_sysid ="260818963">
<cfset field_186 ="">
<cfset field_248 = "">
<cfset field_14= "">

<cfquery datasource="#yourDSN#">
INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#VAL(field_186)#" cfsqltype="cf_sql_integer">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)
</cfquery>

Open in new window


If you prefer to insert NULL, instead of 0, when field_186 is empty, then use the 2nd example.

<cfquery datasource="#yourDSN#">
INSERT INTO tbloffmarket ( field_sysid, NumImages, MLSNum, address )
VALUES (
   <!--- VAL() converts non-numeric values to zero --->
   <cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
  , <cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer" null="#NOT IsNumeric(field_186)#">
   , <cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">
  ,  <cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">
)
</cfquery>

Open in new window

0
 
izweigAuthor Commented:
Thanks, much appreciated.

Is all working fine, except now...
(separate issue)
After my successful insert of the data into  tbloffmarket
I now am trying to delete the data that was inserted from the table (property11) where the data is from.

I am getting an error which references a column name which does not exist in the table.

Error
SQL query:

Delete FROM `property11` WHERE `Class`="11 off market"
MySQL said: Documentation

#1048 - Column 'mls' cannot be null


There is no column named 'mls' in property11

Do you know what this is?
0
 
Dan CraciunIT ConsultantCommented:
Check if you have a FK to another table using the mls column.
0
 
izweigAuthor Commented:
Your are right! Thanks so much.
0
 
sunnyboyxjxCommented:
INSERT INTO tbloffmarket (field_sysid,NumImages,MLSNum,address) VALUES (260818963,,'','');
no value for NumImages, you transfer null to field_186 at the 26th times.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Hi, You can even use cfqueryparam too for this:

INSERT INTO
tbloffmarket
(field_sysid<cfif isDefined('field_186') and field_186 neq ''>,NumImages</cfif>,
MLSNum,address)
VALUES
(<cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
<cfif isDefined('field_186') and field_186 neq ''>,
<cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer">
</cfif><cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">,<cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">);

Open in new window

0
 
_agx_Commented:
INSERT INTO
tbloffmarket
(field_sysid<cfif isDefined('field_186') and field_186 neq ''>,NumImages</cfif>,
MLSNum,address)
VALUES
(<cfqueryparam value="#VAL(field_sysid)#" cfsqltype="cf_sql_integer">
<cfif isDefined('field_186') and field_186 neq ''>,
<cfqueryparam value="#field_186#" cfsqltype="cf_sql_integer">
</cfif>
<cfqueryparam value="#field_248#" cfsqltype="cf_sql_varchar">,<cfqueryparam value="#field_14#" cfsqltype="cf_sql_varchar">);

CFIF's are kind of bulky and less readable. Simpler to just use the "null" attribute. Then there's no messing around with column list.  Also, if values must be numeric then IsNumeric() is a more robust than just checking for an empty string.

VALUES (
    <cfqueryparam value="#field_186#"
            cfsqltype="cf_sql_integer"
            null="#NOT IsNumeric(field_186)#">
      , ....
)
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
I agree with your point, but sometime in cases ifdefined works good, if isdefined is bulky and heavy as it is, we can always switch to structkeyexists
0
 
_agx_Commented:
Yeah, sometimes you do need structKeyExists, but this isn't one of those times ;-). In this specific case, using a cfif in both the column list and values() clause creates extra clutter, decreasing readability IMO. That's why I suggest using the simpler "null" attribute.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now