Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Matthew Williams
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Matthew Williams@ophbalance )

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

By Ben Nadel on
Tags: ColdFusion

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.



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

Reply to this Comment

@Jedihomer,

I can dig it -- that's how I found out that I can remove cf_sql_ also :D :D :D Social coding :D

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.