Re: SQL VS ACCESS
It goes like this:
Select id_seq.nextval AS id
From dual
Yvette Ingram
Brainbench Certified ColdFusion 4.5 Programmer
Email: ingramrecruiting@erols or
[EMAIL PROTECTED]
ICQ: 21200397
- Original Message -
From: Don Vawter [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, June 13, 2001 8:54 PM
Subject: Re: SQL VS ACCESS
Concerning Oracle (and I don't have a lot of experience) you can set up
autincrementing fields I don't remember the details because it has been
about a year but select from dual or something similiar will get the
next
value. You can also reset the starting pt etc.
- Original Message -
From: Mark Warrick [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, June 13, 2001 5:44 PM
Subject: RE: SQL VS ACCESS
Costas,
Your point is well taken, but I never meant to imply that I would allow
a
primary key field to be anything other than a number (INT).
The name of the primary key field doesn't matter, however I typically do
what you do and prefix (or suffix) the primary key field name with ID.
I setup one table in the database called KEYS. (Very simple: ID_KEY
(INT)
USED_DATETIME (date/time). Whenever I need a unique key, I call the
following template:
!-- begin act_global_get_key.cfm --
!--- this template generates a unique key which can be used in any
table
in
the database ---
cfsetting enablecfoutputonly=yes
cftransaction
cfquery name=max datasource=#session.dsn#
SELECT max (ID_KEY) as maxid
FROM KEYS
/cfquery
cfset ID_KEY = max.maxid+1
cfquery name=insert_key datasource=#session.dsn#
INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
#CreateODBCDateTime(Now())#)
/cfquery
/cftransaction
cfsetting enablecfoutputonly=no
!-- end act_global_get_key.cfm --
If I was using Oracle as the DB, I would call a stored procedure to have
the
DB create a unique key for me.
One of the benefits of this is that all numbers used for primary keys in
all
tables are unique. So you never have to worry about the scope
(persay -
that's not the right term for that) of the number because you know it
could
have only come from one of the tables in the system.
The issue about joins is true as well. I'll RARELY join text fields.
Your point about upsizing as an INT field and then changing it back to
an
INT PK SEED {n} field is also absolutely valid - if the database
supports
autonumbering. Last I checked, Oracle does not support autonumber
fields.
(I could be wrong about that.)
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
-Original Message-
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 2:24 PM
To: CF-Talk
Subject: RE: SQL VS ACCESS
Great, but what happens when you wish to change the name of a field in
a
lookup table?
Do you run update statements on EVERY foreign key field?
Wouldn't it be easier to just change a value in the database?
Further, requesting that user's supply indeces isn't the best way to
work;
user's have a tendancy to want to change the value of their indices
from
time to time. For example, If I have table a:
CustomerID int
AccountingID char
CompanyName char
And I want to change the accounting ID (for whatever reason), I'd be
okay.
However, if I were to omit the customerID field and stick with the
Accounting ID Field, the change would be acceptable.
Further, joins on integer fields work much faster than joins on
character
fields. The database has that much less to work with matching up.
You are right though, autonumber fields can be a real pain. What
you should
usually do in a migration from access to sql server is first go
into all of
your tables, turn off autonumber, and leave the field as an integer
field,
when you upsize, go back in and revert to autonumber.
-Original Message-
From: Mark Warrick [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 11:27 AM
To: CF-Talk
Subject: RE: SQL VS ACCESS
I've learned to avoid using autonumber fields whenever possible. It
makes
the job of moving databases between platforms simple. At worst, I
have
to
put the primary key constraints back on the index fields of each
table -
which is no big deal.
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http
RE: SQL VS ACCESS
I've learned to avoid using autonumber fields whenever possible. It makes the job of moving databases between platforms simple. At worst, I have to put the primary key constraints back on the index fields of each table - which is no big deal. ---mark Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 13, 2001 4:02 AM To: CF-Talk Subject: RE: SQL VS ACCESS On 6/12/01, Norman Elton penned: Make sure all your fields that should be indexed are indexed. I think the upsizing wizard forgets about this. The upsizing wizard does a pretty good job of rebuilding the indexes and relationships. It's DTS that loses that stuff. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
Great, but what happens when you wish to change the name of a field in a lookup table? Do you run update statements on EVERY foreign key field? Wouldn't it be easier to just change a value in the database? Further, requesting that user's supply indeces isn't the best way to work; user's have a tendancy to want to change the value of their indices from time to time. For example, If I have table a: CustomerID int AccountingIDchar CompanyName char And I want to change the accounting ID (for whatever reason), I'd be okay. However, if I were to omit the customerID field and stick with the Accounting ID Field, the change would be acceptable. Further, joins on integer fields work much faster than joins on character fields. The database has that much less to work with matching up. You are right though, autonumber fields can be a real pain. What you should usually do in a migration from access to sql server is first go into all of your tables, turn off autonumber, and leave the field as an integer field, when you upsize, go back in and revert to autonumber. -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 13, 2001 11:27 AM To: CF-Talk Subject: RE: SQL VS ACCESS I've learned to avoid using autonumber fields whenever possible. It makes the job of moving databases between platforms simple. At worst, I have to put the primary key constraints back on the index fields of each table - which is no big deal. ---mark Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 13, 2001 4:02 AM To: CF-Talk Subject: RE: SQL VS ACCESS On 6/12/01, Norman Elton penned: Make sure all your fields that should be indexed are indexed. I think the upsizing wizard forgets about this. The upsizing wizard does a pretty good job of rebuilding the indexes and relationships. It's DTS that loses that stuff. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
Costas,
Your point is well taken, but I never meant to imply that I would allow a
primary key field to be anything other than a number (INT).
The name of the primary key field doesn't matter, however I typically do
what you do and prefix (or suffix) the primary key field name with ID.
I setup one table in the database called KEYS. (Very simple: ID_KEY (INT)
USED_DATETIME (date/time). Whenever I need a unique key, I call the
following template:
!-- begin act_global_get_key.cfm --
!--- this template generates a unique key which can be used in any table in
the database ---
cfsetting enablecfoutputonly=yes
cftransaction
cfquery name=max datasource=#session.dsn#
SELECT max (ID_KEY) as maxid
FROM KEYS
/cfquery
cfset ID_KEY = max.maxid+1
cfquery name=insert_key datasource=#session.dsn#
INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
#CreateODBCDateTime(Now())#)
/cfquery
/cftransaction
cfsetting enablecfoutputonly=no
!-- end act_global_get_key.cfm --
If I was using Oracle as the DB, I would call a stored procedure to have the
DB create a unique key for me.
One of the benefits of this is that all numbers used for primary keys in all
tables are unique. So you never have to worry about the scope (persay -
that's not the right term for that) of the number because you know it could
have only come from one of the tables in the system.
The issue about joins is true as well. I'll RARELY join text fields.
Your point about upsizing as an INT field and then changing it back to an
INT PK SEED {n} field is also absolutely valid - if the database supports
autonumbering. Last I checked, Oracle does not support autonumber fields.
(I could be wrong about that.)
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
-Original Message-
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 2:24 PM
To: CF-Talk
Subject: RE: SQL VS ACCESS
Great, but what happens when you wish to change the name of a field in a
lookup table?
Do you run update statements on EVERY foreign key field?
Wouldn't it be easier to just change a value in the database?
Further, requesting that user's supply indeces isn't the best way to work;
user's have a tendancy to want to change the value of their indices from
time to time. For example, If I have table a:
CustomerIDint
AccountingID char
CompanyName char
And I want to change the accounting ID (for whatever reason), I'd be okay.
However, if I were to omit the customerID field and stick with the
Accounting ID Field, the change would be acceptable.
Further, joins on integer fields work much faster than joins on character
fields. The database has that much less to work with matching up.
You are right though, autonumber fields can be a real pain. What
you should
usually do in a migration from access to sql server is first go
into all of
your tables, turn off autonumber, and leave the field as an integer field,
when you upsize, go back in and revert to autonumber.
-Original Message-
From: Mark Warrick [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 11:27 AM
To: CF-Talk
Subject: RE: SQL VS ACCESS
I've learned to avoid using autonumber fields whenever possible. It makes
the job of moving databases between platforms simple. At worst, I have to
put the primary key constraints back on the index fields of each table -
which is no big deal.
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
-Original Message-
From: Bud [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 4:02 AM
To: CF-Talk
Subject: RE: SQL VS ACCESS
On 6/12/01, Norman Elton penned:
Make sure all your fields that should be indexed are indexed. I think
the upsizing wizard forgets about this.
The upsizing wizard does a pretty good job of rebuilding the indexes
and relationships. It's DTS that loses that stuff.
--
Bud Schneehagen - Tropical Web Creations
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com
Re: SQL VS ACCESS
Concerning Oracle (and I don't have a lot of experience) you can set up
autincrementing fields I don't remember the details because it has been
about a year but select from dual or something similiar will get the next
value. You can also reset the starting pt etc.
- Original Message -
From: Mark Warrick [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, June 13, 2001 5:44 PM
Subject: RE: SQL VS ACCESS
Costas,
Your point is well taken, but I never meant to imply that I would allow a
primary key field to be anything other than a number (INT).
The name of the primary key field doesn't matter, however I typically do
what you do and prefix (or suffix) the primary key field name with ID.
I setup one table in the database called KEYS. (Very simple: ID_KEY (INT)
USED_DATETIME (date/time). Whenever I need a unique key, I call the
following template:
!-- begin act_global_get_key.cfm --
!--- this template generates a unique key which can be used in any table
in
the database ---
cfsetting enablecfoutputonly=yes
cftransaction
cfquery name=max datasource=#session.dsn#
SELECT max (ID_KEY) as maxid
FROM KEYS
/cfquery
cfset ID_KEY = max.maxid+1
cfquery name=insert_key datasource=#session.dsn#
INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
#CreateODBCDateTime(Now())#)
/cfquery
/cftransaction
cfsetting enablecfoutputonly=no
!-- end act_global_get_key.cfm --
If I was using Oracle as the DB, I would call a stored procedure to have
the
DB create a unique key for me.
One of the benefits of this is that all numbers used for primary keys in
all
tables are unique. So you never have to worry about the scope (persay -
that's not the right term for that) of the number because you know it
could
have only come from one of the tables in the system.
The issue about joins is true as well. I'll RARELY join text fields.
Your point about upsizing as an INT field and then changing it back to an
INT PK SEED {n} field is also absolutely valid - if the database supports
autonumbering. Last I checked, Oracle does not support autonumber fields.
(I could be wrong about that.)
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
-Original Message-
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 2:24 PM
To: CF-Talk
Subject: RE: SQL VS ACCESS
Great, but what happens when you wish to change the name of a field in a
lookup table?
Do you run update statements on EVERY foreign key field?
Wouldn't it be easier to just change a value in the database?
Further, requesting that user's supply indeces isn't the best way to
work;
user's have a tendancy to want to change the value of their indices from
time to time. For example, If I have table a:
CustomerID int
AccountingID char
CompanyName char
And I want to change the accounting ID (for whatever reason), I'd be
okay.
However, if I were to omit the customerID field and stick with the
Accounting ID Field, the change would be acceptable.
Further, joins on integer fields work much faster than joins on
character
fields. The database has that much less to work with matching up.
You are right though, autonumber fields can be a real pain. What
you should
usually do in a migration from access to sql server is first go
into all of
your tables, turn off autonumber, and leave the field as an integer
field,
when you upsize, go back in and revert to autonumber.
-Original Message-
From: Mark Warrick [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 11:27 AM
To: CF-Talk
Subject: RE: SQL VS ACCESS
I've learned to avoid using autonumber fields whenever possible. It
makes
the job of moving databases between platforms simple. At worst, I have
to
put the primary key constraints back on the index fields of each table -
which is no big deal.
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
-Original Message-
From: Bud [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 4:02 AM
To: CF-Talk
Subject: RE: SQL VS ACCESS
On 6/12/01, Norman Elton penned:
Make sure all your fields that should be indexed are indexed. I think
the upsizing wizard forgets about this.
The upsizing wizard does a pretty good job of rebuilding the indexes
and relationships. It's DTS
