<cfquery name="qTAS_NewID" datasource="#REQUEST.sIF_DSN#">
SELECT questionnaire_seq.nextval NEW_ID
FROM dual
</cfquery>
<cfset iNewQuestID = qNewID.NEW_ID>
<cfquery name="qCopyQuestInfo" datasource="#REQUEST.sIF_DSN#">
INSERT INTO QUESTIONNAIRE (
ID, NAME, CREATED, AUTHOR
) VALUES (
<cfqueryparam value="#iNewQuestID#" cfsqltype="CF_SQL_DECIMAL">,
<cfqueryparam value="#sNewQuestName#"
cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP">,
<cfqueryparam value="#SESSION.Username#"
cfsqltype="CF_SQL_VARCHAR">
)
</cfquery>
-----Original Message-----
From: Tyler Clendenin [mailto:[EMAIL PROTECTED]
Sent: 05 November 2003 16:24
To: CF-Talk
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]