ColdFusion queryExecute parameters object?

Question

I started using cfscript in my projects recently. While working with cfscript I used queryExecute() function to run queries. In few situations there was more than one query in the same function. These queries shared some of the same parameters. I was wondering if I can have one structure that will hold all parameters for all queries. This is what I tried:

local.qryParams = structNew();
structAppend(qryParams, {rec_id = {cfsqltype="cf_sql_numeric", value="#arguments.rec_id#"}});
structAppend(qryParams, {position = "#application.position#"});
structAppend(qryParams, {user_id = "#client.userid#"});
structAppend(qryParams, {title = {cfsqltype="cf_sql_varchar", value="#arguments.title#", maxlength: 50}});
structAppend(qryParams, {description = {cfsqltype="cf_sql_varchar", value="#arguments.description#", maxlength: 1000}});

Then I have two variables with SQL:

local.insertSQL = "
    INSERT INTO notes(rec_id, title, description, user_id, last_update) 
    VALUES (:rec_id, :title, :description, :user_id, getDate())
";
local.updateSQL = "
    UPDATE profile
    SET last_update = getDate(),
        user_id = :user_id
    WHERE rec_id = :rec_id
        AND position = :position
";

Then on the very end I have two execute functions:

queryExecute(insertSQL, qryParams , {datasource="#application.datasource#", result: "insertResult"});
queryExecute(updateSQL, qryParams , {datasource="#application.datasource#", result: "updateResult"});

Both functions executed successfully and record was inserted/updated. My questions are:

Is this a good approach to store all query parameters in one structure?
Is there any potential problem or security issue doing it that way?
Any suggestions/comments?

Thank you!

In few situations there was more than one query in the same function If any of the queries are used multiple times, like 'update profile', I'd move those queries to a separate function. — SOS
– SOS, Commented Jun 25, 2019 at 19:32
@Ageax Is there any benefit moving that query to a separate function other than redundancy effect. Thank you. — espresso_coffee
– espresso_coffee, Commented Jun 26, 2019 at 14:35
The primarily benefit is eliminating redundant code. There's a minor o/h in calling additional methods, but IMO that's outweighed by the gains in clarity and eliminating duplication. Another option is using stored procedures. The advantage of procs is a) they're capable of more complex logic b) can return multiple results (cfquery's can't) c) only require a single trip to the db. In fairness, you can also do "c)" with a cfquery - but that's not what cfquery's designed for. — SOS
– SOS, Commented Jun 26, 2019 at 16:44
I would highly recommend stored procedures for more than simple sql. Also, don't forget that you don't really want to make multiple connections to your database if you can help it. If you can send the data to a sproc rather than running multiple CF functions, that would be much nicer to your network and database. — Shawn
– Shawn, Commented Jun 26, 2019 at 20:11

Community · Accepted Answer · 2019-06-26 20:41:08Z

3

This is kind of an open ended question, but here goes

Q1: Is this a good approach to store all query parameters in one structure?
A1: Yes, but you may want to consider using entities if you like this.

Q2: Is there any potential problem or security issue doing it that way?
A2: None that I know of

Q3: Any suggestions/comments?
A3:

If your datasource is in application.cfc, you don't need to explicity state it.
You are getting the result the hard way. Consider myResult=QueryExecute(...)
You can build the struct in one shot. You don't need all these structAppend()s. structNew() is way too much typing. Consider {}.
user_id = "#client.userid#" is way too much typing... Just user_id = client.userid is enough.
You changed styles of building structs {cfsqltype="cf_sql_varchar", value="#arguments.title#", maxlength: 50}. IMHO, use : instead of =

Hope this is helpful

edited Jun 26, 2019 at 20:41

CommunityBot

11 silver badge

answered Jun 25, 2019 at 15:48

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SOS Over a year ago

Two other comments. Don't forget to 1) local scope any result variables to avoid threading issues 2) wrap multiple, related queries, in a transaction.

Collectives™ on Stack Overflow

ColdFusion queryExecute parameters object?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related