CFSCRIPT query questions?

wwilliams · January 13, 2019, 12:08am

So we recently have been doing a ton more with our new projects using cfscript.

See example below:

VARIABLES.testQuery = new Query(
                datasource = "TestDB",
                name = "testQueryName",
                sql =
                    "SELECT testCol FROM testTable;"
                );
            VARIABLES.testQuery = VARIABLES.testQuery.Execute().GetResult();

What does the name attribute even do? If I try to use #testQueryName.testCol# it errors out. I know I can get the data using #testQuery.testCol#, but why not have it work the same as the tag?

AS A SIDE NOTE: Also when using cfscript and creating a query that does an insert, why does it not just return a PostgreSQL returning variable. See example below:

VARIABLES.testQuery = new Query(
                datasource = "TestDB",
                name = "testQueryName",
                sql =
                    "INSERT into testCol ( testCol )
                    VALUES ('test' )
                    RETURNING ID;"
                );
            VARIABLES.testQuery = VARIABLES.testQuery.Execute().GetResult();

With a tag, the ID is returned, but in the cfsrcipt version, you have to NOT use GetResults() but instead use GetPrefix(). Is there a way to have all this done just like the tag?

FINALLY - Should I scope the query tag into the VARIABLES scope, like VARIABLES.testQuery or just do testQuery?

Thanks in advance!

ddspringle · January 13, 2019, 12:48am

new query() is an abomination… blame Adobe.

That said, I suggest you use queryExecute() queryExecute Code Examples and CFML Documentation instead of new query().

For example:

testQuery = queryExecute( 'SELECT testCol FROM testTable WHERE idCol = :id', 
    { id = { value = '42', cfsqltype = 'cf_sql_int' } } );

As for getting the id back, you can pass the result as an option and get the generatedKey, for example:

testQuery = queryExecute( 'INSERT INTO testTable ( testCol ) VALUES ( :testData ) 
    RETURNING ID', 
    { testData = { value = 'my test data', cfsqltype = 'cf_sql_varchar' } }, 
    { result = 'insertResult' } );

writeDump( insertResult.generatedKey );

Further reading on generatedKey from insert queries

https://lucee.daemonite.io/t/does-queryexecute-return-an-identity-column-on-inserts/3388

^^ I missed that you were using PostgreSQL - edited to add the required RETURNING clause to get the identity into generatedKey()

And finally, as for scoping it depends on the context. While there is no harm in variables scoping everything (inside a .cfm file), there are instances where you don’t need all the typing.

If you’re building a .cfm file, then variables is the default scope, so no need to specifically spell it out.

If you’re building a .cfc file, then inside functions use var, for example:

public string function myFunction( required string someParam ) {

    var myFunctionLocalVariable = [];

    myFunctionlocalVariable.append( { structVar = 'something' } );
}