I like Beth's idea of a function, as its very generic and would allow DBs that don't support sequences to simulate them. I have used the method below in MySQL when I needed records across multiple tables to have a unique id.
<snip from the mysql manual> Create a table to hold the sequence counter and initialize it: mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0); Use the table to generate sequence numbers like this: mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> SELECT LAST_INSERT_ID(); So if I can add a getSequenceID() function to an object and configure Reactor to automagically call it, that would be great. Chris ________________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Kotek Sent: 21 February 2006 13:47 To: [email protected] Subject: Re: [Reactor For CF] Oracle support for Reactor I'm torn. I like Beth's solution because it is very generic and would handle UUID's, sequences, or just about anything else. But I like Doug's solution of specifying the sequence <object name="User" sequence="UserSequence"> .... </object> because it means less work to use it (ie I don't have to create a getUniqueID method for all of my Oracle tables). On 2/21/06, Bowden, Beth (NIH/NIEHS) [E] <[EMAIL PROTECTED]> wrote: How about just two pktypes, "autonumber" or "function"? "Function" would mean use the "getUniqueID" function. This would add additional flexibility to use any different numbering scheme. Beth Bowden Phone: 919-541-3355 FAX: 919-541-3687 E-Mail: [EMAIL PROTECTED] Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709 ________________________________________ From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 21, 2006 8:18 AM To: [email protected] Subject: Re: [Reactor For CF] Oracle support for Reactor Perhaps something user-configurable in the <config> set specifically for Oracle (or any other DBs that don't have autonumber)? Maybe something like this (with autonumber as the default if this tag wasn't specified): <config> <dsn value="Scratch" /> <type value="oracle" /> <pktype value="autonumber|sequence|uuid" /> <mapping value="/ScratchData" /> <mode value="development" /> </config> The trick with sequence seems to be, how do you tell Reactor where to get the next sequence value. Is there one for each table or a global one for each project that is used for all tables? Are the sequences named by convention ie "seq_customers" or would one have to specify the sequence to be used for each table? On 2/21/06, Bowden, Beth (NIH/NIEHS) [E] <[EMAIL PROTECTED]> wrote: I don't know of an Oracle column type that behaves like a sequence unless she meant adding a trigger. In Oracle, you can define a trigger on a column that will take the next sequence value and insert it into the column. The issue I have is returning that key. Since Oracle doesn't have the "Identity" or "autonumber", it also doesn't have the corresponding LAST_INSERT_ID() or SCOPE_IDENTITY() functions. Another thought is to use UUID for keys. Beth Bowden Phone: 919-541-3355 FAX: 919-541-3687 E-Mail: [EMAIL PROTECTED] Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709 -----Original Message----- From: Sean Corfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 21, 2006 1:55 AM To: [email protected] Subject: Re: [Reactor For CF] Oracle support for Reactor On 2/20/06, Doug Hughes <[EMAIL PROTECTED]> wrote: > So, when working with oracle you need to create your IDs ahead of time? > > In PostgreSQL they use sequences too. However, I think you can define a > default of the next value from a sequence to a column. (It's been a while, > forgive me if I'm wrong.) > > Is there anything like that in Oracle? I just chatted to one of my Oracle DBA friends (she's in India looking after her mother right now) and she says that there is a column type that behaves like a sequence number but I suspect that isn't the default way that legacy databases are created in Oracle. For the most part, I've used a dedicated SEQUENCE as Beth indicates. Not sure how to make this clean and generic across multiple DBs. -- Sean A Corfield -- http://corfield.org/ Got frameworks? "If you're not annoying somebody, you're not really alive." -- Margaret Atwood -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
