Link to home
Start Free TrialLog in
Avatar of Dusty
DustyFlag for United States of America

asked on

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
SOLUTION
Avatar of Matthew Kelly
Matthew Kelly
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gdemaria
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)#">

Avatar of Dusty

ASKER

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

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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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>
Bang-O, did it work ?
Avatar of Dusty

ASKER

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.
Avatar of Dusty

ASKER

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

your very last line should not have a comma before the )



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


Avatar of Dusty

ASKER

gdemaria, that did it! thanks again for your help, and thanks agx also.