I’m by no means an expert using SQL, but as of yet I’ve found no way to delete data from multiple tables without writing a single query specifically for each table that data needs deleted from. So if you’re reading this expecting a native SQL statement that will delete data from multiple tables then I have nothing to offer. However, I do have a solution. One which requires a programming language, Coldfusion in my case.

The idea is that we have a number of tables.  Lets say we have five tables holding data about products.  So we’ll have tables named ‘products_tbl’, ‘products_name_tbl’, ‘products_description_tbl’.  Using just SQL we would need to write three delete statements like:

Straight SQL

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_tbl
	WHERE product_id = 9
</cfquery>

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_name_tbl
	WHERE product_id = 9
</cfquery>

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_description_tbl
	WHERE product_id = 9
</cfquery>

Now let’s say that halfway through the project the client suddenly wants the price to be included. Really not a big deal, but when we now go to delete the product we realize that we need to go find our component or whatever that is handling the deleting transactions and add a new delete statement.

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_price_tbl
	WHERE product_id = 9
</cfquery>

I personally hate this, and like most things the more complicated the data becomes the more burdensome it gets on the developer. So what’s the solution?

The idea is that we need to bundle all of these related sets of data into a group. When I say related I simply mean that in this case all the tables containing the parts and pieces of a product. The product is an object, and when that object is deleted we need to visit each of the tables containing information for that product and get rid of it. If we could simply group each of the table names we could run a loop with a delete statement inside where the table name dynamically changes with each loop. This would allow us to update our group and not have to worry about updating any actual SQL.

In my case, I’ve created the group under the APPLICATION scope. This let’s me access the group from anywhere within my application. As such my Application file would contain inside the onApplicationStart() method, for this case, the following.

<cfparam name="APPLICATION.producttables.productTbl" type="string" default="product_tbl">
<cfparam name="APPLICATION.producttables.productNameTbl" type="string" default="product_name_tbl">
<cfparam name="APPLICATION.producttables.productDescriptionTbl" type="string" default="product_description_tbl">

Now that we have our table names stored in the APPLICATION scope under a group called ‘producttables’ we can build a function that will get all the data in the ‘producttables’ group and set it into an array. We can then loop over this array of table names deleting from each the product data associated with the specified product id. In this case our function would look like.

<cfcomponent displayname="Looping Delete" hint="Loops over a portion of a struct with table names and deletes from those tables." output="false">

	<cffunction name="deleteProduct" displayname="Delete Product" description="Deletes a product." access="public" output="false" returntype="void">

		<!--- PRODUCT ID --->
		<cfargument name="productid" displayname="Product ID" hint="Id of the product to be deleted." type="numeric" required="true" />

		<!--- CREATE AN ARRAY OF TABLE NAMES STORED IN APPLICATION SCOPE --->
		<cfset var tableArray = structkeyarray(APPLICATION.producttables)>

		<!--- LOOP OVER THE ARRAY --->
		<cfloop array="#tblArray#" index="VARIABLES.i">
			<cfquery name="deleteproduct" datasource="#APPLICATION.dataSource#">
				DELETE
				FROM schema_name.#APPLICATION.producttables[VARIABLES.i]#
				WHERE product_id = <cfqueryparam value="#ARGUMENTS.productid#" cfsqltype="cf_sql_integer" maxlength="10">
			</cfquery>
		</cfloop>

	</cffunction>

</cfcomponent>

So what we’ve done here is made it much easier to add data to the product without ever having to touch our component. Instead all we really need to do is add the data to the APPLICATION scope to the producttables group. So for instance now if we need to add a price to our product all we have to do is go into the Application.cfc file and set

<cfparam name="APPLICATION.producttables.productPriceTbl" type="string" default="product_price_tbl">

That’s all that we need now to make the update. The loop will now find this table in our group and delete from it the same as the others.

Quick post on how to set up a datasource in the Coldfusion Administrator. First you’ll want to open the Coldfusion Administrator. I’m going to assume that you will be doing this locally since if you don’t have a datasource you’ve probably are just at the beginning stages of development. As such let’s open the admin.

Working locally it is likely that you will use the following link http://localhost:8500/CFIDE/administrator/index.cfm for Coldfusion 8 and http://127.0.0.1:8500/CFIDE/administrator/index.cfm for Coldfusion 9. Remember though if you have multiple versions of the Coldfusion server running on your machine the port number may not be 8500. Instead as ports are used Coldfusion will increment the port number by 1 until reaching the next available port. So for example if you had Coldfusion 8 running, and are now running Coldfusion 9 as well your port number is most likely 8501. Nonetheless, if this is the case, my guess is that your not reading this article.

Once into the administrator click on ‘Data Sources’ under the ‘Data & Services’ tab on the left.

Data Sources menu in Coldfusion 9 Administrator

Next create a name for your datasource.  This can be whatever you want it to be.  This is the label that will represent the connection setup between Coldfusion and this particular database.  You will also need to select a driver from the drop-down.  In my case I’m using MySql 5 so I’ll choose ‘MySql 4/5′ from the drop-down.  You will choose whatever is appropriate for your particular database.

(more…)

The majority of my clients are convinced that their Excel spreadsheets are database tables. I’ve found that it’s easier to just give them the ability to move their data into a spreadsheet file they can save to their desktop to work with the way they know how, than to explain how their data is being stored.  This is fine and understandable, and I also find that I don’t need to spend a lot of time formatting and preparing the data for Excel since they are usually really good with the program.  Long story short, all I really need to do is get the data they want into spreadsheet form and let them do the rest.  I’ve found that this seems to be both what I want and what they want.  While there are more complex and better ways to do this, I’m going to take a look at the simplest way I know how to take data from a record set and save it in spreadsheet format to a .xls file for download by the client.

First we need a query to work with. I’ve run this in a method in a component, but will assume that you will be working with an actual query in a database.  Let’s look at the query.

Generate Emails

<cfcomponent displayname="Generate Email Query" hint="Generates a query of emails." output="false">

	<!--- ********************************  EMAIL QUERY  ******************************** --->
	<cffunction name="emailquery" displayname="Email Query" description="Creates a query of emails." access="public" output="false" returntype="query">

		<!--- CREATE LIST OF EMAILS --->
		<cfset var emaillist = "matt@somecompany.com, adri@somecompany.com, sancho@somecompany.com, weewee@somecompany.com">

		<!--- CREATE QUERY --->
		<cfset var emailquery = querynew("email_id, email, customer", "Integer, VarChar, VarChar")>

		<!--- CREATE COUNTER TO KEEP UP WITH NUMBER OF LOOPS --->
		<cfset var counter = 1>

		<!--- LOOP OVER LIST --->
		<cfloop list="#emaillist#" index="VARIABLES.i">
			<!--- CREATE NEW RECORD IN QUERY --->
			<cfset queryaddrow(emailquery, 1)>

			<!--- SET DATA INTO QUERY --->
			<cfset querysetcell(emailquery, "email_id", counter)>
			<cfset querysetcell(emailquery, "email", VARIABLES.i)>
			<cfset querysetcell(emailquery, "customer", "Yes")>

			<!--- INCREASE COUNTER BY 1 --->
			<cfset counter = counter + 1>
		</cfloop>

		<!--- RETURN THE QUERY --->
		<cfreturn emailquery>

	</cffunction>

</cfcomponent>

So the query returned looks like

List of emails.

Now we can look at the .cfm file that will create the .xls file for download.  The code looks like the following.

Create .xls File

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

<!--- MAKE SURE WE ONLY OUTPUT DATA IN CFOUTPUT TAGS --->
<cfsetting enablecfoutputonly="true">

<!--- GET A QUERY OF EMAILS --->
<cfinvoke 	component="path.to.generate_emails"
			method="emailquery"
			returnVariable="generate_emails" >
</cfinvoke>

<!--- SET SOME DELIMITERS --->
<cfset VARIABLES.tab = chr(9)>
<cfset VARIABLES.semicolon = chr(59)>
<cfset VARIABLES.comma = chr(44)>
<cfset VARIABLES.verticalbar = chr(124)>

<!--- SET NEW LINE --->
<cfset VARIABLES.line = chr(13) & chr(10)>

<!--- SET HEADER WITH FILE NAME FOR INTERNET EXPLORER --->
<cfheader name="Content-Disposition" value="filename=emails.xls">

<!--- SET THE MIME TYPE WE ARE CREATING --->
<cfcontent type="application/msexcel">

<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<title>Untitled Document</title>
	</head>

	<body>
		<!--- SET COLUMN HEADERS IN SPREADSHEET --->
		<cfoutput>EMAIL ID#VARIABLES.tab#EMAIL#VARIABLES.tab#CUSTOMER#VARIABLES.line#</cfoutput>

		<!--- LOOP OVER QUERY --->
		<cfloop query="generate_emails">
			<!--- OUTPUT EACH ROW OF DATA --->
			<cfoutput>#trim(email_id)##VARIABLES.tab##trim(email)##VARIABLES.tab##trim(customer)##VARIABLES.line#</cfoutput>
		</cfloop>
	</body>
</html>

Several things need to be set before doing any data output.  First we need to make sure that we only output data that is in <cfoutput> tags.  Without setting this to true we end up outputting some html, even if you can’t see it, that Excel cannot read.  As a result your file will be opened as something like a plain text file.  So make sure that enablecfoutputonly is set to true.  Next we get our query of emails, this is the method that is shown above.  Then we need to set at least one delimiter for our data.  I’ve set four different delimiters here for as examples, but you really only need one.  Next make sure we have the equivalent of a line break which is just a carriage return and line break.  And lastly make sure that the user downloads the data written to the <body> tag as an excel file.  The <cfcontent type=”application/msexcel”> works for all browsers except Internet Explorer (surprise, surprise!).  As such, we can add a header with a value equal to the name of the .xls file the data will be saved to for the user to download.

Now that we have all the file making out of the way, we only need to create the content of the file itself.  Excel uses delimiters to understand the difference between data in a cell, and cells in a row.  For example, when Excel reads a line break it understands that as to mean create a new row.  This is why we’ve set a var to indicate a line break called ‘VARIABLES.line’.  Cell data separation is much more flexible in Excel in that any delimiter can be used to indicate when the data of one cell ends and the data for the next cell begins.  The most common delimiter is the ‘tab’ character, but really any can be used.  To illustrate this I’ve set four different delimiters, being a ‘tab’, a ’semi-colon’ (;), a ‘comma’ (,), and a ‘vertical bar’ (|).

First we want to create some column titles in our spreadsheet.  The first cell should say ‘EMAIL ID’, then a delimiter to indicate a new cell (here a ‘tab’), then ‘EMAIL’, a delimiter to indicate a new cell, and ‘CUSTOMER’, then a new line to indicate that this is the end of this row and we want to create a new row.  Now we need to do the same thing but with the actual data returned from the query.  We’ll want to loop over our query and run the email id, a delimiter, the email, a delimiter, and if a customer, and a new row, for each record in the record set.

When we run this in the browser it realizes that the file cannot be opened within the browser so prompts you to either save the file or open it with the application of your choice.  Save the file.  When you open the file you will be prompted with a question of how the data should be separated.  I’m running OpenOffice on my computer (and you should be too!), but excel looks much the same as the screen shot below.

Prompt for table delimiter using OpenOffice

So notice above, the other options as delimiters.  If I were to use the comma or semi-colon I would simply check those boxes.  This will tell excel how to read the data.  If I were to use the vertical bar or pipe I would check the other box and insert a pipe.  For example,

Using a pipe as a delimter to read data in a spreadsheet.

So this is a pretty easy way to take complex related data in a database, query it, write it to a file, and open as a spreadsheet.

It’s hard to sum this subject up in a title so let me explain a little more.  Let’s say you have two tables in a database.  One is named ‘product_prices_tbl’ and the other is called ‘product_quantities_tbl’.  In both tables you have a key called product_id that represents the individual products associated with each price and quantity.  So what we want is to get the price and quantity of the product that has an id of ‘4′, but we only want to write one query that will run twice – first for the prices table and second for the quantities table.  First let’s build some queries.

I’ve created a CFC to do the work for me.  It has one main public function called ‘getData’ and two private methods called by the getData() method called ‘getPrices’ and ‘getQuantities’.  The two private methods simply create their respective query and return it to the getData() method to be worked with.  So just for reference I’ll post the two private methods and what they return.

getPrices() Private Method

<!--- ********************************  CREATE PRICES QUERY  ******************************** --->
<cffunction name="getPrices" displayname="Get Prices" description="Creates a query of product prices." access="private" output="false" returntype="query">

	<!--- CREATE A QUERY --->
	<cfset var product_price_tbl = querynew("product_id, product_price")>

	<!--- LOOP TO SET DATA INTO QUERY --->
	<cfloop from="1" to="10" index="i">
		<!--- CREATE A NEW ROW TO BOTH TABLES --->
		<cfset queryaddrow(product_price_tbl, 1)>

		<!--- SELECT A NUMBER BETWEEN 1 AND 100 TO BE PRICE --->
		<cfset myprice = randrange(1, 100)>

		<!--- SET OUR ID EQUAL TO THE INDEX AND THE PRICE EQUAL TO OUR NUMBER BETWEEN 1 AND 100 --->
		<cfset querysetcell(product_price_tbl, "product_id", i)>
		<cfset querysetcell(product_price_tbl, "product_price", decimalformat(myprice))>
	</cfloop>

	<!--- RETURN QUANTITIES QUERY --->
	<cfreturn product_price_tbl>

</cffunction>

getQuantities() Private Method

<!--- ********************************  CREATE QUANTITIES QUERY  ******************************** --->
<cffunction name="getQuantities" displayname="Get Quantities" description="Creates a query of product quantities." access="private" output="false" returntype="query">

	<!--- CREATE A QUERY --->
	<cfset var product_quantity_tbl = querynew("product_id, product_quantity")>

	<!--- LOOP TO SET DATA INTO QUERY --->
	<cfloop from="1" to="10" index="i">
		<!--- CREATE A NEW ROW TO BOTH TABLES --->
		<cfset queryaddrow(product_quantity_tbl, 1)>

		<!--- SELECT A NUMBER BETWEEN 1 AND 5 TO BE QUANTITY --->
		<cfset myquantity = randrange(1, 5)>

		<!--- SET OUR ID EQUAL TO THE INDEX AND THE QUANTITY EQUAL TO OUR NUMBER BETWEEN 1 AND 5 --->
		<cfset querysetcell(product_quantity_tbl, "product_id", i)>
		<cfset querysetcell(product_quantity_tbl, "product_quantity", myquantity)>
	</cfloop>

	<!--- RETURN QUANTITIES QUERY --->
	<cfreturn product_quantity_tbl>

</cffunction>

(more…)