I accidentally posted the following message to mailing.databases.mysql,
where I believe it is off-topic, so am reposting here with apologies to
the other group.


I can't seem to get a clean insert from a fairly large text file
database into mysql. I was wondering if maybe I'm hitting a limit on
character length per line on some insert
statements that I'm trying.

Here's the error messages:

execute failed: 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
'"COMP",5,"CRAWLSP","","PUBLIC","","LAP,STONE","", "FIREPL,GREAT- at
../res_sort.pl line 24, <> line 1.

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 '"COMP",8,"CRAWLSP","ACREADY","PUBLIC","EATAREA"," WOODCOM","","G
at ./res_sort.pl line 24, <> line 2.

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 '"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR
at ./res_sort.pl line 24, <> line 3.

execute failed: 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
'"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR at
../res_sort.pl line 24, <> line 4.

Here's a sample of the insert statements (note: this is all on one
line -- though it may not appear that way because google news
automatically chops lines of greater than a certain length)

INSERT INTO
residential(ml,Area,ListPrice,City,County,Neighbor hood,ZipCode,PropertyType,LotDescription,LotDimens ions,LotSize,ExteriorFeatures,NumberofLevels,Inter iorFeatures,KitchenFeatures,FireplaceDescription,E lementarySchool,MiddleSchool,HighSchool,Heat,YearB uilt,Style,NumberofBedrooms,NumberofCarGarage,Gara geDescription,NumberofFireplaces,TotalSQFT,Numbero fFullBathrooms,NumberofPartialBathrooms,TotalBathr ooms,PublicRemarks,ListingAgentPublicID,ListingOff iceID,ListingOfficePhone,ListingAgentName,ListingA gentPhone,ListingOfficeName,Acres,Roof,NumberofPho tos,BasementFoundation,Cooling,Water,DiningRoom,Ex teriorDescription,FamilyRoom,LivingRoom,Accessibil ity,Virtual,Waterfront,WaterfrontDescription)
VALUES(6003771,"44",371100,"Vancouver","Clark","Sk ylar
Estates",98686,"RESID","LEVEL","","5K-6,999SF","PATIO,SPRNKLR",2,"GAR-OPN,HARDWOD,TILE-FL,LAM-FL,SOAKTUB","BI-MICO,DISHWAS,DISPOSL,GASAPPL,PANTRY,FS-RANG","GAS","PLEASANT
VALLEY","PLEASANT
VALLEY","PRAIRIE","FOR-AIR",2006,"CRAFTSM",5,2,"ATTACHD",1,2533,3,1,3.1," Elegance
marks this home w/atrium & mother-in-law suite/office w/separate
entry.Beautiful great rm concept complete w/feplce & bamboo
hardwds.Gourmet islnd kitchn w/granite cntrtp,stlesteel appl, eating
area & formal dining. 4bedrms 3 1/2 bth.Photos/virtual tour 4 flr plan
idea only!","BREDLIES","4PLA01","360-896-5086","Shastine
Bredlie","360-904-9907","Platinum Real
Estate",,"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FO RMAL","WOODCOM","","GREAT-R","","http://www.obeo.com/u.aspx?id=252119","","");

Here's the record layout:

Field Type Null Key Default Extra
id int(11) PRI NULL auto_increment
ml int(11) YES NULL
Area varchar(35) YES NULL
ListPrice float(8,2) YES NULL
City varchar(30) YES NULL
County varchar(20) YES NULL
Neighborhood varchar(20) YES NULL
ZipCode int(5) YES NULL
PropertyType varchar(40) YES NULL
LotDescription varchar(18) YES NULL
LotDimensions varchar(20) YES NULL
LotSize varchar(15) YES NULL
ExteriorFeatures varchar(100) YES NULL
NumberofLevels int(1) YES NULL
InteriorFeatures varchar(100) YES NULL
KitchenFeatures varchar(100) YES NULL
FireplaceDescription varchar(10) YES NULL
ElementarySchool varchar(20) YES NULL
MiddleSchool varchar(20) YES NULL
HighSchool varchar(20) YES NULL
Heat varchar(20) YES NULL
YearBuilt int(4) YES NULL
Style varchar(10) YES NULL
NumberofBedrooms int(2) YES NULL
NumberofCarGarage int(1) YES NULL
GarageDescription varchar(10) YES NULL
NumberofFireplaces int(1) YES NULL
TotalSQFT int(5) YES NULL
NumberofFullBathrooms int(1) YES NULL
NumberofPartialBathrooms int(1) YES NULL
TotalBathrooms float(2,1) YES NULL
PublicRemarks text YES NULL
ListingAgentPublicID varchar(10) YES NULL
ListingOfficeID varchar(10) YES NULL
ListingOfficePhone varchar(13) YES NULL
ListingAgentName varchar(25) YES NULL
ListingAgentPhone varchar(13) YES NULL
ListingOfficeName varchar(25) YES NULL
Acres float(4,2) YES NULL
Roof varchar(20) YES NULL
NumberofPhotos int(1) YES NULL
BasementFoundation varchar(20) YES NULL
Cooling varchar(20) YES NULL
Water varchar(10) YES NULL
Sewer varchar(10) YES NULL
DiningRoom varchar(50) YES NULL
ExteriorDescription varchar(100) YES NULL
FamilyRoom varchar(50) YES NULL
LivingRoom varchar(50) YES NULL
Accessibility varchar(10) YES NULL
VirtualTour varchar(50) YES NULL
Waterfront varchar(10) YES NULL
WaterfrontDescription varchar(20) YES NULL

Anyone have any suggestions?

>I can't seem to get a clean insert from a fairly large text file
>database into mysql. I was wondering if maybe I'm hitting a limit on
>character length per line on some insert
>statements that I'm trying.

mysqldump generates multi-record insert statements that are a little
over a megabyte per line. I have no trouble putting them back into
MySQL. Your article is barely over 6k, and it contains a bunch of
stuff other than the query. You should be nowhere close to a length
limit.

>Here's the error messages:
>
>execute failed: 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
>'"COMP",5,"CRAWLSP","","PUBLIC","","LAP,STONE","" ,"FIREPL,GREAT- at
>./res_sort.pl line 24, <> line 1.

A double comma (outside of quotes) in your insert statement is a
syntax error. Fix it.

Gordon L. Burditt

speralta@progressivetrail.org wrote:
> I accidentally posted the following message to mailing.databases.mysql,
> where I believe it is off-topic, so am reposting here with apologies to
> the other group.
>
>
> I can't seem to get a clean insert from a fairly large text file
> database into mysql. I was wondering if maybe I'm hitting a limit on
> character length per line on some insert
> statements that I'm trying.
>
> Here's the error messages:
>
> execute failed: 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
> '"COMP",5,"CRAWLSP","","PUBLIC","","LAP,STONE","", "FIREPL,GREAT- at
> ./res_sort.pl line 24, <> line 1.
>
> 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 '"COMP",8,"CRAWLSP","ACREADY","PUBLIC","EATAREA"," WOODCOM","","G
> at ./res_sort.pl line 24, <> line 2.
>
> 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 '"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR
> at ./res_sort.pl line 24, <> line 3.
>
> execute failed: 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
> '"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR at
> ./res_sort.pl line 24, <> line 4.
>
> Here's a sample of the insert statements (note: this is all on one
> line -- though it may not appear that way because google news
> automatically chops lines of greater than a certain length)
>
> INSERT INTO
> residential(ml,Area,ListPrice,City,County,Neighbor hood,ZipCode,PropertyType,LotDescription,LotDimens ions,LotSize,ExteriorFeatures,NumberofLevels,Inter iorFeatures,KitchenFeatures,FireplaceDescription,E lementarySchool,MiddleSchool,HighSchool,Heat,YearB uilt,Style,NumberofBedrooms,NumberofCarGarage,Gara geDescription,NumberofFireplaces,TotalSQFT,Numbero fFullBathrooms,NumberofPartialBathrooms,TotalBathr ooms,PublicRemarks,ListingAgentPublicID,ListingOff iceID,ListingOfficePhone,ListingAgentName,ListingA gentPhone,ListingOfficeName,Acres,Roof,NumberofPho tos,BasementFoundation,Cooling,Water,DiningRoom,Ex teriorDescription,FamilyRoom,LivingRoom,Accessibil ity,Virtual,Waterfront,WaterfrontDescription)
> VALUES(6003771,"44",371100,"Vancouver","Clark","Sk ylar
> Estates",98686,"RESID","LEVEL","","5K-6,999SF","PATIO,SPRNKLR",2,"GAR-OPN,HARDWOD,TILE-FL,LAM-FL,SOAKTUB","BI-MICO,DISHWAS,DISPOSL,GASAPPL,PANTRY,FS-RANG","GAS","PLEASANT
> VALLEY","PLEASANT
> VALLEY","PRAIRIE","FOR-AIR",2006,"CRAFTSM",5,2,"ATTACHD",1,2533,3,1,3.1," Elegance
> marks this home w/atrium & mother-in-law suite/office w/separate
> entry.Beautiful great rm concept complete w/feplce & bamboo
> hardwds.Gourmet islnd kitchn w/granite cntrtp,stlesteel appl, eating
> area & formal dining. 4bedrms 3 1/2 bth.Photos/virtual tour 4 flr plan
> idea only!","BREDLIES","4PLA01","360-896-5086","Shastine
> Bredlie","360-904-9907","Platinum Real
> Estate",,"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FO RMAL","WOODCOM","","GREAT-R","","http://www.obeo.com/u.aspx?id=252119","","");
^^ Bad!


> Anyone have any suggestions?

Don't know about the first message - that INSERT isn't listed here. But
the second message points right at the error.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

You've both been very helpful. Thanks.

Jerry Stuckle wrote:
> speralta@progressivetrail.org wrote:
> > I accidentally posted the following message to mailing.databases.mysql,
> > where I believe it is off-topic, so am reposting here with apologies to
> > the other group.
> >
> >
> > I can't seem to get a clean insert from a fairly large text file
> > database into mysql. I was wondering if maybe I'm hitting a limit on
> > character length per line on some insert
> > statements that I'm trying.
> >
> > Here's the error messages:
> >
> > execute failed: 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
> > '"COMP",5,"CRAWLSP","","PUBLIC","","LAP,STONE","", "FIREPL,GREAT- at
> > ./res_sort.pl line 24, <> line 1.
> >
> > 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 '"COMP",8,"CRAWLSP","ACREADY","PUBLIC","EATAREA"," WOODCOM","","G
> > at ./res_sort.pl line 24, <> line 2.
> >
> > 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 '"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR
> > at ./res_sort.pl line 24, <> line 3.
> >
> > execute failed: 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
> > '"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FORMAL","W OODCOM","","GR at
> > ./res_sort.pl line 24, <> line 4.
> >
> > Here's a sample of the insert statements (note: this is all on one
> > line -- though it may not appear that way because google news
> > automatically chops lines of greater than a certain length)
> >
> > INSERT INTO
> > residential(ml,Area,ListPrice,City,County,Neighbor hood,ZipCode,PropertyType,LotDescription,LotDimens ions,LotSize,ExteriorFeatures,NumberofLevels,Inter iorFeatures,KitchenFeatures,FireplaceDescription,E lementarySchool,MiddleSchool,HighSchool,Heat,YearB uilt,Style,NumberofBedrooms,NumberofCarGarage,Gara geDescription,NumberofFireplaces,TotalSQFT,Numbero fFullBathrooms,NumberofPartialBathrooms,TotalBathr ooms,PublicRemarks,ListingAgentPublicID,ListingOff iceID,ListingOfficePhone,ListingAgentName,ListingA gentPhone,ListingOfficeName,Acres,Roof,NumberofPho tos,BasementFoundation,Cooling,Water,DiningRoom,Ex teriorDescription,FamilyRoom,LivingRoom,Accessibil ity,Virtual,Waterfront,WaterfrontDescription)
> > VALUES(6003771,"44",371100,"Vancouver","Clark","Sk ylar
> > Estates",98686,"RESID","LEVEL","","5K-6,999SF","PATIO,SPRNKLR",2,"GAR-OPN,HARDWOD,TILE-FL,LAM-FL,SOAKTUB","BI-MICO,DISHWAS,DISPOSL,GASAPPL,PANTRY,FS-RANG","GAS","PLEASANT
> > VALLEY","PLEASANT
> > VALLEY","PRAIRIE","FOR-AIR",2006,"CRAFTSM",5,2,"ATTACHD",1,2533,3,1,3.1," Elegance
> > marks this home w/atrium & mother-in-law suite/office w/separate
> > entry.Beautiful great rm concept complete w/feplce & bamboo
> > hardwds.Gourmet islnd kitchn w/granite cntrtp,stlesteel appl, eating
> > area & formal dining. 4bedrms 3 1/2 bth.Photos/virtual tour 4 flr plan
> > idea only!","BREDLIES","4PLA01","360-896-5086","Shastine
> > Bredlie","360-904-9907","Platinum Real
> > Estate",,"COMP",3,"CRAWLSP","ACREADY","PUBLIC","FO RMAL","WOODCOM","","GREAT-R","","http://www.obeo.com/u.aspx?id=252119","","");
> ^^ Bad!
>
>
> > Anyone have any suggestions?
>
> Don't know about the first message - that INSERT isn't listed here. But
> the second message points right at the error.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================