• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MYSQL error

 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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,
)


 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Today you are you, that is turer than true. There is no one alive who is youer than you! - Seuss. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic