CFSCRIPT query questions?

language
2

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' } );
}

Further reading on the variables scope

HTH

– Denny

2 Likes