Solved

MySQL 5.1 - Incorrect integer value: '' for column

Posted on 2011-02-12
12
1,601 Views
Last Modified: 2012-06-27
windows 2003, MySQL 5.1, Railo3.1

When I try to Parse a text file into my table I get this error: Incorrect integer value: '' for column 'ApproxAcreage' at row 1. (the ApproxAcreage field is sometimes blank) the Code works fine on my other server running MySQL 4.1. If I change the field type from "int" to "varchar" it parses with no error. I did some research on this issue and found that it could be related to "Strict mode" so I commented out that line in "my.ini" file restarted the server and I still get the error?? Any help is appreciated
0
Comment
Question by:Bang-O-Matic
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 18

Assisted Solution

by:Matthew Kelly
Matthew Kelly earned 50 total points
ID: 34881219
It appears to be a common problem between 4.1 and 5.1: http://www.john-sieber.com/post.cfm/incorrect-integer-value-for-column-x

Is the ApproxAcreage set to 'not null' or does it allow 'null' values?

Are you parsing through a script you wrote or importing the text file through a third party software/mysql bin executable?

If you are parsing yourself, make sure ApproxAcreage allows NULL values and then when parsing replace all ApproxAcreage values where length is zero with NULL.

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34881318
This question is also in the coldfusion zone...

A similar error can be thrown from the cfqueryparam used in a query in Coldfusion.

To resolve this, you need to add the NULL parameter to specify if the value is empty

ApproxAcreage = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.ApproxAcreage#" null="#NOT len(form.ApproxAcreage)#">

0
 

Author Comment

by:Bang-O-Matic
ID: 34885095
The ApproxAcreage field is set to "int" allow null values, and I am attaching a sample of my code.

I tried parsing the file while replacing empty values with "NULL" and got another error: Incorrect integer value: 'NULL' for column. Maybe I was not Parsing it correctly? Heres how I did it:

<cfif trim(listgetat(record,2,'|')) is ''>
<cfset ApproxAcreage = 'NULL'>
<cfelse>
<cfset ApproxAcreage = '#trim(listgetat(record,2,'|'))#'>

see attached code




 
<cffile action="read"
file="C:\www\idx\cgmls\ftp\txt\listings-residential.txt"
		variable="txtFile">
			
	
	<cfloop 
		index="record"
		list="#Replace(Replace(Replace(txtFile,'''','''''','all'),'|','','all'),chr(9),'| ','all')#"
		delimiters="#chr(13)##chr(10)#">
		

	

	<cfif trim(listgetat(record,1,'|')) is ''>
		<cfset ListID = ' '>
	<cfelse>
		<cfset ListID = '#trim(listgetat(record,1,'|'))#'>
	</cfif>
	<cfif trim(listgetat(record,2,'|')) is ''>
		<cfset ApproxAcreage = ' '>
	<cfelse>
		<cfset ApproxAcreage = '#trim(listgetat(record,2,'|'))#'>
	</cfif>

<!--- SQL COMMAND TO INPUT THE LISTINGS DATA FROM THE TEXT FILE TO THE  DATABASE --->
	<cfquery name="qryInsert" datasource="CGMLS">
		  INSERT INTO listings (ListID, ApproxAcreage)

VALUES ('#ListID#', '#ApproxAcreage#')

</cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34885372
Incorrect integer value: 'NULL'

If it's an integer value, it shouldn't be enclosed in quotes.  Otherwise, MySQL treats it as a literal string instead of the keyword NULL

ie   This:  
        INSERT INTO Table (Col) VALUES (  'NULL' )
Is not the same as this:  
         INSERT INTO Table (Col) VALUES (  NULL )

As gdemaria suggested, use cfqueryparam's null attribute to insert NULL when the value isn't numeric.

ie

VALUES ( 
.... 
,  <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ApproxAcreage#" null="#NOT isNumeric(ApproxAcreage)#">
) 

Open in new window

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 225 total points
ID: 34885384

This statement will never be true... unless the variable record is empty

  <cfif trim(listgetat(record,1,'|')) is ''>

list functions ignore empty elements by definition.   So it will skip over any empty elements.

For example,

 1,2,3,,5,,7

The 4th element in the above list is 5, the 5th element is 7


To get around this, convert the list to an array with the feature to include empty elements set to true

<cfset Rec = listToArray(record,"|",true)>


I am not really sure what the replace statements are doing, so I left them, be here's the rest ...

<cffile action="read"
file="C:\www\idx\cgmls\ftp\txt\listings-residential.txt"
		variable="txtFile">
			
	
	<cfloop 
		index="record"
		list="#Replace(Replace(Replace(txtFile,'''','''''','all'),'|','','all'),chr(9),'| ','all')#"
		delimiters="#chr(13)##chr(10)#">
		
    <cfset rec = listToArray(record,"|",true)>
    <cfset listId = trim(rec[1])>
    <cfset ApproxAcreage = trim(rec[2])>
    
	<cfquery name="qryInsert" datasource="CGMLS">
	   INSERT INTO listings (ListID, ApproxAcreage)
       VALUES ('#ListID#'
            , <cfif len(ApproxAcreage)>'#ApproxAcreage#'<cfelse>NULL</cfif>)
     </cfquery>

Open in new window

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 225 total points
ID: 34885389
<cfif trim(listgetat(record,1,'|')) is ''>
            <cfset ListID = ' '>
     

Also, do you really need CFIF conditions? It seems like you could just extract the values and insert them.  And maybe validate and skip the insert if both values are empty.

ie
<cfloop ....>

    <cfset ListID =  trim( listgetat(record,1,'|') )>
    <cfset ApproxAcreage = trim( listgetat(record,2,'|') )> 

    <!--- if at least one of the values was provided, insert a new record ... --->
    <cfif len(listID) or len(ApproxAcreage)>
       <cfquery name="qryInsert" datasource="CGMLS">
            INSERT INTO listings (ListID, ApproxAcreage)
            VALUES 
            (
               <!--- assuming INT, but change the cfsqltype if needed ---->
               <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ListID#" null="#NOT isNumeric(ListID)#">
              , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ApproxAcreage#" null="#NOT isNumeric(ApproxAcreage)#">
            )
       </cfquery>
    </cfif>

</cfloop>

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 52

Expert Comment

by:_agx_
ID: 34885394
Oops... posts crossed

To get around this, convert the list to an array with the feature to include empty elements set to true

Yeah, but check the length first and skip the line if both elements are empty. No reason to insert a record if both values are empty

<!--- if one of the values is present --->
<cfif listLen(record, "|")>
       .... rest of logic to insert record
</cfif>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34897464
Bang-O, did it work ?
0
 

Author Comment

by:Bang-O-Matic
ID: 34897616
I have been so busy with other issues I havent had a chance to make these changes and do any testing.  Hopefully I will be able to get back on it tonight...thanks for your help! I do appreciate it.
0
 

Author Comment

by:Bang-O-Matic
ID: 34913106
Ok I  updated all the code, attached. Maybe I missed something? but I'm still getting insert errors:

"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 82
Stacktrace The Error Occurred in
 C:\www\IDX\CGMLS\test.cfm: line 133

131: <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Garage#" null="#NOT isNumeric(Garage)#">,
132: <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#MasterBedroomOnMainFloor#">,
133: <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#NumDiningAreas#">,
134: <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SplitBedroomPlan#">,
135: <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#HalfBaths#" null="#NOT isNumeric(HalfBaths)#">,"

 


 
<cfquery name="qry1" datasource="CGMLS">
DELETE FROM listings
</cfquery>


<cffile action="read"
file="C:\www\idx\cgmls\ftp\txt\listings-residential.txt"
variable="txtFile">
			
	
<cfloop 
index="record"
list="#Replace(Replace(Replace(txtFile,'''','''''','all'),'|','','all'),chr(9),'| ','all')#"
delimiters="#chr(13)##chr(10)#">
		
<cfset rec = listToArray(record,"|",true)>


<cfset ListId = trim(rec[1])>
<cfset Class = trim(rec[2])>
<cfset Type = trim(rec[3])>
<cfset Area = trim(rec[4])>
<cfset Price = trim(rec[5])>
<cfset StreetNumber = trim(rec[6])>
<cfset StreetDirection = trim(rec[7])>
<cfset StreetName = trim(rec[8])>
<cfset City = trim(rec[10])>
<cfset State = trim(rec[11])>
<cfset Zipcode = trim(rec[12])>
<cfset Status = trim(rec[13])>
<cfset SaleOrRent = trim(rec[14])>
<cfset Level = trim(rec[15])>
<cfset NumLivingAreas = trim(rec[16])>
<cfset Bedrooms = trim(rec[17])>
<cfset FullBaths = trim(rec[18])>
<cfset Garage = trim(rec[19])>
<cfset MasterBedroomOnMainFloor = trim(rec[20])>
<cfset NumDiningAreas = trim(rec[21])>
<cfset SplitBedroomPlan = trim(rec[22])>
<cfset HalfBaths = trim(rec[23])>
<cfset Occupancy = trim(rec[24])>
<cfset AgentID = trim(rec[25])>
<cfset AgentName = trim(rec[26])>
<cfset AgentPhone = trim(rec[27])>
<cfset ListingOfficeID = trim(rec[28])>
<cfset ListingOfficeName = trim(rec[29])>
<cfset ListingOfficePhone = trim(rec[30])>
<cfset CoAgent2ID = trim(rec[31])>
<cfset CoAgent2Name = trim(rec[32])>
<cfset CoAgent2Phone = trim(rec[33])>
<cfset CoAgent2OfficeID = trim(rec[34])>
<cfset CoAgent2OfficeName = trim(rec[35])>
<cfset CoAgent2OfficePhone = trim(rec[36])>
<cfset Subdivision = trim(rec[58])>
<cfset County = trim(rec[59])>
<cfset ApproxAcreage = trim(rec[64])>
<cfset LotDim = trim(rec[65])>
<cfset SQFT = trim(rec[66])>
<cfset SQFTBelowGrade = trim(rec[67])>
<cfset TotalFinishedSQFT = trim(rec[68])>
<cfset BelowGradeUnfinishedSQFT = trim(rec[70])>
<cfset SQFTSource = trim(rec[71])>
<cfset LeasePurchase = trim(rec[73])>
<cfset ConstructionStatus = trim(rec[74])>
<cfset YearBuilt = trim(rec[75])>
<cfset Pool = trim(rec[76])>
<cfset ApproxAge = trim(rec[77])>
<cfset EstCompletionDate = trim(rec[78])>
<cfset ElementarySch = trim(rec[79])>
<cfset MiddleSch = trim(rec[80])>
<cfset HighSch = trim(rec[81])>
<cfset NumFireplaces = trim(rec[82])>
<cfset TotalNumRooms = trim(rec[83])>
<cfset SecondarySuite = trim(rec[84])>
<cfset Kitchen = trim(rec[85])>
<cfset BreakFastArea = trim(rec[86])>
<cfset DiningRoom = trim(rec[87])>
<cfset DiningRoomType = trim(rec[88])>
<cfset Foyer = trim(rec[89])>
<cfset LivingRoom = trim(rec[90])>
<cfset GreatRoom = trim(rec[91])>
<cfset Den = trim(rec[92])>
<cfset MasterBedRoom = trim(rec[93])>
<cfset Bedroom2 = trim(rec[94])>
<cfset Bedroom3 = trim(rec[95])>
<cfset Bedroom4 = trim(rec[96])>
<cfset Bedroom5 = trim(rec[97])>
<cfset LaundryRoom = trim(rec[98])>
<cfset OtherRoom1 = trim(rec[99])>
<cfset OtherRoom2 = trim(rec[100])>
<cfset Remarks = trim(rec[101])>
<cfset Amenities = trim(rec[129])>
<cfset VirtualTour = trim(rec[130])>
<cfset ListDate = trim(rec[131])>


    
<cfquery name="qryInsert" datasource="CGMLS">

INSERT INTO listings (ListID, Class, Type, Area, Price, StreetNumber, StreetDirection, StreetName, City, State, Zipcode, Status, SaleOrRent, Level, NumLivingAreas, Bedrooms, FullBaths, Garage, MasterBedroomOnMainFloor, NumDiningAreas, SplitBedroomPlan, HalfBaths, Occupancy, AgentID, AgentName, AgentPhone, ListingOfficeID, ListingOfficeName, ListingOfficePhone, CoAgent2ID, CoAgent2Name, CoAgent2Phone, CoAgentOfficeID, CoAgent2OfficeName, CoAgent2OfficePhone, Subdivision, County, ApproxAcreage, LotDim, SQFT, SQFTBelowGrade, TotalFinishedSQFT, BelowGradeUnfinishedSQFT, SQFTSource, LeasePurchase, ConstructionStatus, YearBuilt, Pool, ApproxAge, EstCompletionDate, ElementarySch, MiddleSch, HighSch, NumFireplaces, TotalNumRooms, SecondarySuite, Kitchen, BreakFastArea, DiningRoom, DiningRoomType, Foyer, LivingRoom, GreatRoom, Den, MasterBedRoom, Bedroom2,Bedroom3, Bedroom4, Bedroom5, LaundryRoom, OtherRoom1, OtherRoom2, Remarks, Amenities, VirtualTour, ListDate)

VALUES (

<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ListID#" null="#NOT isNumeric(ListID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Class#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Type#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Area#">,
<cfqueryparam cfsqltype="CF_SQL_DECIMAL" value="#Price#" null="#NOT isNumeric(Price)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#StreetNumber#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#StreetDirection#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#StreetName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#City#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#State#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Zipcode#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Status#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SaleOrRent#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Level#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#NumLivingAreas#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Bedrooms#" null="#NOT isNumeric(Bedrooms)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#FullBaths#" null="#NOT isNumeric(FullBaths)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Garage#" null="#NOT isNumeric(Garage)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#MasterBedroomOnMainFloor#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#NumDiningAreas#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SplitBedroomPlan#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#HalfBaths#" null="#NOT isNumeric(HalfBaths)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Occupancy#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#AgentID#" null="#NOT isNumeric(AgentID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#AgentName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#AgentPhone#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ListingOfficeID#" null="#NOT isNumeric(ListingOfficeID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListingOfficeName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListingOfficePhone#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CoAgent2ID#" null="#NOT isNumeric(CoAgent2ID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoAgent2Name#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoAgent2Phone#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CoAgent2OfficeID#" null="#NOT isNumeric(CoAgent2OfficeID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoAgent2OfficeName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoAgent2OfficePhone#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Subdivision#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#County#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ApproxAcreage#" null="#NOT isNumeric(ApproxAcreage)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LotDim#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SQFT#" null="#NOT isNumeric(SQFT)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SQFTBelowGrade#" null="#NOT isNumeric(SQFTBelowGrade)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#TotalFinishedSQFT#" null="#NOT isNumeric(TotalFinishedSQFT)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BelowGradeUnfinishedSQFT#" null="#NOT isNumeric(BelowGradeUnfinishedSQFT)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SQFTSource#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LeasePurchase#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ConstructionStatus#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#YearBuilt#" null="#NOT isNumeric(YearBuilt)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Pool#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ApproxAge#" null="#NOT isNumeric(ApproxAge)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#EstCompletionDate#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ElementarySch#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#MiddleSch#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#HighSch#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#NumFireplaces#" null="#NOT isNumeric(NumFireplaces)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#TotalNumRooms#" null="#NOT isNumeric(TotalNumRooms)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SecondarySuite#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Kitchen#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#BreakFastArea#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DiningRoom#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DiningRoomType#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Foyer#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LivingRoom#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#GreatRoom#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Den#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#MasterBedRoom#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Bedroom2#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Bedroom3#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Bedroom4#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Bedroom5#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LaundryRoom#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#OtherRoom1#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#OtherRoom2#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#Remarks#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Amenities#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#VirtualTour#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListDate#">,

)

</cfquery> 

</cfloop>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34913317
your very last line should not have a comma before the )



<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListDate#">,   <=== remove comma
)


0
 

Author Comment

by:Bang-O-Matic
ID: 34913390
gdemaria, that did it! thanks again for your help, and thanks agx also.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
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: …

743 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

13 Experts available now in Live!

Get 1:1 Help Now