Solved

CF MYSQL insert query error

Posted on 2014-04-22
20
400 Views
Last Modified: 2014-04-24
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
Comment
Question by:izweig
  • 5
  • 5
  • 4
  • +3
20 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40016829
Check if field_186 can be null, cause you're trying to insert an empty value into it.

HTH,
Dan
0
 
LVL 28

Expert Comment

by:becraig
ID: 40016831
Are mlsnum and address allowed to be null ?

It seems it has no data to insert for those two columns into that row.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40016840
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40016845
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
 
LVL 15

Accepted Solution

by:
Gurpreet Singh Randhawa earned 500 total points
ID: 40017082
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40017134
@myselfrandhawa: MySQL will happily accept quoted values in numeric fields. It will do an automatic conversion.

'' will get converted to 0.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40017482
@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
 
LVL 15

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40017773
Thanks Dan for clarification
0
 

Author Closing Comment

by:izweig
ID: 40017894
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
 
LVL 52

Expert Comment

by:_agx_
ID: 40017957
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:izweig
ID: 40018004
Thanks..
Data types: first 2 are int, second 2 are varchar.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40018079
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
 

Author Comment

by:izweig
ID: 40018185
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40018199
Check if you have a FK to another table using the mls column.
0
 

Author Comment

by:izweig
ID: 40018225
Your are right! Thanks so much.
0
 

Expert Comment

by:sunnyboyxjx
ID: 40019124
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
 
LVL 15

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40019239
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
 
LVL 52

Expert Comment

by:_agx_
ID: 40020165
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
 
LVL 15

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40020787
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
 
LVL 52

Expert Comment

by:_agx_
ID: 40020823
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now