Solved

CF MYSQL insert query error

Posted on 2014-04-22
20
405 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 29

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 16

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 16

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
 

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 16

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 16

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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