The CFQueryParam Tag Does Not Require The "CF_SQL_" Prefix On SQL Types In Lucee 5.3.2.77
This is primarily a note to self; but, one nice thing about moving from Adobe ColdFusion 10 to Lucee CFML 5.3.2.77 is that I can start to omit the cf_sql_ prefix in my cfqueryparam tags. So, instead of using something like cf_sql_longvarchar, I can just use longvarchar. I never understood the meaning of the cf_sql_ prefix; and, being able to remove it will make the code seem cleaner.
NOTE: According to the CFDocs page on
cfqueryparam, this is also true for Adobe ColdFusion 11+.
To test this, I created a MySQL table with a number of different column types. And then, tried to insert a row using the new cfqueryparam syntax:
<cfscript>
results = queryExecute(
"
INSERT IGNORE INTO
ben_test
SET
id = :id,
iAmVarchar = :iAmVarchar,
iAmBit = :iAmBit,
iAmTinyInt = :iAmTinyInt,
iAmFloat = :iAmFloat,
iAmDateTime = :iAmDateTime,
iAmMediumText = :iAmMediumText
;
SELECT
*
FROM
ben_test
;
",
{
// In Lucee CFQueryParam tags, the "cf_sql_type" tag attribute has been
// deprecated in favor of the "sql_type" tag attribute. And, the actual data
// -types can be used without the "cf_sql_" prefix. Meaning, "cf_sql_varchar"
// becomes simply "varchar".
// --
// NOTE: This latter point is also true in Adobe ColdFusion 11+ (according to
// the CFDocs.org site).
id: {
value: "1",
sqlType: "integer"
},
iAmVarchar: {
value: "I am varchar",
sqlType: "varchar"
},
iAmBit: {
value: "1",
sqlType: "bit"
},
iAmTinyInt: {
value: "101",
sqlType: "tinyint"
},
iAmFloat: {
value: "3.14",
sqlType: "float",
scale: 2
},
iAmDateTime: {
value: now(),
sqlType: "timestamp"
},
iAmMediumText: {
value: "I am medium text :D",
sqlType: "longvarchar"
},
},
{
datasource: "testing",
returnType: "array"
}
);
dump( label = "Results", var = results[ 1 ] );
</cfscript>
As you can see, in Lucee CFML 5.2.3.77, I am using the sqlType property instead of deprecated cfSqlType property; and, I'm using data-type values like float and varchar instead of cf_sql_float and cf_sql_varchar, respectively. And, when we run this ColdFusion code, we get the following output:
As you can see, everything worked perfectly well!
Being able to use the cfqueryparam tag without the unnecessary cruft is a small but very welcome change in Lucee CFML. It will certainly make my code look and feel cleaner.
Want to use code from this post? Check out the license.
Reader Comments
For the params, you can even use "type" rather than "sqlType", cuts down on a few key strokes...
@Jedihomer,
Oh, really? Is that documented anywhere? Or just something you can see in the Java code itself? Just curious -- the
cf_sql_stuff isn't even documented on the Lucee site itself -- but, it is on the CFDocs.org site.Nope, not documented, I'd have read it on either slack or the forum at some point.
@Jedihomer,
I can dig it -- that's how I found out that I can remove
cf_sql_also :D :D :D Social coding :DI've updated some of the Lucee documentation here:
https://docs.lucee.org/guides/cookbooks/Sql-Types.html
and
https://docs.lucee.org/reference/functions/queryexecute.html
To reflect this.
Also, I would name the different part of the QueryExecute call, as I've done on the example on the page above, so that it is clear what each part is.
Also, do you know about Lazy Queries in Lucee?
https://docs.lucee.org/guides/cookbooks/lazy_queries.html
Micha also did a great video about queries in Lucee:
https://www.youtube.com/watch?v=IMdPM58guUQ
@Andrew,
Very cool! I am loving the Lucee community leadership -- you guys are awesome :D
I as actually just thinking about naming the query arguments the other day. I agree that it would make things a bit more clear. It would also mean I can omit
nullValue()as the second argument when I don't actually have any params to pass-through.Re: lazy queries, I have read a bit about the lazy queries, which sound interesting. I am not just not sure I have a great use-case in mind as of yet. This is because I typically consume the whole query right after it is returned (usually to map / transform it in some way, or to create an ID-based index or something). But, I am really appreciating the innovation.