can't use <cftransaction>
First we do a nextval for the sequence. Because asking for the nextval
creates the record, it effectively "reserves" the slot for the rest of what
we need to do. Then in another query, we do an insert or update into the
record reserved for us by the nextval.
Because we already have the key for the record, we can also do any other
queries or whatver need to be done without asking for that record position
again.
-Kevin
----- Original Message -----
From: "Tyler Clendenin" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, November 05, 2003 10: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]
