469,167 Members | 1,168 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

SQLServer 2000 Conversion

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

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

Jul 20 '05 #1
3 1557

"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
Jul 20 '05 #2

"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
Jul 20 '05 #3

"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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Stormblade | last post: by
3 posts views Thread by Devonish | last post: by
14 posts views Thread by Roy Gourgi | last post: by
8 posts views Thread by Darryl Kerkeslager | last post: by
2 posts views Thread by =?Utf-8?B?SmVmZnJleQ==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.