CODEWITHSUNDEEP
oracle-databasecoldfusion
John
John

Reputation: 13699

ColdFusion using Oracle's "INSERT ... RETURNING ... INTO" statement

I am building a simple chat application with ColdFusion and I am trying to get a message ID when I run my insert. Here is what I have so far

<cffunction name="putMessage" access="remote" returnformat="JSON">

     <cfargument name="message" />
     <cfset LOCAL.id = 0 />

     <cfquery name="insertquery" datasource="myDS"> 
          insert into 
               chat (message) 
          values 
               (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.message#">)
          RETURNING
               id
          INTO
               <cfqueryparam cfsqltype="cf_sql_numeric" value="#LOCAL.id#">
     </cfquery>

     <cfreturn LOCAL.id />
        
</cffunction>

The error I am getting is

Error Executing Database Query.

Not all return parameters registered

I know there are workarounds with stored procedures, and running multiple queries. I would like to know how to do it this way.

Upvotes: 3

Views: 1624

Answers (2)

David Faber
David Faber

Reputation: 12485

This is an old question so apologies in advance for the necromancy. In my opinion the best (and maybe only) way to accomplish this is by wrapping the INSERT statement in a stored procedure and returning the values from the RETURNING ... INTO clause in an output parameter.

CREATE OR REPLACE PROCEDURE chat_insert
  ( p_message IN VARCHAR2, r_id OUT NUMBER )
AS
BEGIN
  INSERT INTO chat ( message )
  VALUES ( p_message )
  RETURNING id INTO r_id;
END;
/

You can then invoke this stored procedure from ColdFusion as follows:

<cffunction name="putMessage" access="remote" returnformat="JSON">
    <cfargument name="message" />
    <cfset LOCAL.id = 0 />

    <cfstoredproc procedure="chat_insert" datasource="myDS">
        <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="in" value="#ARGUMENTS.message#" />
        <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="LOCAL.id" />
    </cfstoredproc>

    <cfreturn LOCAL.id />
</cffunction>

Hope this helps.

Upvotes: 2

Mark A Kruger
Mark A Kruger

Reputation: 7193

ID is something returned by the query - not something you are going to send into the query - right? Something like (note - untested):

 <cfargument name="message" />
 <cfset LOCAL.id = 0 />

 <cfquery name="insertquery" datasource="myDS"> 

      DECLARE newID NUMBER;

      insert into 
           chat (message) 
      values 
           (<cfqueryparam cfsqltype="cf_sql_varchar" 
                                  value="#ARGUMENTS.message#">)
      RETURNING
           id
      INTO
          newID
 </cfquery>

 <cfreturn insertquery.newID />

Otherwise you are expecting the driver to turn an Oracle var into a CF var before CF has actually received results from the driver conneciton (you are also passing the value 0, and not the name of the var you wish to return when you use #local.id# in value="").

EDIT: If this is an "autoincrement" field another approach is to use the "Result" variable. That looks like this.

<cfquery name="insertquery" datasource="myDS" result="r"> 
      insert into 
           chat (message) 
      values 
           (<cfqueryparam cfsqltype="cf_sql_varchar" 
                                  value="#ARGUMENTS.message#">)

 </cfquery>

<cfreturn r.rowID/>

RowID is the Oracle version I think. Note - this is CF8 or later.

Upvotes: -1

Related Questions