Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Adam Tuttle
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Adam Tuttle

The CFQueryParam Tag Does Not Require The "CF_SQL_" Prefix On SQL Types In Lucee 5.3.2.77

By
Published in Comments (6)

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:

The cfqueryparam tags works without the cf_sql_ prefix on data-types in Lucee CFML 5.3.2.77.

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

15,811 Comments

@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.

3 Comments

I'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

15,811 Comments

@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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel