SQLServer 2000 Conversion

[Stormblade]

Hey all,

I have a web app that uses SQLServer 2000. I am switching to MySQL 4.

1.1. I have re-created all the tables but I'm running into 2 problems.


1. In SQLServer I can create a date field and as the default value I

can use a macro/function to have it set the default. I have not seen

an equivalent in MySQL. After doing some research I have seen only

two possible solutions:

a) Use timestamp. This is not suitable because it has a tendency to
auto update and it's more trouble than it's worth to use a field that
does this when you know it should never really change.

b) Use datetime and just insert the time in. This option will
require

me to mess in the web app and go through and change SQL which I

really would rather not have to do but will as a very last resort. I

prefer the database to handle this sort of thing.

So am I missing something? Or does MySQL simply not support using

macros or functions as default values like SQLServer does?

2. Second problem may not be MySQL. I use Naicat and I created a

varchar field with a length of 2. When I saved the table it was

converted to a char of length 2. This was not cool because the two

are not equivalent. I don't care how much faster char is. So my

question is, is there something in MySQL that doesn't allow varchars

of length < 4 or is this a Navcat thing?

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-

Stormblade (Shaolin Code Warrior)

Software Developer (15+ Years Programming exp.)

My System: http://www.anandtech.com/mysystemrig.html?rigid=1683

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

[Mike Chirico]

"Stormblade" <st********@bellsouth.net> wrote in message
news:kC*******************@fe19.usenetserver.com.. .

Hey all, [snip] two possible solutions:
a) Use timestamp. This is not suitable because it has a tendency to
auto update and it's more trouble than it's worth to use a field that
does this when you know it should never really change.

b) Use datetime and just insert the time in. This option will
require
me to mess in the web app and go through and change SQL which I

really would rather not have to do but will as a very last resort. I
prefer the database to handle this sort of thing.

So am I missing something? Or does MySQL simply not support using
macros or functions as default values like SQLServer does?

Well, one big difference..the order of the timestamp filed in MySQL matters.
Yes,
so be careful when using alter table statements.

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time.
So
if the record is updated, or inserted, this time gets changed. If the
order is changed with an alter table statement, "timeEnter" is before
"timeUpdate", then, "timeEnter" would get updated. First timestamp
column updates automatically.

Note, in the table above timeEnter will only get updated if passed a
null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

May want to reference the following (TIP 3:)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt
Regards,

Mike Chirico

[Mike Chirico]

"Stormblade" <st********@bellsouth.net> wrote in message
news:kC*******************@fe19.usenetserver.com.. .

Hey all, [snip] two possible solutions:
a) Use timestamp. This is not suitable because it has a tendency to
auto update and it's more trouble than it's worth to use a field that
does this when you know it should never really change.

b) Use datetime and just insert the time in. This option will
require
me to mess in the web app and go through and change SQL which I

really would rather not have to do but will as a very last resort. I
prefer the database to handle this sort of thing.

So am I missing something? Or does MySQL simply not support using
macros or functions as default values like SQLServer does?

Well, one big difference..the order of the timestamp filed in MySQL matters.
Yes,
so be careful when using alter table statements.

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time.
So
if the record is updated, or inserted, this time gets changed. If the
order is changed with an alter table statement, "timeEnter" is before
"timeUpdate", then, "timeEnter" would get updated. First timestamp
column updates automatically.

Note, in the table above timeEnter will only get updated if passed a
null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

May want to reference the following (TIP 3:)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt
Regards,

Mike Chirico

[Mike Chirico]

"Stormblade" <st********@bellsouth.net> wrote in message
news:kC*******************@fe19.usenetserver.com.. .

Hey all, [snip] two possible solutions:
a) Use timestamp. This is not suitable because it has a tendency to
auto update and it's more trouble than it's worth to use a field that
does this when you know it should never really change.

b) Use datetime and just insert the time in. This option will
require
me to mess in the web app and go through and change SQL which I

really would rather not have to do but will as a very last resort. I
prefer the database to handle this sort of thing.

So am I missing something? Or does MySQL simply not support using
macros or functions as default values like SQLServer does?

Well, one big difference..the order of the timestamp filed in MySQL matters.
Yes,
so be careful when using alter table statements.

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time.
So
if the record is updated, or inserted, this time gets changed. If the
order is changed with an alter table statement, "timeEnter" is before
"timeUpdate", then, "timeEnter" would get updated. First timestamp
column updates automatically.

Note, in the table above timeEnter will only get updated if passed a
null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

May want to reference the following (TIP 3:)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt
Regards,

Mike Chirico