Bryan Stevenson B.Comm.
VP & Director of E-Commerce Development
Electric Edge Systems Group Inc.
t. 250.920.8830
e. [EMAIL PROTECTED]
---------------------------------------------------------
Macromedia Associate Partner
www.macromedia.com
---------------------------------------------------------
Vancouver Island ColdFusion Users Group
Founder & Director
www.cfug-vancouverisland.com
----- Original Message -----
From: Tyler Clendenin
To: CF-Talk
Sent: Wednesday, November 05, 2003 8:28 AM
Subject: RE: Oracle Sequences
right but that is not a very intuitive approach, shouldn't there be
something. is it just because the oracle driver does not allow multiple
queries in the same cfquery and the mssql driver does? is there any way i
can get around this. i really don't want to have to break that statement
out into another query. i'm sure there is a performance hit of some sort.
i want to make this as scalable as possible and am trying to follow that
idea in all the code. plus it would just be annoying to have to do another
cfquery just to only execute one more command.
Tyler Clendenin
GSL Solutions
_____
From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 10:44 AM
To: CF-Talk
Subject: RE: Oracle Sequences
Try doing two separate queries:
<cfquery name="insertData">
INSERT INTO ...
</cfquery>
<cfquery name="getSeq">
SELECT PKSeq.CurrVal
</cfquery>
Stick them inside a transaction and you should be good to go.
-----Original Message-----
From: Tyler Clendenin [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 10:48 AM
To: CF-Talk
Subject: Oracle Sequences
I am using CF6.1 on a redhat linux server connecting to Oracle 8i. I am
trying to select the CurrVal of the sequence that i just used to insert a
recod. in mssql server i can do this by selecting @@identity and i know in
oracle i can do it using SeqName.CurrVal. The problem is i get this error
when trying it in oracle:
"[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly
ended "
i am sure it is because there are multiple queries in the same cfquery but
this is the way it must be done for data integrity reasons yes? I don't
want to split it into two cfqueries because that would both create
opportunities for mixed up data and would add an extra call to the db (which
all in all is not that bad but should not be necessary).
The query i am running loks like this.
INSERT INTO TableName(PK,
Name,
FK)
VALUES(PKSeq.NextVal,
'NameValue',
#FK#)
SELECT PKSeq.CurrVal AS PK
I am sure that someone has run into this before I just hope there is a
better answer then splitting into two queries.
I also tried seperating the two queries with a semi-colon and that gave this
error
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character
Tyler Clendenin
GSL Solutions
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
