I’ve been doing a lot of work with SQLite and AIR lately and I recently came across a post from Eric Feminella which has helped me tremendously.

The SQLiteadmin program can be downloaded at the end of this page: SQLite Administrator - International Milestone Beta

Instructions for installing on Windows: Unzip and run

This is currently only available for Windows so sorry to all you Mac users (including myself)

I’ll be the first to admit that I’m not the greatest when it comes to database design, optimization, even query code when it comes right down to it. Sure, I can do the standard CRUD things withough a problem but really digging into a database is not my cup of tea. So, when I came up with something actually usable, I had to post it So, on with the shameless self-promotion…

So, I am using an IF EXISTS … ELSE block for an INSERT/UPDATE query and was having an issue with the message handler. Yeah, I could have added some additional ColdFusion logic, but who wants to do that??? I thought, It’d be really great if the query would give me back the results of what action was actually taken. So, Here’s the original query:

<cfquery name="addUpdate" datasource="#application.CaseInfoDsn#">
	IF EXISTS (		SELECT 	AreaCode
					FROM 	area_codes
					WHERE 	AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.originalAreaCode#">
			  		OR		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.AreaCode#">	)
		BEGIN
			UPDATE 	area_codes
			SET		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.AreaCode)#">,
					State_Region = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.State_Region)#">,
					Description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.Description)#">
			WHERE 	AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.originalAreaCode#">
			OR		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.AreaCode#">

		END
	ELSE
		BEGIN
			INSERT
			INTO 	area_codes
					(
						AreaCode,
						State_Region,
						Description
					)
			VALUES
					(
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.AreaCode)#">,
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.State_Region)#">,
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.Description)#">
					)

		END
</cfquery>

And Here’s my addition:

<cfquery name="addUpdate" datasource="#application.CaseInfoDsn#">
	IF EXISTS (		SELECT 	AreaCode
					FROM 	area_codes
					WHERE 	AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.originalAreaCode#">
			  		OR		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.AreaCode#">	)
		BEGIN
			UPDATE 	area_codes
			SET		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.AreaCode)#">,
					State_Region = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.State_Region)#">,
					Description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.Description)#">
			WHERE 	AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.originalAreaCode#">
			OR		AreaCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.AreaCode#">

			SELECT	TOP 1 'upd' AS actionTaken
			FROM	area_codes
		END
	ELSE
		BEGIN
			INSERT
			INTO 	area_codes
					(
						AreaCode,
						State_Region,
						Description
					)
			VALUES
					(
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.AreaCode)#">,
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.State_Region)#">,
						<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.Description)#">
					)

			SELECT	TOP 1 'ins' AS actionTaken
			FROM	area_codes
		END
</cfquery>

After each action (Insert or Update) I select a random string with a random alias which I can then use for my message handler. Hopefully someone else out there thinks this is cool because I sure do