dbTalk Databases Forums  

SQL Syntax error?

Go Back dbTalk Databases Forums Databases comp.databases.mysql SQL Syntax error?

comp.databases.mysql comp.databases.mysql


Discuss SQL Syntax error? in the comp.databases.mysql forum.



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

Default SQL Syntax error? - 11-10-2011 , 01:55 PM





I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
Can you help?


mysql> describe Schedule;
+-----------------+------------------+------+-----+---------
+----------------+
Quote:
Field | Type | Null | Key | Default |
Extra |
+-----------------+------------------+------+-----+---------
+----------------+
Quote:
MeetingID | int(10) unsigned | NO | PRI | NULL |
auto_increment |
Category | varchar(80) | NO | PRI | NULL
|
Location | varchar(80) | NO | | NULL
|
MeetingDT | date | NO | | NULL
|
MeetingTM | time | NO | | NULL
|
DurationMinutes | int(11) | YES | | NULL
|
Topic | varchar(80) | NO | PRI | NULL
|
Speaker | varchar(32) | YES | | NULL
|
EventLink | varchar(128) | YES | | NULL
|
Description | varchar(256) | YES | | NULL
|
+-----------------+------------------+------+-----+---------
+----------------+
10 rows in set (0.01 sec)

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Reply With Quote
  #2  
Old   
Doug Miller
 
Posts: n/a

Default Re: SQL Syntax error? - 11-10-2011 , 02:11 PM





On 11/10/2011 2:55 PM, SpreadTooThin wrote:
Quote:
I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'
Ummmm.... look at the quotes around that string in your SQL command....
>

Reply With Quote
  #3  
Old   
SpreadTooThin
 
Posts: n/a

Default Re: SQL Syntax error? - 11-10-2011 , 04:11 PM


On Nov 10, 1:11*pm, Doug Miller <doug_at_milmacdot... (AT) example (DOT) com>
wrote:
Quote:
On 11/10/2011 2:55 PM, SpreadTooThin wrote:

I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> *INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Ummmm.... look at the quotes around that string in your SQL command....
It's a date. Should it have been "2011-12-01"?

Reply With Quote
  #4  
Old   
Jerry Stuckle
 
Posts: n/a

Default Re: SQL Syntax error? - 11-10-2011 , 06:52 PM


On 11/10/2011 5:11 PM, SpreadTooThin wrote:
Quote:
On Nov 10, 1:11 pm, Doug Miller<doug_at_milmacdot... (AT) example (DOT) com
wrote:
On 11/10/2011 2:55 PM, SpreadTooThin wrote:

I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Ummmm.... look at the quotes around that string in your SQL command....



It's a date. Should it have been "2011-12-01"?
You should be using single quotes (') around non-numeric values. Double
quotes are not on the SQL standard and only supported by MySQL. Do
yourself a favor and stick to SQL standards where possible.

And you had back-ticks (`) around the date, not single quotes.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Reply With Quote
  #5  
Old   
Robert Hairgrove
 
Posts: n/a

Default Re: SQL Syntax error? - 11-11-2011 , 04:29 AM


On 11/10/2011 08:55 PM, SpreadTooThin wrote:
Quote:
I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
Can you help?


mysql> describe Schedule;
+-----------------+------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------+------------------+------+-----+---------
+----------------+
| MeetingID | int(10) unsigned | NO | PRI | NULL |
auto_increment |
| Category | varchar(80) | NO | PRI | NULL
| |
| Location | varchar(80) | NO | | NULL
| |
| MeetingDT | date | NO | | NULL
| |
| MeetingTM | time | NO | | NULL
| |
| DurationMinutes | int(11) | YES | | NULL
| |
| Topic | varchar(80) | NO | PRI | NULL
| |
| Speaker | varchar(32) | YES | | NULL
| |
| EventLink | varchar(128) | YES | | NULL
| |
| Description | varchar(256) | YES | | NULL
| |
+-----------------+------------------+------+-----+---------
+----------------+
10 rows in set (0.01 sec)

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'
Hmmm ... does Mr. O'Brien have a single quote in his name? If so, you
need to escape it. Either double up the single quote or precede it with
a backslash.

Also, you should not use backticks to quote literal values. These are
used for quoting identifiers such as column or table names. Use regular
(single) quotes around the date value.

And the same goes for double quotes: use single quotes instead. Double
quotes are used to quote identifiers in ANSI-SQL mode.

Reply With Quote
  #6  
Old   
Robert Hairgrove
 
Posts: n/a

Default Re: SQL Syntax error? - 11-11-2011 , 04:33 AM


On 11/11/2011 11:29 AM, Robert Hairgrove wrote:
Quote:
And the same goes for double quotes: use single quotes instead. Double
quotes are used to quote identifiers in ANSI-SQL mode.
Of course, you CAN use double quotes if you aren't running in strict
ANSI mode, but IMHO it is bad practice.

Reply With Quote
  #7  
Old   
Doug Miller
 
Posts: n/a

Default Re: SQL Syntax error? - 11-11-2011 , 07:01 AM


On 11/10/2011 5:11 PM, SpreadTooThin wrote:
Quote:
On Nov 10, 1:11 pm, Doug Miller<doug_at_milmacdot... (AT) example (DOT) com
wrote:
On 11/10/2011 2:55 PM, SpreadTooThin wrote:

I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Ummmm.... look at the quotes around that string in your SQL command....



It's a date. Should it have been "2011-12-01"?
Doesn't matter whether it's a date, or anything else -- NEVER use
backticks around data values.

Yes, "2011-12-01" would work. Using single quotes for everything would
be better. But not backticks. Backticks are not quotes.

This is a backtick: ` (which you used)
This is a single quote: '

Reply With Quote
  #8  
Old   
SpreadTooThin
 
Posts: n/a

Default Re: SQL Syntax error? - 11-17-2011 , 02:17 PM


On Nov 11, 6:01*am, Doug Miller <doug_at_milmacdot... (AT) example (DOT) com>
wrote:
Quote:
On 11/10/2011 5:11 PM, SpreadTooThin wrote:









On Nov 10, 1:11 pm, Doug Miller<doug_at_milmacdot... (AT) example (DOT) com
wrote:
On 11/10/2011 2:55 PM, SpreadTooThin wrote:

I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> * *INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Ummmm.... look at the quotes around that string in your SQL command.....

It's a date. *Should it have been "2011-12-01"?

Doesn't matter whether it's a date, or anything else -- NEVER use
backticks around data values.

Yes, "2011-12-01" would work. Using single quotes for everything would
be better. But not backticks. Backticks are not quotes.

This is a backtick: ` (which you used)
This is a single quote: '
So if a value has a back tic ` in it does it need to be escaped?

Reply With Quote
  #9  
Old   
Jerry Stuckle
 
Posts: n/a

Default Re: SQL Syntax error? - 11-17-2011 , 02:18 PM


On 11/17/2011 3:17 PM, SpreadTooThin wrote:
Quote:
On Nov 11, 6:01 am, Doug Miller<doug_at_milmacdot... (AT) example (DOT) com
wrote:
On 11/10/2011 5:11 PM, SpreadTooThin wrote:









On Nov 10, 1:11 pm, Doug Miller<doug_at_milmacdot... (AT) example (DOT) com
wrote:
On 11/10/2011 2:55 PM, SpreadTooThin wrote:

I'm trying to add a row to my table but I'm getting an SQL sytax error
that I don't understand.
[...]

mysql> INSERT INTO Schedule
(Category,Location,MeetingDT,MeetingTM,DurationMin utes,Topic,Speaker,EventLink,Description)
VALUES ("Rounds","No Location Set",`2011-12-01`,"12:00",
60,"DICOM","Brian OBrien","http://nema.medical.org","A Discussion of
the DICOM standard.");
ERROR 1054 (42S22): Unknown column '2011-12-01' in 'field list'

Ummmm.... look at the quotes around that string in your SQL command....

It's a date. Should it have been "2011-12-01"?

Doesn't matter whether it's a date, or anything else -- NEVER use
backticks around data values.

Yes, "2011-12-01" would work. Using single quotes for everything would
be better. But not backticks. Backticks are not quotes.

This is a backtick: ` (which you used)
This is a single quote: '

So if a value has a back tic ` in it does it need to be escaped?
ANY string value should be escaped. See mysql_real_escape_string().
Alternatively, us bound values.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

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