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

What is the problem? `r.getPrefix().generatedkey` will get the inserted record ID. — Abhishekh Gupta, May 16 '15 at 17:43
on a select - I presume you're correct... 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 — j-p, May 16 '15 at 17:50

score 3 · Accepted Answer · edited May 17 '15 at 00:38

3

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)

edited May 17 '15 at 00:38

Leigh

28,765
10
55
103

answered May 16 '15 at 17:58

Abhishekh Gupta

6,206
4
18
46

1

Good catch. IMO, it was a poor choice for method names on the part of Adobe. Given that the tag version uses `result` to return the `generatedkey`, naturally most folks try `getResult()`. However, the docs mention the cryptically named `getPrefix()` method is the one you want here: *Prefix: Equivalent to the result attribute for the cfquery tag*. – Leigh May 17 '15 at 00:37

score 1 · Answer 2 · answered May 16 '15 at 17:57

1

after some futzing... THIS seems to work

... tmp = q.execute( sql=sql );

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

        q.clearParams();

    return r;

answered May 16 '15 at 17:57

j-p

3,698
9
50
93

getting result metadata from coldfusion newQuery() in cfscript

2 Answers2

Linked