I have never been able to get an equivalent to happen in Oracle
through a cfquery, I am guessing due to the drivers. Could do a SP in
oracle that returns the value. I'd love to see a working example
though of how this could be done in one cfquery when using oracle.
On Fri, 10 Dec 2004 14:34:00 -0600, Schreck, Tom
<[EMAIL PROTECTED]> wrote:
> How do you do the Oracle equivalent to:
>
> DECLARE @MyGUID uniqueidentifier
> SELECT @MyGUID = NewID()
> INSERT INTO MyTable(Col1, Col2, ColGUID, Col3)
> VALUES('aaa', 'bbb', @MyGUID, 'zzz')
> SELECT @MyGUID AS MyGUID
>
> This is what I've come up with and it's not working:
>
> <cfquery>
> DECLARE myguid varchar(100);
> BEGIN
> myguid := SYS_GUID();
>
> INSERT INTO TABLE (GUID,OTHER FIELDS)
> VALUES(myguid,OTHER FIELD VALUES)
>
> END;
> </cfquery>
>
>
>
> Thanks
>
> Tom Schreck
> 972-361-9943
>
> -----Original Message-----
> From: Steve Runyon [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 08, 2004 10:51 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [CFCDev] GUIDs as Primary/Foreign Keys
>
> I know we're supposed to code to handle exceptions like that, but
> that's so unlikely for GUIDs that (IMHO) you're more likely to mistype
> something and cause an error than your error-prevention code is to
> prevent an error that would otherwise have occurred. Of course, I
> could be totally wrong! :) Has anybody here ever seen a duplicate
> GUID?
>
> Somebody in a previous email mentioned getting the values of the
> just-inserted keys. Here are some tricks:
>
> * In SQL Server, use scope_identity() after inserting the row to get
> the value of the identity that was just inserted. You can do it in a
> cfquery like this:
> <cfquery name="blah" datasource="blahdsn">
> declare @IntPK int
>
> insert into MyIdentityTable ( ... )
> values ( ... )
>
> set @IntPK = scope_identity()
> select @IntPK as NewIntPK
> </cfquery>
> Don't use @@identity or ident_current() - you might not get what you
> expect.
> * Do something similar to get a GUID PK.
> <cfquery name="blah" datasource="blahdsn">
> declare @GuidPK uniqueidentifier
> set @GuidPK = newid()
>
> insert into MyGuidTable( GuidPK, ... )
> values ( @GuidPK, ... )
>
> select @GuidPK as NewGuidPK
> </cfquery>
> * In Oracle, do it like the GUID in SQL, but use the sequence instead
> of newid().
> ----------------------------------------------------------
> You are subscribed to cfcdev. To unsubscribe, send an email
> to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
> in the message of the email.
>
> CFCDev is run by CFCZone (www.cfczone.org) and supported
> by Mindtool, Corporation (www.mindtool.com).
>
> An archive of the CFCDev list is available at
> [EMAIL PROTECTED]
> ----------------------------------------------------------
> You are subscribed to cfcdev. To unsubscribe, send an email
> to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
> in the message of the email.
>
> CFCDev is run by CFCZone (www.cfczone.org) and supported
> by Mindtool, Corporation (www.mindtool.com).
>
> An archive of the CFCDev list is available at
> [EMAIL PROTECTED]
>
--
Aaron Rouse
http://www.happyhacker.com/
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at
[EMAIL PROTECTED]
