shot from CF. And, you can't use <cftransaction> either, as each call
carries an implicit commit. What you need to do is the first query to get
the seq. using
SELECT myseq.NEXTVAL as newid from dual
then use it in your next query
insert into mytable(myid)
values (#myquery.nextval#)
Of course this is pseudo code and you should be usin queryparams, etc.
-d
----- Original Message -----
From: "Tyler Clendenin" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, November 05, 2003 10:24 AM
Subject: RE: Oracle Sequences
> right i tried that too (sorry forgot to post it), still the same error of
> "[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not
properly
> ended "
>
> Tyler Clendenin
> GSL Solutions
>
> _____
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 05, 2003 10:47 AM
> To: CF-Talk
> Subject: RE: Oracle Sequences
>
>
> in Oracle you select stuff like this from the table dual, Oracles all
> powerful ally.
>
>
> Example
> select elem_seq.currval from dual
>
> Doug
>
> -----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]
