dbTalk Databases Forums  

INSERT puzzle

Go Back dbTalk Databases Forums Databases comp.databases.mysql INSERT puzzle

comp.databases.mysql comp.databases.mysql


Discuss INSERT puzzle in the comp.databases.mysql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Steve Ackman
 
Posts: n/a

Default INSERT puzzle - 03-08-2010 , 05:03 AM





Server version: 5.1.41-3 (Debian)

This INSERT works fine until I add the last line.

mysql> INSERT INTO testing
-> SET server_id = 280578,
-> Newsgroups = 'alt.coffee',
-> Message_ID = '<8l0rk5584fqtdp2jk4v1e6k0ccq4v64iq4 (AT) 4ax (DOT) com>',
-> Author = 'Barry Jarrett <barry (AT) some-coffee (DOT) invalid>',
-> Subject = 'Re: coffee quiz',
-> Raw_Date = 'Wed, 13 Jan 2010 02:22:22 -0600',
-> Xref = 'xyz.dyndns.org alt.coffee:280578',
-> Content_Type = 'text/plain; charset=us-ascii',
-> Lines = 4;
(or '4' )

Then it fails with,

ERROR 1064 (42000): 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 'Lines = 4' at line 10

What is it about "Lines" that breaks things?

The DESCRIBE (so far),

+-------------------+-----------------------+------+-----+---------+-------+
Quote:
Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+-------+
server_id | mediumint(8) unsigned | NO | PRI | NULL | |
Message_ID | varchar(80) | NO | MUL | NULL | |
Author | varchar(64) | NO | | NULL | |
Subject | varchar(132) | NO | | NULL | |
Raw_Date | varchar(37) | NO | | 0 | |
User_Agent | varchar(132) | YES | | NULL | |
NNTP_Posting_Host | varchar(64) | YES | | NULL | |
X_Trace | varchar(64) | YES | | NULL | |
Content_Type | varchar(64) | YES | | NULL | |
Lines | smallint(6) unsigned | YES | | NULL | |
Bytes | mediumint(9) unsigned | YES | | NULL | |
Xref | varchar(132) | YES | UNI | NULL | |
Body | mediumtext | YES | | NULL | |
Head | mediumtext | YES | | NULL | |
References | mediumtext | YES | | NULL | |
Newsgroups | varchar(128) | NO | | NULL | |
+-------------------+-----------------------+------+-----+---------+-------+

While we're at it, what's the best way to convert a Date like
Wed, 13 Jan 2010 02:22:22 -0600 into standard MySQL format?

Thanks.
--
☯☯

Reply With Quote
  #2  
Old   
Erick T. Barkhuis
 
Posts: n/a

Default Re: INSERT puzzle - 03-08-2010 , 05:21 AM





Steve Ackman:


Quote:
mysql> INSERT INTO testing
-> SET server_id = 280578,
-> Newsgroups = 'alt.coffee',
-> Message_ID = '<8l0rk5584fqtdp2jk4v1e6k0ccq4v64iq4 (AT) 4ax (DOT) com>',
-> Author = 'Barry Jarrett <barry (AT) some-coffee (DOT) invalid>',
-> Subject = 'Re: coffee quiz',
-> Raw_Date = 'Wed, 13 Jan 2010 02:22:22 -0600',
-> Xref = 'xyz.dyndns.org alt.coffee:280578',
-> Content_Type = 'text/plain; charset=us-ascii',
-> Lines = 4;

What is it about "Lines" that breaks things?
It's a reserved word:
http://dev.mysql.com/doc/refman/5.1/...ved-words.html

--
Erick

Reply With Quote
  #3  
Old   
Erick T. Barkhuis
 
Posts: n/a

Default TimeZone in MySQL (was: INSERT puzzle) - 03-08-2010 , 05:26 AM


Steve Ackman:


Quote:
While we're at it, what's the best way to convert a Date like
Wed, 13 Jan 2010 02:22:22 -0600 into standard MySQL format?
The - not too friendly - thread that ends with this may help you
further:
http://forums.mysql.com/read.php?20,...968#msg-272968


--
Erick

Reply With Quote
  #4  
Old   
The Natural Philosopher
 
Posts: n/a

Default Re: INSERT puzzle - 03-08-2010 , 08:11 AM


Erick T. Barkhuis wrote:
Quote:
Steve Ackman:


mysql> INSERT INTO testing
-> SET server_id = 280578,
-> Newsgroups = 'alt.coffee',
-> Message_ID = '<8l0rk5584fqtdp2jk4v1e6k0ccq4v64iq4 (AT) 4ax (DOT) com>',
-> Author = 'Barry Jarrett <barry (AT) some-coffee (DOT) invalid>',
-> Subject = 'Re: coffee quiz',
-> Raw_Date = 'Wed, 13 Jan 2010 02:22:22 -0600',
-> Xref = 'xyz.dyndns.org alt.coffee:280578',
-> Content_Type = 'text/plain; charset=us-ascii',
-> Lines = 4;

What is it about "Lines" that breaks things?

It's a reserved word:
http://dev.mysql.com/doc/refman/5.1/...ved-words.html

....yeah as I discovered when I tried to use a table called 'order'



:-)

Reply With Quote
  #5  
Old   
Steve Ackman
 
Posts: n/a

Default Re: INSERT puzzle - 03-08-2010 , 01:04 PM


In <7vk4u1F6ifU1 (AT) mid (DOT) individual.net>, on 8 Mar 2010 11:21:37 GMT, Erick T.
Barkhuis, erick.use-net (AT) ardane (DOT) c.o.m wrote:
Quote:
Steve Ackman:

-> Lines = 4;

What is it about "Lines" that breaks things?

It's a reserved word:
http://dev.mysql.com/doc/refman/5.1/...ved-words.html
I just knew it was going to be something obvious.
I'm doing that through python's MySQLdb, and for some
reason thought to check python's reserved words, but
neglected the (MUCH LONGER!) MySQL list. It's sure
bookmarked now.

Thanks.

--
☯☯

Reply With Quote
  #6  
Old   
Erick T. Barkhuis
 
Posts: n/a

Default Re: INSERT puzzle - 03-08-2010 , 02:19 PM


Steve Ackman:

Quote:
In <7vk4u1F6ifU1 (AT) mid (DOT) individual.net>, on 8 Mar 2010 11:21:37 GMT,
Erick T. Barkhuis, erick.use-net (AT) ardane (DOT) c.o.m wrote:
Steve Ackman:


mysql> INSERT INTO testing
-> SET server_id = 280578,
-> Newsgroups = 'alt.coffee',
-> Message_ID =
'<8l0rk5584fqtdp2jk4v1e6k0ccq4v64iq4 (AT) 4ax (DOT) com>', -> Author =
'Barry Jarrett <barry (AT) some-coffee (DOT) invalid>', -> Subject =
'Re: coffee quiz', -> Raw_Date = 'Wed, 13 Jan 2010 02:22:22
-0600', -> Xref = 'xyz.dyndns.org alt.coffee:280578',
-> Content_Type = 'text/plain; charset=us-ascii',
-> Lines = 4;

What is it about "Lines" that breaks things?

It's a reserved word:
http://dev.mysql.com/doc/refman/5.1/...ved-words.html

Ok... I didn't think I was COMPLETELY crazy. Here's the table I
used probably two years ago that worked fine being populated with a
bash/awk/sed script. Very strange that Lines worked then.
Well, in MySQL, you can still use field names that are reserved words,
but then you have to use backticks.
So,
SELECT `lines` from someTable
would work.

I would rather use different names for fields and tables, though.


--
Erick

Reply With Quote
  #7  
Old   
Steve Ackman
 
Posts: n/a

Default Re: TimeZone in MySQL (was: INSERT puzzle) - 03-08-2010 , 02:19 PM


In <7vk56eF7vmU1 (AT) mid (DOT) individual.net>, on 8 Mar 2010 11:26:06 GMT, Erick T.
Barkhuis, erick.use-net (AT) ardane (DOT) c.o.m wrote:
Quote:
Steve Ackman:

While we're at it, what's the best way to convert a Date like
Wed, 13 Jan 2010 02:22:22 -0600 into standard MySQL format?

The - not too friendly - thread that ends with this may help you
further:
http://forums.mysql.com/read.php?20,...968#msg-272968
Thanks. Looks like it'll be easier to figure out how do it in Python
beforehand.

--
☯☯

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top