Gossamer Forum
Home : General : Databases and SQL :

New Table..

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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
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
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
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
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] New Table.. In reply to
It works fine if you put back CHAR to CHAR(50) Wink
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
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
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.
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
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.
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
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
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?
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
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.
Quote Reply
Re: [demon] New Table.. In reply to
I keep this bookmarked :)

http://www.mysql.com/.../Reserved_words.html
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?