Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1657
  • Last Modified:

MySQL 5.1 - Incorrect integer value: '' for column

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
Bang-O-Matic
Asked:
Bang-O-Matic
  • 4
  • 4
  • 3
  • +1
3 Solutions
 
Matthew KellyCommented:
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
 
gdemariaCommented:
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
 
Bang-O-MaticAuthor Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
_agx_Commented:
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
 
gdemariaCommented:

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
 
_agx_Commented:
<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
 
_agx_Commented:
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
 
gdemariaCommented:
Bang-O, did it work ?
0
 
Bang-O-MaticAuthor Commented:
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
 
Bang-O-MaticAuthor Commented:
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
 
gdemariaCommented:
your very last line should not have a comma before the )



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


0
 
Bang-O-MaticAuthor Commented:
gdemaria, that did it! thanks again for your help, and thanks agx also.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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