List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:June 9 2005 11:56am
Subject:RE: Seriously.. When are we going to get subqueries?!
View as plain text  
[snip]
I think MySQL has a little ways to go yet before I would subjectively
call
it best.

I posted twice to the list with questions about porting my application
that
runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access,
and
DB2) to MySQL. No one on the mysql list, or the internals list responded
to
my pretty basic issues:

1)	Why can't I declare a datetime field with DEFAULT NOW()
2)	Since the SQL standard states that identifiers are not case
sensitive, how can I use the DB without case sensitivity, when I don't
have
authority to change the system wide lowercase setting? I wouldn't have
authority to change the setting in a hosted environment.

I have to say, MySQL still looks like a tinker-toy to me.
[/snip]

Hi George,

Allow me to reiterate that I was trying to inject some humor into what
was fast becoming a rude situation. Having said that....

The list(s) where you post is not comprised of MySQL employees, it is
made up of other users/volunteers who seek help and/or can be of help to
others using the product. The list is often graced by several authors
and people who are intimately invoved with MySQL development and we
appreciate what little time they do have to offer for free. Sometimes
these folks do not have the answers you are seeking. Let me see if I can
help some with your issues.

1. I am supposing that with DEFAULT NOW() you are wanting the datetime
field to be populated with the current datetime when the tuple is
populated. (This is what I read from your question.) From
http://dev.mysql.com/doc/mysql/en/create-table.html "For date and time
types other than TIMESTAMP, the default is the appropriate ``zero''
value for the type. For the first TIMESTAMP column in a table, the
default value is the current date and time. See Section 11.3, "Date and
Time Types"." Declaring the column type to be TIMESTAMP may be all that
you need to do. I'll test...

CREATE TABLE `tblTimeDate` (
  `id` int(11) NOT NULL auto_increment,
  `theDate` date NOT NULL default '0000-00-00',
  `theTime` time NOT NULL default '00:00:00',
  `theDateStamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `theTimeStamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM

After doing a couple of inserts...

INSERT INTO tblTimeDate(theDate) VALUES ('2005-06-09');

The table returns....

+----+------------+----------+---------------------+----------------+
| id | theDate    | theTime  | theDateStamp        | theTimeStamp   |
+----+------------+----------+---------------------+----------------+
|  1 | 2005-06-09 | 00:00:00 | 0000-00-00 00:00:00 | 20050609063428 |
|  2 | 2005-06-09 | 00:00:00 | 0000-00-00 00:00:00 | 20050609063438 |
+----+------------+----------+---------------------+----------------+

You'll note that theTimeStamp column has data inserted into it without
my prodding. I got this information by searching the online manual, I
have never used this MySQL feature. I hope that this is what you were
looking for.

On case sensitivity (I found this in the online manual after two
clicks), http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html

Here is the first paragraph and subsequent note....

"In MySQL, databases correspond to directories within the data
directory. Tables within a database correspond to at least one file
within the database directory (and possibly more, depending on the
storage engine). Consequently, the case sensitivity of the underlying
operating system determines the case sensitivity of database and table
names. This means database and table names are not case sensitive in
Windows, and case sensitive in most varieties of Unix. One notable
exception is Mac OS X, which is Unix-based but uses a default filesystem
type (HFS+) that is not case sensitive. However, Mac OS X also supports
UFS volumes, which are case sensitive just as on any Unix. See Section
1.7.4, "MySQL Extensions to Standard SQL". 

Note: Although database and table names are not case sensitive on some
platforms, you should not refer to a given database or table using
different cases within the same query. The following query would not
work because it refers to a table both as my_table and as MY_TABLE: 

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;"

There is a lot more on the issue of identifier case sensitivity.

Since I have never been concerned with either of these issues I had to
rely on a couple of things to come up with these answers. One, my past
database experience with MySQL and other databases. And B, my ability to
type some simple search terms into my browser address bar. (For
instance, when searching for answers about identifiers I first typed
http://www.mysql.com/identifiers into the address bar. When the page
appeared one of the first links that I saw concerned case sensitivity.)

One final note concerning your comments. Tinker-Toys were among the most
popular toys of the mid to late 20th century and have made a resurgence
early in this century. Your comparison of MySQL to Tinker-Toys in this
light is spot on as MySQL has become one of the most popular databases
this century. It is still a growing, evolving, maturing product whose
feature list, it has been widely acknowledged, is not the same as some
other database products. But MySQL is catching up fast!

I sent this back to the list as well, in hopes that others may benefit
from the questions and answers. Please let me know if I can be of any
further assistance. 
Thread
Seriously.. When are we going to get subqueries?!Kevin Burton7 Jun
  • Re: Seriously.. When are we going to get subqueries?!Greg Whalin7 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton8 Jun
  • Re: Seriously.. When are we going to get subqueries?!DBA8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Dan Nelson8 Jun
      • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
        • Re: Seriously.. When are we going to get subqueries?!Kevin Burton9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
            • Re: Seriously.. When are we going to get subqueries?!Kevin Burton10 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Martijn Tonies8 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Josh Trutwin9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Keith Ivey9 Jun
      • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
      • Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Greg Whalin8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
RE: Seriously.. When are we going to get subqueries?!PMilanese8 Jun
Re: Seriously.. When are we going to get subqueries?!Bob_Savard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Boyd Lynn Gerber9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Josh Trutwin9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
      • Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
        • Re: Seriously.. When are we going to get subqueries?!Roger B.A. Klorese 9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
        • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Greg Whalin9 Jun
            • Re: Seriously.. When are we going to get subqueries?!daniel9 Jun
              • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
  • Re: Seriously.. When are we going to get subqueries?!David Griffiths9 Jun
    • RE: Seriously.. When are we going to get subqueries?!J.R. Bullington9 Jun
Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Bob)9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Gordon Bruce9 Jun
  • Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Martijn Tonies9 Jun
    • Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Jochem van Dieten9 Jun
  • Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Martijn Tonies9 Jun