RE: [Reactor For CF] Oracle support for Reactor
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: reactor@doughughes.net 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
RE: [Reactor For CF] Oracle support for Reactor
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: reactor@doughughes.net 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: reactor@doughughes.net 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
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" …. /objectbecause 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: reactor@doughughes.net 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: reactor@doughughes.net 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
RE: [Reactor For CF] Oracle support for Reactor
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: reactor@doughughes.net 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: reactor@doughughes.net 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: reactor@doughughes.net 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor
Re: [Reactor For CF] Oracle support for Reactor
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. That sounds like a good solution to me but two questions: 1) the pktype would be specified per-object, yes? 2) where would the getUniqueID() function live? -- 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
[Reactor For CF] Oracle support for Reactor
Doug,
I've run into one problem migrating the Reactor blog to Oracle. Oracle doesn't
have an autonumber or identity equivalent. So the function,
Convention.lastInseredIdSyntax, doesn't work. Oracle has sequences which can
be used as a work-around. The code below is a modified version of the create
function in EntryDAO in the blog app (the modified lines are marked w/ an x).
This seems to require always creating a custom create function -- that seems
very much against the reactor spirit. I thought about changing the XSL DAO
template.
Any suggestions?
Thanks,
Beth
cffunction name=create access=public hint=I create a Entry
object. output=false returntype=void
cfargument name=to hint=I am the transfer object for Entry
required=yes type=reactor.project.ReactorBlog.To.EntryTo /
cfset var Convention = getConventions() /
cfset var qCreate = 0 /
x cfset var qUniqueSeq = 0 /
cftransaction
x cfquery name=qUniqueSeq
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
x select unique_seq.nextval nextSequenceValue
x from dual
x /cfquery
cfquery name=qCreate
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
INSERT INTO
#Convention.FormatObjectName(getObjectMetadata(), '')#
(
#Convention.formatFieldName('ENTRYID', 'Entry')#
,...
) VALUES (
cfqueryparam
cfsqltype=cf_sql_numeric
scale=22
x
value=#qUniqueSeq.nextSequenceValue#
/...
)
cfif ListFindNoCase(mssql,
_getConfig().getType())
#Convention.lastInseredIdSyntax(getObjectMetadata())#
/cfif
/cfquery
x cfif NOT ListFindNoCase(mssql,oracle,
_getConfig().getType())
cfquery name=qCreate
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
#Convention.lastInseredIdSyntax(getObjectMetadata())#
/cfquery
/cfif
/cftransaction
/cffunction
-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
winmail.dat
RE: [Reactor For CF] Oracle support for Reactor
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? What I'm really asking is, from a
metadata perspective, how do you know if a particular column relies on a
sequence and can you identify the particular sequence?
If we can find that out, we'll edit (*cough* hack *cough*) the xsl to create
the project record with the needed sequence-value-getter. It'll end up in
every project record cfc, unfortunately, but it's important enough that it's
probably worth the hack.
Doug
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bowden,
Beth (NIH/NIEHS) [E]
Sent: Monday, February 20, 2006 5:33 PM
To: reactor@doughughes.net; [EMAIL PROTECTED]
Subject: [Reactor For CF] Oracle support for Reactor
Doug,
I've run into one problem migrating the Reactor blog to Oracle. Oracle
doesn't have an autonumber or identity equivalent. So the function,
Convention.lastInseredIdSyntax, doesn't work. Oracle has sequences which
can be used as a work-around. The code below is a modified version of the
create function in EntryDAO in the blog app (the modified lines are marked
w/ an x). This seems to require always creating a custom create function
-- that seems very much against the reactor spirit. I thought about changing
the XSL DAO template.
Any suggestions?
Thanks,
Beth
cffunction name=create access=public hint=I create a Entry
object. output=false returntype=void
cfargument name=to hint=I am the transfer object for
Entry required=yes type=reactor.project.ReactorBlog.To.EntryTo /
cfset var Convention = getConventions() /
cfset var qCreate = 0 /
x cfset var qUniqueSeq = 0 /
cftransaction
x cfquery name=qUniqueSeq
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
x select unique_seq.nextval nextSequenceValue
x from dual
x /cfquery
cfquery name=qCreate
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
INSERT INTO
#Convention.FormatObjectName(getObjectMetadata(), '')#
(
#Convention.formatFieldName('ENTRYID', 'Entry')#
,...
) VALUES (
cfqueryparam
cfsqltype=cf_sql_numeric
scale=22
x
value=#qUniqueSeq.nextSequenceValue#
/...
)
cfif ListFindNoCase(mssql,
_getConfig().getType())
#Convention.lastInseredIdSyntax(getObjectMetadata())#
/cfif
/cfquery
x cfif NOT ListFindNoCase(mssql,oracle,
_getConfig().getType())
cfquery name=qCreate
datasource=#_getConfig().getDsn()# username=#_getConfig().getUsername()#
password=#_getConfig().getPassword()#
#Convention.lastInseredIdSyntax(getObjectMetadata())#
/cfquery
/cfif
/cftransaction
/cffunction
-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
