Gossamer Forum
(Advanced Search)
for
Home : General : Databases and SQL :

New Table..

Oct 18, 2002, 2:25 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 18, 2002, 2:25 AM
Post #1 of 17
Views: 1539
Shortcut
Quote Reply
New Table..
Argh....I'm not sure if this is a problem with the distro MySQLMan, but this isn't the first time it has played up on me with creating tables. It produced this code;

CREATE TABLE lsql_Links(title CHAR(50) NOT NULL ,short TEXT(50) NOT NULL ,full TEXT(500) NOT NULL ,date INT NOT NULL ,nice_date CHAR NOT NULL )

Which was declined by MySQL....but why?

Any ideas? Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 18, 2002, 2:32 AM
Paul
Veteran (19537 posts)
Oct 18, 2002, 2:32 AM
Post #2 of 17
Views: 1530
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
All mysqlman does is execute your code Wink

Try reading the column types page and specifically look at TEXT....

http://www.mysql.com/...en/Column_types.html

http://www.mysql.com/doc/en/BLOB.html

In other words your syntax is wrong, which is probably what MySQL already told you Wink

Last edited by:

Paul: Oct 18, 2002, 2:33 AM
Oct 18, 2002, 5:40 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 18, 2002, 5:40 AM
Post #3 of 17
Views: 1525
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
No..this wasn't from the SQL monitor tool, but rather the 'add tables' forms...which should at lesat give me a SQL error (other than what it gave me already). I'll have look at it anyway...Wink

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 18, 2002, 6:13 AM
Paul
Veteran (19537 posts)
Oct 18, 2002, 6:13 AM
Post #4 of 17
Views: 1521
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
Same difference, all the create table page does is build the command based on what you enter.

On the page I linked to notice:

Quote:
M Indicates the maximum display size. The maximum legal display size is 255.

....and then:

Quote:
VARCHAR(M) A variable length string. The range of M is 0 to 255 characters

TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters.

Notice M isn't there for TEXT
Oct 18, 2002, 9:46 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 18, 2002, 9:46 AM
Post #5 of 17
Views: 1513
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
Hi...thanks for the pointers...but it still doesn't seem to be working Unsure

CREATE TABLE lsql_News (title CHAR NOT NULL ,short TEXT NOT NULL ,full TEXT NOT NULL ,date INT NOT NULL ,nice_date CHAR NOT NULL)

Any more ideas?

Cheers

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 18, 2002, 10:26 AM
Paul
Veteran (19537 posts)
Oct 18, 2002, 10:26 AM
Post #6 of 17
Views: 1507
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
It works fine if you put back CHAR to CHAR(50) Wink
Oct 18, 2002, 10:53 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 18, 2002, 10:53 AM
Post #7 of 17
Views: 1501
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
Does it?

Error: Query Error: Failed to execute query: 'CREATE TABLE lsql_News (title CHAR(50) NOT NULL ,short TEXT NOT NULL ,full TEXT NOT NULL ,date INT NOT NULL ,nice_date CHAR(50) NOT NULL) ' Reason: You have an error in your SQL syntax near 'full TEXT NOT NULL ,date INT NOT NULL ,nice_date CHAR(50) NOT NULL) ' at line 1

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 18, 2002, 11:22 AM
Paul
Veteran (19537 posts)
Oct 18, 2002, 11:22 AM
Post #8 of 17
Views: 1496
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
Quote:
Does it?

Yeah it does.

This works fine:

Code:
CREATE TABLE lsql_Links(
title CHAR(50) NOT NULL ,
short TEXT NOT NULL ,
full TEXT NOT NULL ,
date INT NOT NULL ,
nice_date CHAR NOT NULL
)

...as does:

Code:
CREATE TABLE lsql_Links(
title CHAR(50) NOT NULL ,
short TEXT NOT NULL ,
full TEXT NOT NULL ,
date INT NOT NULL ,
nice_date CHAR(50) NOT NULL
)

Last edited by:

Paul: Oct 18, 2002, 11:28 AM
Oct 18, 2002, 11:27 AM
Alex
Administrator (9386 posts)
Oct 18, 2002, 11:27 AM
Post #9 of 17
Views: 1490
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
Might want to watch for reserved words. Try not to call your columns the same name as column types (i.e. text and date). Mysql may allow it, but other databases will choke.

Cheers,

Alex
--
Gossamer Threads Inc.
Oct 19, 2002, 1:30 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 19, 2002, 1:30 AM
Post #10 of 17
Views: 1472
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
Paul, I just tried entering both of those commands, and they didn't work! Could it be that the user being used at the moment doesn't have the permission to create new tables? I'm just guessing, because the code in my Plugin worked fine on all the other servers it was tested on, except this one Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 19, 2002, 3:00 AM
Paul
Veteran (19537 posts)
Oct 19, 2002, 3:00 AM
Post #11 of 17
Views: 1467
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
I have no idea without you stating the error.

Either way it is definitely something wrong at your end because both those CREATE TABLE queries work.
Oct 19, 2002, 9:46 AM
Andy
Veteran / Moderator (17297 posts)
Andy's avatar
Oct 19, 2002, 9:46 AM
Post #12 of 17
Views: 1465
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
THats the thing though...there is no real error. It just says the query could not be executed! Unsure

Very odd indeed

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Oct 21, 2002, 12:53 PM
demon
User (95 posts)
Oct 21, 2002, 12:53 PM
Post #13 of 17
Views: 1445
Shortcut
Quote Reply
Re: [Andy] New Table.. In reply to
It is possible to create all fields except the field called: 'full'

I have been able to create the other 4 fields, but when I do try and create 'fulll' I get the following error message:

MySQL said: You have an error in your SQL syntax near 'full TEXT NOT NULL AFTER short' at line 1.

Query: ALTER TABLE lsql_News ADD full TEXT NOT NULL AFTER short



Any Idea?
Oct 21, 2002, 12:58 PM
Paul
Veteran (19537 posts)
Oct 21, 2002, 12:58 PM
Post #14 of 17
Views: 1442
Shortcut
Quote Reply
Re: [demon] New Table.. In reply to
Strange...what version of mysql are you using?

I works for me without a hitch on windows running mysql 3.23.49-nt
Oct 21, 2002, 1:28 PM
Alex
Administrator (9386 posts)
Oct 21, 2002, 1:28 PM
Post #15 of 17
Views: 1439
Shortcut
Quote Reply
Re: [demon] New Table.. In reply to
Probably was a reserved word in older versions of mysql. Probably need to rename your column.

Cheers,

Alex
--
Gossamer Threads Inc.
Oct 21, 2002, 1:48 PM
Paul
Veteran (19537 posts)
Oct 21, 2002, 1:48 PM
Post #16 of 17
Views: 1435
Shortcut
Quote Reply
Re: [demon] New Table.. In reply to
I keep this bookmarked :)

http://www.mysql.com/.../Reserved_words.html
Oct 21, 2002, 2:30 PM
demon
User (95 posts)
Oct 21, 2002, 2:30 PM
Post #17 of 17
Views: 1430
Shortcut
Quote Reply
Re: [Paul] New Table.. In reply to
Thanks for you reply,

I use mySQL version 3.22.32

So slightly older than the one you have.

By the way would that make any differance in any other way as well? Or should I say having a later version of mySQL will that give any improvments to performance?