Professional Web Applications Themes

Whoops -- character limit per line in Mysql or dodgy debugging on my part? - MySQL

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, <> ...

  1. #1

    Default Whoops -- character limit per line in Mysql or dodgy debugging on my part?

    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?

    speralta@progressivetrail.org Guest

  2. #2

    Default Re: Whoops -- character limit per line in Mysql or dodgy debugging on my part?

    >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
    Gordon Burditt Guest

  3. #3

    Default Re: Whoops -- character limit per line in Mysql or dodgy debuggingon my part?

    [email]speraltaprogressivetrail.org[/email] 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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: Whoops -- character limit per line in Mysql or dodgy debugging on my part?

    You've both been very helpful. Thanks.

    Jerry Stuckle wrote:
    > [email]speraltaprogressivetrail.org[/email] 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.
    > [email]jstucklexattglobal.net[/email]
    > ==================
    salvador Guest

Similar Threads

  1. Telnet character limit?
    By rfolden in forum PERL Modules
    Replies: 3
    Last Post: February 3rd, 12:08 AM
  2. 16 character password limit; need work-around
    By quasigenx in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 29th, 04:07 PM
  3. How to limit character output with CFML
    By KidKodiak in forum Macromedia ColdFusion
    Replies: 3
    Last Post: June 22nd, 06:36 AM
  4. Dynamically limit character output to fit space
    By JakeFlynn in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: May 11th, 05:53 PM
  5. TOC character limit, glitch?
    By Bill_Gibbs@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: July 17th, 11:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139