How to call an INSERT query by name?

Question

I've to clone a class ID #FORM.classid# several times but instead of writing the INSERT sql query each time I need it, is there any solution to just write it one time and call the query with its addClass name ? I wish I know how to code functions but I'm a newbie to CFML programming.

<cfquery name="currentClass" datasource="#dsn#">
        SELECT class_name, class_description
        FROM classes
        WHERE classid = <cfqueryparam value="#FORM.classid#" cfsqltype="cf_sql_numeric"> 
</cfquery>
<cfquery name="addClass" datasource="#dsn#">   
        INSERT INTO classes (class_name,class_description)
        VALUES ('#currentClass.class_name#', '#currentClass.class_description#')
</cfquery>
<cfquery name="getNewID" datasource="#dsn#">
        Select LAST_INSERT_ID() as classid
</cfquery>

Snipe656 · Accepted Answer · 2012-06-13T17:32:16.983

Make a function to do the work for you and then call that function. This is how you could do the function if making it within ColdFusion. You could though simplify the amount of queries you are running to get to your end goal but I just wrote this via what you are already doing:

<cffunction name="AddTheClass" access="public" returntype="numeric">
    <cfargument name="ClassID" required="true" type="numeric" />

    <cfscript>
        var currentClass    = "";
        var addClass        = "";
        var getNewID        = "";

        var myResult        = 0;
    </cfscript>

    <cfquery name="currentClass" datasource="#dsn#">
        SELECT class_name, class_description
        FROM classes
        WHERE classid = <cfqueryparam value="#Arguments.classid#" cfsqltype="cf_sql_numeric"> 
    </cfquery>
    <cfquery name="addClass" datasource="#dsn#">   
        INSERT INTO classes (class_name,class_description)
        VALUES ('#currentClass.class_name#', '#currentClass.class_description#')
    </cfquery>
    <cfquery name="getNewID" datasource="#dsn#">
        Select LAST_INSERT_ID() as classid
        </cfquery>

    <cfset myResult = getNewID.classid />

    <cfreturn myResult />
</cffunction>

<!--- How to call it --->
<cfset intNewClassID    = AddTheClass(ClassID=Form.classid) />

EDIT: You may need to make adjustments for your datasource variable. Kind of depends on where it comes from and hard to tell since you did not scope that variable.

@StephaneKouakou - Also, if you are using CF8+ you can use [cfquery's](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html) `result` attribute to retrieve the new id instead of `LAST_INSERT_ID()`. (Edit: Ooops did not notice Busches mentioned that already :) — Leigh, Jun 13 '12 at 18:27

score 3 · Answer 2 · answered Jun 13 '12 at 17:34

Here's one way to do it. Reading up on functions in the documentation would be a good start too. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f5c.html

<cffunction name='addClass' returntype='numeric'>
    <cfargument name='ClassID' type='numeric' required='yes' />
    <cfargument name='DSN' type='string' required='yes' />

    <cfset var currentClass = '' />
    <cfset var addClass = '' />
    <cfset var getNewID = '' />

    <cfquery name="currentClass" datasource="#Arguments.dsn#">
    SELECT class_name, class_description
    FROM classes
    WHERE classid = <cfqueryparam value="#Arguments.classid#" cfsqltype="cf_sql_numeric"> 
    </cfquery>
    <cfquery name="addClass" datasource="#Arguments.dsn#">   
    INSERT INTO classes (class_name,class_description)
    VALUES ('#currentClass.class_name#', '#currentClass.class_description#')
    </cfquery>
    <cfquery name="getNewID" datasource="#Arguments.dsn#">
    Select LAST_INSERT_ID() as classid
    </cfquery>
    <cfreturn getNewID.ClassID />
</cffunction>

You should also look into using the result attribute on the cfquery tag as you may be able to replace the last query with it. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

If you are unable to utilize the result method, then I would utilize to ensure that you don't run into race conditions between the two queries.

[e] You'll notice I added DSN to the arguments, as you shouldn't reference variables outside the scope of the function! [e2] You should also use cfqueryparam tags in your cfqueries, but I'll let you update that part.

How to call an INSERT query by name?

2 Answers2