Eric Fickes

Eleven Coldfusion-ish tips from the field

Posted on 02/20/2011 by Eric Fickes

I’ve had this running list of Coldfusion tips on my wall for the last few years and it’s time to get these online. All of the items in this list came from Coldfusion projects over the last few years, but a good portion of these could easily be considered tips for server programmers. I definitely run into the same items when programming Asp.NET.

There is no rhyme or reason here, just some things I felt need to be repeated.

1. PreserveSingleQuotes()

This one came in really handy on a project requiring large text files to be imported into a MySQL database. I used Coldfusion to upload and read the files into large INSERT chunks using MySQL’s multi – row INSERT syntax. Code built the VALUES portion of the SQL, then I just fed the data into a function for insertion.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

	<cfquery name="insert_data" result="insert_result" DATASOURCE="#request.dsn#" USERNAME="#request.dbuser#" PASSWORD="#request.dbpswd#">
	INSERT INTO table
	( column1, column2, column3, column4, column5, column6, column7 )
	VALUES
	#PreserveSingleQuotes( insert_values )#
	</cfquery>

2. What if GENERATED_KEY doesn’t work?

If you’re using MySQL and the GENERATED_KEY property of your cfquery objects isn’t populating, you can use LAST_INSERT_ID instead.

SELECT LAST_INSERT_ID();

3. Use ArrayAppend when building strings

Classic performance tuning tip for just about any programming language. Here I’ll give a Coldfusion example and keep it dead simple. If you ever have to concatenate strings in code, user ArrayAppend instead. Here are two loops that do the same thing. If you run this code, you should notice loop1 takes forever, and loop2 is smoking fast.

slow…..

<cfscript>
xx				= 100000;
insertString	= "";

// do the loop
while( xx > 0 ) {
	insertString &= xx & " ";
	xx--;
}

WriteOutput( insertString);
</cfscript>

FAST!

<cfscript>
xx				= 1000000;
insertArray		= ArrayNew(1);
// do the loop
while( xx > 0 ) {
	ArrayAppend( insertArray, xx & " " );
	xx--;
}

WriteOutput( ArrayToList( insertArray, " " ) );
ArrayClear( insertArray );
</cfscript>

4. If CSV, then CHR

This one is simple, if you find yourself creating CSV or any other text file, use special characters when dealing with single and double quotes, etc.

chr(9) = Tab
chr(34) = ” double quote
chr(39) = ‘ single quote

And if you’re not sure of the correct code for the character you’re looking to use, just wrap that character in ASC() and WriteOutput to the page.

5. Use CFMail with GMail

This is a no brainer, but with how difficult sending email can be with other languages, I’m mentioning it here.

Application.cfc

<cfscript>
	APPLICATION.mail.server		= "smtp.gmail.com";
	APPLICATION.mail.port		= "465";
	APPLICATION.mail.ssl		= true;
	APPLICATION.mail.user		= "gmailAccount";
	APPLICATION.mail.pswd		= "gmailPasssword";
</cfscript>

Emailer.cfm

<cfmail to="work@ericfickes.com"
		bcc=""
		from="web@master.com"
		subject="sending mail is easy with Coldfusion"
		server="#application.mail.server#"
		useSSL="#application.mail.ssl#"
		port="#application.mail.port#"
		username="#application.mail.user#"
		password="#application.mail.pswd#">
#emailBody#
</cfmail>

6. CFSCRIPT doesn’t know NULL?

Another tip from the land of importing and exporting data. While working with query objects in CFScript, for some reason I could never accurately detect for NULL values. I tried all sorts of detection schemes and ended up just writing a hacky fail safe. Please, if you have a better suggestion for *easy* NULL detection in CFSCript, add it in the comments below.

utils.cfc

<!---  Simple value getter with try / catch to get around NULL values
This function originated in a script where we always needed a " " even if
the value from the database was null.
--->
<cffunction name="qryGetString" access="public" returntype="string">

<cfargument name="data" type="string">

<cftry>
	<cfscript>
	return #data# & " ";
	</cfscript>

	<cfcatch type="Any">
		<cfreturn " "/>
	</cfcatch>
</cftry>

</cffunction>

Exporter.cfm

// largeish loop
tab = chr(9);
for( xx = 1; xx <= queryObj.RecordCount; xx++ )
{
	// start the row
	this_row = 	utils_cfc.qryGetString( queryObj.FirstName[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.MiddleName[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.LastName[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.Suffix[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.MedicalTitle[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.email[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_phone[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_fax[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_name[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_address1[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_address2[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_city[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_state[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.practice_zipcode[xx] ) & tab &
			utils_cfc.qryGetString( queryObj.hospital_affiliation[xx] ) & tab;

	// do stuff with the data
}

7. How I find list items

Ever notice the different behavior in the Coldfusion ListFind commands? I ended up writing my own ListHasValue function in order to find exact pattern matching in a list. I had a list of role ids in a list, and just couldn’t get the built in functions to tell me when my id was in the list without also matching on other ids. This one makes sense when you run some code.

The top of this sample as my custom ListHasValue() command, and the lower half does three simple loops counting from 1 to 100, and using ListFind, ListContains, and ListHasValue for number checking against the same list.

<!---
	Use this to do an exact pattern check for a value in a list.
	This is useful inside of a loop checking numbers against a number list.

	EX :

	list = "91, 92"

	if you loop from 1 - 100, ListFind and related CF functions will match
	on 1, 2, and 9.  Not just 91 and 92

	Use this when you're looping and you ONLY want to match on 91, or 92

--->
<cffunction name="ListHasValue" access="public" returntype="boolean">

	<cfargument name="list" required="yes" type="string">
	<cfargument name="value" required="yes" type="any">

	<cfscript>
		// clean up to be safe
		list = trim( toString( list ) );

		// check to see if we have a *possible* match
		position = ListContains( list, value ) ;

		if( position > 0 )
		{
			// NOTE : KEEP THE TRIM AND TOSTRING
			found_value = trim( toString( ListGetAt( list, position ) ) );

			if( Compare( value, found_value ) == 0 )
			{
				return true;
			}
		}

		// no match for you!
		return false;
	</cfscript>

</cffunction>

<cfscript>
list	= "1, 11, 21, 31, 41, 51, 61, 71, 81, 91, 99";
xx		= 1;

WriteOutput( "List = " & list & "<hr>");

while( xx < 100 ) {
	if( ListContains( list, xx ) > 0 )
	{
		WriteOutput( "ListContains found " & xx & "<br>" );
	}
    xx++;
}

WriteOutput("<hr />");

xx = 1;
while( xx < 100 ) {
	// ListFind
	if( ListFind( list, xx ) > 0 )
	{
		WriteOutput( "ListFind found " & xx & "<br>" );
	}
    xx++;
}

WriteOutput("<hr />");

xx = 1;
while( xx < 100 ) {
    // Eric's ListHasValue
	if( ListHasValue( list, xx ) )
	{
		WriteOutput( "ListHasValue found " & xx & "<br>" );
	}
	xx++;
}
</cfscript>

If you run this code on your Coldfusion server, you should notice the following results. ListContains matches single digits from the loop that do not really exist in the list. ListFind only finds the number 1? And finally, my function does exactly what I needed it to do. Tell me when a specific number exists in a list.

Coldfusion ListFind functions don't always behave how I want them to

8. Make PDFs faster

This could easily be it’s own topic, but I’ll say one thing about making PDFs faster with CFDocument. Only put final content between <cfdocument> and </cfdocument>. That is, if you have any processing code, cfqueries, cfloops, inside of your cfdocument tag, your cfml page is running slower than it needs to be. Here’s a simple example of one of my cfml pages that has only final content in the cfdocument tags.

The key to this example is moving all of my content creation code into an external file, then including at the top of my page. I always do a check for my main PDF_BODY variable, and then spit out my PDF document.

<cfinclude template="code/export_pdf_codefile.cfm">

<cfif PDF_BODY NEQ "">

    <cfdocument	name="provider_profile"
                format="PDF"
                pagetype="A4"
                mimetype="application/pdf"
                orientation="portrait"
                margintop="0"
                marginbottom="0.2"
                marginleft="0.2"
                marginright="0.2"
                >
        <cfoutput>#PDF_BODY#</cfoutput>
    </cfdocument>

    <!--- send directly to client --->
    <cfheader name="Content-Disposition" value="attachment; filename=#filename#">
    <cfcontent type="application/pdf" variable="#provider_profile#">

<cfelse>
	No PDF content found
</cfif>

9. Use parameterized queries

This is a tip for all server side programmers whether you use Coldfusion, ASP, JSP, PBJ. Use parameterized queries when doing any database interaction. It’s too easy not to use, and you get protection from SQL Injection, as well as enforcing proper data types when speaking to your database. This is something all server programmers should do regardless of your language, the sample below is for Coldfusion.

BAD

<cfquery name="tblInsert" datasource="myDb">
INSERT INTO	myTable
( col1, col2, col3, col4 )
VALUES
( '#Form.field1#', '#Form.field2#', '#Form.field3#' )
</cfquery>

GOOD

<cfset val1 = Form.field1>
<cfset val2 = Form.field1>
<cfset val3 = Form.field1>

<cfquery name="tblInsert" datasource="myDb">
INSERT INTO	myTable
( col1, col2, col3, col4 )
VALUES
(
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#val1#" />,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#val2#" />,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#val3#" />
)
</cfquery>

10. Where’d the time go?

For the good programmers already using parameterized queries, ever insert a timestamp into your database and find out the date is correct, but the time is always 12:00:00? Take a closer look at the cfsqltype in your cfqueryparam, I had this exact problem and here’s what happened.

Using cf_sql_date does not include the full date and timestamp, just the date.

<cfqueryparam cfsqltype="cf_sql_date" value="#paymentDate#" />

Using cf_sql_timestamp includes the full date and timestamp I was looking for.

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#paymentDate#" />

11. Stored Procedures are a little different

This last one isn’t much of a tip, but more of a reminder to myself. I do so much database work that stored procedures are just queries to me, but not so to Coldfusion and the CFQuery tag. If you want to get data from a stored procedure, you need to use the CFStoredproc tag. Here’s a sample of passing one argument into a stored procedure, and how to get the resulting data.

<cfstoredproc	datasource="myDb" procedure="GetDataFaster">

	<cfprocparam type="in" cfsqltype="cf_sql_integer" value="#inputVar#" />

	<!--- specify sproc result here, cfstoredproc res != returned recordset --->
	<cfprocresult name = sprocResult>

</cfstoredproc>

<cfreturn #sprocResult.ColumnFromQuery#>

Posted in adobe, coldfusion, database, tips and tricks | Tagged asc, cfml, cfscript, chr, coldfusion, list, listcontains, listfind, listhasvalue, mysql, parameterized query, special character, sproc, SQL, stored procedure | 10 Comments

Injecting javascript into asp.net via code

Posted on 02/18/2011 by Eric Fickes

Microsoft has a great MSDN article on using javascript along asp.net, but they didn’t mention a technique I like to use, put it in a Literal control. While there are many ways to add javascript to a page, I find putting the javascript in a literal much less stressful. Using a Literal control placeholder is also a good way to add messaging to a page after postback, but we’re just going to look at adding javascript.

Let’s take a simple example. Say you’ve got a comment form that you want to auto close, or reload after the form was posted. Below is a simple single file style asp.net page with a simple javascript function that reloads this page.

<%@ Page Language="C#" %>

<script runat="server">
/// <summary>
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {

    }
}

///////////////////////////////////////////////////////////////////////////////
/// Do stuff with the form data, then refresh page using javascript
protected void submitComments(object sender, EventArgs e)
{

    try
    {
	//
	// do stuff here
	//

	// set javascript timer to reload page afer 3 seconds
	js_target.Text = "setTimeout('reload()', 3000);";

    }
    catch (Exception exc)
    {
        Response.Write( "ERROR : " + exc.Message );
    }
}
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
	<title>Comments</title>
	<script type="text/javascript">
	// page reload helper
	function reload() {
		document.location.replace( document.location );
	}
	</script>
</head>
<body>

<form id="form1" runat="server" method="post">

<script type="text/javascript"><asp:Literal runat="server" id="js_target" /></script>

	Comments
	<asp:TextBox runat="server" ID="comment_box" Width="200" />
	<br><br>

	Your name
	<asp:TextBox runat="server" ID="fullname" Width="200" />
	<br><br>
	<asp:Button runat="server" ID="submit_btn" onclick="submitComment" Text="submit" />

</form>
</body>
</html>

If you look just under the form tag you’ll see the key to this technique, an asp literal wrapped by an open and close script tag.

<script type="text/javascript"><asp:Literal runat="server" id="js_target" /></script>

When you load your page and view the source you’ll just see an empty script tag, so it shouldn’t interfere with the execution or rendering of your page.

The last part of this technique is simple, in your server code just set your Literal control’s .Text value to your javascript code. In this case when I post my comment form, after handling the input data I display a thank you message, then set some javascript to reload the page.

ltl_js.Text = "setTimeout('reload()', 3000);";

That’s all there is to it. Drop a literal in an empty script block and BAM!, you have an easy way to add javascript to your asp.net page.

Posted in .net, C#, development, microsoft, tips and tricks | Tagged asp.net, aspx, javascript, js, Literal, msdn, postback | 2 Comments

Today I took 30 minutes to save hours

Posted on 01/03/2011 by Eric Fickes

UPDATE – 1/6/2011 : Since writing this post I have bundled this entire reset process into a single windows batch file. In addition to restoring my database, I also have to remove temp files from my server, so it was time to go command line with all of this.

Here the contents of my batch file.

### DELETE TEMP FILES
del /q /f /s Z:\_FavoriteClient\_GIT_\webapp\invoices\* /f /s

### RESTORE DATABASE
sqlcmd -S "localhost" -i Z:\_FavoriteClient\_GIT_\sql\restoreBackup.sql

To use this in a .bat file, copy and paste the text into Notepad, then save as “restore.bat”. Remember, you will need to wrap your filename.bat with quotes, otherwise Notepad will save the file as filename.bat.txt.

* The contents of restoreBackup.sql is listed below.

One of my favorite pastimes as a programmer is writing code that writes code, aka workflow automation. It’s not something I do on every project, but I’ve been doing a lot of SQL Server development recently, and I’ve been having a lot of fun using the INFORMATION_SCHEMA views to build VO classes, forms representing tables, or just finding what tables have a specific column. Today I took a 30 minute trip to the SYS side and wrote a tsql script that will save me hours.

The web application I’m working on is a payment processor made up of five steps. In order to test I have to setup multiple sales for multiple clients, then log in as three different admin users to push the transactions through the system. This gets my test transactions to the proper testable state. The process of setting up testable transactions takes over 30 clicks, and once I hit step 3 of the wizard, my test transactions are completed in a way that I have to re-setup the test data ( 20 GOTO 10 ).

I thought about a handful of options, and ended up going with this solution.

Setup all test transactions in web application by hand ( get the data ready )
Take a full database backup ( freeze the data )
Use TSQL script to drop all connections to my app’s db, then restore the database to the “testable” state from step 1 ( reset the DB )

Here is my TSQL script

DECLARE @sessID int,
		@dbName varchar(50),
		@userName varchar(50),
		@backupFile varchar(200)

SET @dbName		= 'DA413'	-- your database name
SET @userName	= 'DA413'	-- sql user account to look for
SET @backupFile = 'D:\DB\Backup\DA413.bak' -- path to SQL backup file

-- use a cursor to store all session_ids
DECLARE session_cursor CURSOR
FOR
	SELECT	session_id
	FROM	sys.dm_exec_sessions
	WHERE	original_login_name = @userName

	-- open cursor and grab first row
	OPEN session_cursor
	FETCH NEXT FROM session_cursor INTO @sessID

	-- loop through session_ids
	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- kill it
		-- using EXEC because the sproc kill does not like @variables
		EXEC('kill ' + @sessID)

		-- get the next session_id
		FETCH NEXT FROM session_cursor INTO @sessID
	END

-- cursor cleanup
CLOSE session_cursor
DEALLOCATE session_cursor

-- restore backup
USE master
	RESTORE DATABASE DA413
	FROM DISK = @backupFile
GO

A few notes about this script :

I only need to disconnect my web application’s database user, not any user
I’m using the latest full backup, and not a specific database snapshot
The SPROC kill doesn’t like @variables as input, so use EXEC
Your web server doesn’t know the user was disconnected, so you’ll have to log back into your application.
If you use this technique, be sure to RERUN your backup if you add anything to the database ( EX : new table column, stored procedure, etc )
If you’re not the only person connected to this database, make sure you don’t disconnect anybody else using the same database name

This solution is perfect for me because I have full control over my code, database, and server. It’s also great because I can test my application, run a single sql script, and 10 seconds later I can test my application again. While this solution is perfect for me, it’s probably best used as reference for others. However, this technique of rolling back the database could be applied to any software application using SQL Server for it’s datasource.

Hope this helps somebody.

Posted in database, development, SQL, tips and tricks | Tagged automation, backup, bat, batch, batch file, cli, cmd, command line, cursor, DECLARE, del, exec, fetch, fetch_status, information_schema, kill, restore, sproc, SQL, sqlcmd, sys, tsql, workflow | Leave a comment

Use sys.dm_exec_sessions to disconnect SQL user connections

Posted on 01/03/2011 by Eric Fickes

-- init vars
DECLARE @sessID int,
		@dbName varchar(50),
		@userName varchar(50)
SET @dbName	= 'DA413'	-- your database name
SET @userName	= 'DA413'	-- sql user account to look for
-- use a cursor to store all session_ids
DECLARE session_cursor CURSOR
FOR
	SELECT	session_id
	FROM	sys.dm_exec_sessions
	WHERE	original_login_name = @userName
	-- open cursor and grab first row
	OPEN session_cursor
	FETCH NEXT FROM session_cursor INTO @sessID
	-- loop through session_ids
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- kill it
		-- using EXEC because the sproc kill does not like @variables
		EXEC('kill ' + @sessID)
		-- get the next session_id
		FETCH NEXT FROM session_cursor INTO @sessID
	END
-- cursor cleanup
CLOSE session_cursor
DEALLOCATE session_cursor

Permalink

| Leave a comment

Posted in database, internets, microsoft, SQL, tsql | Leave a comment

Injecting javascript into asp.net via code

Today I took 30 minutes to save hours

Use sys.dm_exec_sessions to disconnect SQL user connections

My Work

My Blogs

Hotlinks

File Under

Eleven Coldfusion-ish tips from the field

1. PreserveSingleQuotes()

2. What if GENERATED_KEY doesn’t work?

3. Use ArrayAppend when building strings

4. If CSV, then CHR

5. Use CFMail with GMail

6. CFSCRIPT doesn’t know NULL?

7. How I find list items

8. Make PDFs faster

9. Use parameterized queries

10. Where’d the time go?

11. Stored Procedures are a little different

Injecting javascript into asp.net via code

Today I took 30 minutes to save hours

Use sys.dm_exec_sessions to disconnect SQL user connections

My Work

My Blogs

Hotlinks

File Under

My Cloud