getting result metadata from coldfusion newQuery() in cfscript

Question

Documentation on CFscript is a bit sparse in the docs, and searching for a cfscript specific answer gets lost in CF tag answers. So here's my question:

How do I get the result metadata from a query that was performed using script? Using tags I can add result="myNamedResultVar" to my cfquery. I can then refer to the query name for data, or myNamedResultVar for some metadata. However, now I'm trying to write everything in script, so my component is script based, top-to-bottom. What I'm ultimately after is the last inserted Id from a MySQL insert. That ID exists in the result metadata.

myNamedResultVar.getPrefix().generatedkey

Here's my query code:

public any function insertUser( required string name, required string email, required string pass ) {
    // insert user
    var sql     = '';
    var tmp     = '';
    var q       = new query();

        q.setDatasource( application.dsn );

        q.addParam(
                    name='name'
                    ,value='#trim( arguments.name )#'
                    ,cfsqltype='CF_SQL_VARCHAR'
                );
        q.addParam(
                    name='email'
                    ,value='#trim( arguments.email )#'
                    ,cfsqltype='CF_SQL_VARCHAR'
                );
        q.addParam(
                    name='pass'
                    ,value='#hashMyString( arguments.pass )#'
                    ,cfsqltype='CF_SQL_VARCHAR'
                );


        sql = 'INSERT INTO
                    users
                    (
                        name
                        ,email
                        ,pass
                        ,joined
                        ,lastaccess
                    )
                VALUES
                    (
                        :name
                        ,:email
                        ,:pass
                        ,CURRENT_TIMESTAMP
                        ,CURRENT_TIMESTAMP
                    );
            ';

        tmp = q.execute( sql=sql );

        q.clearParams();

}

How do I specify the result data? I've tried something like this: ... tmp = q.execute( sql=sql );

var r = tmp.getResult();
    r = r.getPrefix().generatedkey;

q.clearParams();

return r;

However, on an insert the getResult() returns a NULL as best I can tell. So the r.getPrefix().generatedkey does NOT work after an insert. I get r is undefined

Answer 1

You are getting the result property of the query first and then from that you are trying to get the prefix property in result. But this is not the case. You can directly get the prefix property and then the generated key like this:

tmp.getPrefix().generatedkey;

For reference you can check this blog entry: Getting the Generated Key From A Query in ColdFusion (Including Script Based Queries)