LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 06-09-2007, 12:54 PM   #1
taylor_venable
Member
 
Registered: Jun 2005
Location: Indiana, USA
Distribution: OpenBSD, Ubuntu
Posts: 892

Rep: Reputation: 41
MySQL Syntax Error?


I'm trying to create a table in MySQL 5.0 which will hold data on words of a certain part of speech in the English language. So, for example, according to the controlled English system I'm working with, adjectives have properties like the positive, comparative, and superlative forms (e.g. "good", "better", "best"). You can also specify aliases for certain properties, which are alternatives that have the same meaning. The aliases are lists, so there could be some as-yet-unknown number of them. As such, I tried to create a table for adjectives (where each row is a word entry) using the following SQL:
Code:
CREATE TABLE `capture`.`adjective` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `positive` VARCHAR NOT NULL,
  `comparative` VARCHAR NOT NULL,
  `superlative` VARCHAR NOT NULL,
  `positive_aliases` SET,
  `comparative_aliases` SET,
  `superlative_aliases` SET,
  `complementing_preposition` VARCHAR NOT NULL,
  `comment` VARCHAR,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
CHARACTER SET utf8 COLLATE utf8_general_ci;
This was generated by using one of the MySQL GUI tools. But I get a syntax error (#1064) when I try to execute it:
Code:
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 'NOT NULL,
  `comparative` VARCHAR NOT NULL,
  `superlative` VARCHAR NOT NULL,
' at line 3
I've checked the generated SQL against what I understand from "SQL in a Nutshell" and it seems to check out OK by my human eye. But the server thinks there's something wrong, obviously. The full ID of the server's version is "MySQL 5.0.38-Ubuntu_0ubuntu1-log" if that has any meaning. Thanks for any ideas!
 
Old 06-10-2007, 04:28 AM   #2
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,159

Rep: Reputation: 258Reputation: 258Reputation: 258
Hi

Varchar needs maximum length, so you need to change "varchar" into for example "varchar (255)". Also, "set" by itself is not a type. You need to specify some values it can have. And I think you might want to not use a set for this:

positive_aliases SET ('good','better','best')
means that alias can have a combination of any of those values.

positive_aliases ENUM ('good','better','best')
means it will have one of those values.

Or maybe just use varchar(255) for that one too, if you don't know all the alternatives yet.

See:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, what GUI tool did you use? I use phpmyadmin. With it you can do a lot of stuff like creating tables, and you can look at the SQL if you want to, but you really don't need to. The only thing better than phpmyadmin I found is "navicat" but it's commercial software. They have an evaluation for it if you want to try it, just google for it.

Hope this helps.
 
Old 06-10-2007, 09:39 AM   #3
taylor_venable
Member
 
Registered: Jun 2005
Location: Indiana, USA
Distribution: OpenBSD, Ubuntu
Posts: 892

Original Poster
Rep: Reputation: 41
Quote:
Originally Posted by Guttorm
Varchar needs maximum length, so you need to change "varchar" into for example "varchar (255)". Also, "set" by itself is not a type. You need to specify some values it can have. And I think you might want to not use a set for this:

positive_aliases SET ('good','better','best')
means that alias can have a combination of any of those values.

positive_aliases ENUM ('good','better','best')
means it will have one of those values.

Or maybe just use varchar(255) for that one too, if you don't know all the alternatives yet.
Thanks, I didn't know that you had to specify those things. For the non-aliases, it's perfectly alright. But that is unfortunate about the set thing, because it's impossible to know what values the user will provide for aliases. I may end up and use the TEXT type instead, and delimit the aliases with newlines. (The actual usable form of the information is a Prolog database, but we want to store it on a server somewhere with a web interface, so something like MySQL seemed like a logical choice. The two systems are not directly analogous. )

Quote:
Originally Posted by Guttorm
Also, what GUI tool did you use? I use phpmyadmin. With it you can do a lot of stuff like creating tables, and you can look at the SQL if you want to, but you really don't need to. The only thing better than phpmyadmin I found is "navicat" but it's commercial software. They have an evaluation for it if you want to try it, just google for it.
I use phpmyadmin sometimes, but most of the time I use MySQL Administrator and MySQL Query Browser (both available as free software from mysql.org). They're both pretty nice tools, and in my experience offer a bit more functionality than phpmyadmin.

Quote:
Originally Posted by Guttorm
Hope this helps.
It sure does. Thanks!
 
  


Reply

Tags
sql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 09:44 AM
MySQL syntax problem Hydrap Linux - Software 1 08-11-2005 11:49 PM
Can't get pass this mysql syntax error Neruocomp Programming 5 07-24-2005 06:25 PM
C++ syntax error before :: token HELP, i cant find the syntax error :( qwijibow Programming 2 12-14-2004 07:09 PM
mysql syntax question pk21 Linux - General 1 05-18-2004 07:30 AM


All times are GMT -5. The time now is 10:12 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration