• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

MYSQL error  RSS feed

 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Can someone please help me out what causes this below error?

ERROR 1064: 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 ''fname' varchar(25) default NULL,
'lname' varchar(25) default NULL,
'cname' varc' at line 2
SQL Statement:
CREATE TABLE `jj`.`jjform` (
'fname' varchar(25) default NULL,
'lname' varchar(25) default NULL,
'cname' varchar(10) default NULL,
'addno' int(10) default NULL,
'dir' varchar(25) default NULL,
'street' varchar(50) default NULL,
'type' varchar(10) default NULL,
'aptno' int(15) default NULL,
'city' varchar(15) default NULL,
'state' varchar(2) default NULL,
'zip' varchar(5) default NULL,
'hphone' int(10) default NULL,
'wphone' int(10) default NULL,
'cphone' int(10) default NULL,
'email' varchar(25) default NULL,
'immanual' varchar(10) default NULL,
'residence' varchar(10) default NULL,
'recruitment' varchar(25) default NULL,
'recruitment1' varchar(25) default NULL,
'ads' varchar(3) default NULL,
'pinetree' varchar(8) default NULL,
'homecare' varchar(8) default NULL,
'none1' varchar(4) default NULL,
'month1' varchar(15) default NULL,
'day1' varchar(2) default NULL,
'year' varchar(4) default NULL,
'male' varchar(4) default NULL,
'female' varchar(6) default NULL,
'cob' varchar(20) default NULL,
'cob1' varchar(20) default NULL,
'ability' varchar(10) default NULL,
'ability1' varchar(10) default NULL,
'preference' varchar(10) default NULL,
)


 
Bartender
Posts: 2038
273
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The column names in your DDL are enclosed in single-quotes: ' -- you probably want to use back-ticks: `
 
Marshal
Posts: 60060
188
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you using only 25 letters for an email address?
Why are you using INTs for phone numbers and house numbers; they are not INTs.
 
Vani Jay
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ron and Campbell,

I changed according your suggestions but still get the same error but near aptno : Please see the code below: Can I use Number data type for phone and houseno.?

ERROR 1064: 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 'number(15) default NULL,
`city` varchar(15) default NULL,
`state` varchar(2) def' at line 9
SQL Statement:
CREATE TABLE `jj`.`jjform` (
`fname` varchar(25) default NULL,
`lname` varchar(25) default NULL,
`cname` varchar(10) default NULL,
`addno` int(10) default NULL,
`dir` varchar(25) default NULL,
`street` varchar(50) default NULL,
`type` varchar(10) default NULL,
`aptno` number(15) default NULL,
`city` varchar(15) default NULL,
`state` varchar(2) default NULL,
`zip` varchar(5) default NULL,
`hphone`number(10) default NULL,
`wphone` number(10) default NULL,
`cphone` number(10) default NULL,
`email` varchar(50) default NULL,
`immanual` varchar(10) default NULL,
`residence` varchar(10) default NULL,
`residence1` varchar(10) default NULL,
`recruitment` varchar(25) default NULL,
`recruitment1` varchar(25) default NULL,
`ads` varchar(3) default NULL,
`pinetree` varchar(8) default NULL,
`homecare` varchar(8) default NULL,
`none1` varchar(4) default NULL,
`month1` varchar(15) default NULL,
`day1` varchar(2) default NULL,
`year1` varchar(4) default NULL,
`male` varchar(4) default NULL,
`female` varchar(6) default NULL,
`cob` varchar(20) default NULL,
`cob1` varchar(20) default NULL,
`ability` varchar(10) default NULL,
`ability1` varchar(10) default NULL,
`preference` varchar(10) default NULL,
)


 
Ron McLeod
Bartender
Posts: 2038
273
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Vani Jay wrote:ERROR 1064: 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 'number(15) default NULL...


number is not a valid MySQL data  type - refer to MySQL Data Types.
 
Ron McLeod
Bartender
Posts: 2038
273
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Vani Jay wrote:Can I use Number data type for phone and houseno.?


In North America, house numbers and apartment number can contain letters as well as digits.

For example:
    3260B - the basement of house address 3260
    529A, 529B - the the two sides of a duplex house
    5D - apartment D on the 5th floor of an apartment block
 
Vani Jay
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, thanks Ron. Its working now.  There is an online MYSQL syntax error website which I used that to0 find my other errors .  Appreciate your help.
 
Campbell Ritchie
Marshal
Posts: 60060
188
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Phone numbers can start with a 0, which is actually a significant digit.
You can't do arithmetic with house numbers; sometimes the distance from 11 to 15 is the same as from 12 to 14, and the distance from 14 to 15 can be different still.
 
Vani Jay
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a silly question now. I created this table in workbench. Now where will I see them stored? I worked in 2007 few months in MySQL and thus I am rusty in it. Please let me know. Thanks,
 
Vani Jay
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Phone numbers can start with a 0, which is actually a significant digit.
You can't do arithmetic with house numbers; sometimes the distance from 11 to 15 is the same as from 12 to 14, and the distance from 14 to 15 can be different still.


Thanks Campbell. Understand.
 
Bartender
Posts: 19719
92
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Phone numbers can start with a 0, which is actually a significant digit.
You can't do arithmetic with house numbers; sometimes the distance from 11 to 15 is the same as from 12 to 14, and the distance from 14 to 15 can be different still.



It should also be noted that Sherlock Holmes lived at 221B Baker Street. So it's not just North American addresses.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!