Sql Query Error

[Noctorum]

Hi,

I'm setting up a table in a database for my site, and I'm getting a SQL error after entering this code into the query window:

 

>CREATE TABLE Sabre (

sabreName VARCHAR(20) NOT NULL,
realName VARCHAR(20),
age CHAR(2),
timezone VARCHAR(15),
country VARCHAR(20),
email VARCHAR(30),
AIM VARCHAR(25),
MSN VARCHAR(30),
ICQ VARCHAR(15),
sabreChar VARCHAR(12),
sabreCombat CHAR(3),
otherChar VARCHAR(12),
comment BLOB(500),
PRIMARY KEY (sabreName) 
)

 

I get the error:

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 '( 500  ) ,

PRIMARY  KEY ( sabreName )  )' at line 13

 

Any idea what I'm doing wrong?

 

~Noctorum

[taznumber1]

The Colum Name Comment that you are trying to use is a reserve word that mysql uses.

 

Here is a list of those words just for your info

 

http://docsrv.caldera.com:8457/cgi-bin/inf...erved%2520words

[Lianna]

Also, make sure that your statement is ended with a semi-colon ( ; ).

It isn't in the code pasted above, so I didn't know if that was just a copy paste error, or if it was really missing in your statement.

[Noctorum]

Ok, I redid it and tried both of these:

 

>CREATE TABLE Sabre (

sabreName VARCHAR(20) NOT NULL,
realName VARCHAR(20),
age CHAR(2),
timezone VARCHAR(15),
country VARCHAR(20),
email VARCHAR(30),
AIM VARCHAR(25),
MSN VARCHAR(30),
ICQ VARCHAR(15),
sabreChar VARCHAR(12),
sabreCombat CHAR(3),
otherChar VARCHAR(12),
extra BLOB(500),
PRIMARY KEY (sabreName);
)

 

And

 

>CREATE TABLE Sabre (

sabreName VARCHAR(20) NOT NULL,
realName VARCHAR(20),
age CHAR(2),
timezone VARCHAR(15),
country VARCHAR(20),
email VARCHAR(30),
AIM VARCHAR(25),
MSN VARCHAR(30),
ICQ VARCHAR(15),
sabreChar VARCHAR(12),
sabreCombat CHAR(3),
otherChar VARCHAR(12),
extra BLOB(500),
PRIMARY KEY (sabreName)
);

 

Neither of those worked. It won't work putting the table in which MySQL-Front, either.

 

 

I'm not quite sure what could be going wrong, it's giving me the same error.

[Lianna]

>CREATE TABLE Sabre
(sabreName VARCHAR(20) PRIMARY KEY,
realName VARCHAR(20),
age CHAR(2),
timezone VARCHAR(15),
country VARCHAR(20),
email VARCHAR(30),
AIM VARCHAR(25),
MSN VARCHAR(30),
ICQ VARCHAR(15),
sabreChar VARCHAR(12),
sabreCombat CHAR(3),
otherChar VARCHAR(12),
extra BLOB(500),

);

 

Try that instead. I think having two distinct lines for sabreName may have been fouling it up. A primary key cannot be null anyway, so the two instructions were redundant.

[Noctorum]

Using that code, I get

 

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 '(500), )' at line 1

 

It seems to have an issue with

>extra BLOB(500),

 

I tried it with text also, no success. If I remove it, I don't get an error. I can do without it for the time being, but I'm still curious as to what was wrong with it :/

[Lianna]

Hang tight and I'll manually create the table and export the SQL results.

 

Which begs the question, why not just build the table through the nice pretty phpMyAdmin interface in the first place?

[Noctorum]

Well the code was the backup, it didn't work through MySQL-Front, so I used phpMyAdmin, that didn't work, so I tried the SQL code. Thanks for the help though

[Lianna]

Blob is binary so this works just dandy (remove the "(500)" from the 'extra' field):

 

>CREATE TABLE Sabre(
sabreName VARCHAR( 20 ) PRIMARY KEY ,
realName VARCHAR( 20 ) ,
age CHAR( 2 ) ,
timezone VARCHAR( 15 ) ,
country VARCHAR( 20 ) ,
email VARCHAR( 30 ) ,
AIM VARCHAR( 25 ) ,
MSN VARCHAR( 30 ) ,
ICQ VARCHAR( 15 ) ,
sabreChar VARCHAR( 12 ) ,
sabreCombat CHAR( 3 ) ,
otherChar VARCHAR( 12 ) ,
extra BLOB
);

[Noctorum]

Much thanks

[Lianna]

No problem. Sorry for the delay in testing that for you. Been pretty busy today.