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://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
