answer.
Tyler Clendenin
GSL Solutions
_____
From: Dave Carabetta [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 11:19 AM
To: CF-Talk
Subject: Re: 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
>
JDBC has a limitation where you can't have multiple queries within the same
cfquery, as least for Oracle (not sure about the other vendors). You have to
have two separate calls inside of a cftransaction. Either that, or convert
the process into a stored proc and just set a return value to the primary
key value.
Regards,
Dave.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
