Team Ravenglass

I haven't really seen a lot of documentation on how to read an XLS file into Coldfusion. Luckily, one of my colleagues was able to point me in the right direction. I am now here to write up a blog post for future reference.

Once you set up the new datasource in the CF Administrator (see comments below), it's really simple (at least more simple than parsing through the other file formats that I've had to do far). Basically, we just read it in as a query:

[More]

This entry was posted on September 9, 2009 at 8:39 AM and has received 3201 views. There are currently 3 comments. Print this entry.

In SQL, COUNT() is an aggregate function that we can use to count the number of rows in a query. We can also use this function to figure out if there are duplicate records in a table.

[More]

This entry was posted on August 28, 2009 at 9:06 AM and has received 1288 views. There are currently 0 comments. Print this entry.

I'm always looking for ways to speed up my SQL Server queries.  Here's a tip that I used just today to speed up a search query, and I thought maybe it would be useful for all of you.

First up, make sure to include an execution plan by right-clicking on your query window and selecting "Include Actual Execution Plan"

Let's take a look at the following query:

As you can see, we have two tables, books and authors. Each of these tables are joined to show the author names for each book, and we filter by books that are published and are best sellers. This is where we'll be concentrating.

[More]

This entry was posted on August 14, 2009 at 4:00 PM and has received 1678 views. There are currently 0 comments. Print this entry.

Good afternoon everyone, today were going to talk about meta-data.  Specifically data relating to tables in SQL Server.  Have you ever wanted to grab all of the column names of a table for some operation?  Well, here is a break down of a technique that can be used to do it.

The first table that we'll be working with is the "sysobjects" table.  This table contains all objects within a database.  Objects are things such as views, tables, and stored procedures.  If you want to see a full collection of this you can query this table and see it first hand:

To filter only tables you can add an xtype of "U"

Now on to column names, we can get the column names for a specific table by joining this on the "syscolumns" table. 

There you have it, working with these metadata tables is incredible useful, good luck on using it!

This entry was posted on June 10, 2009 at 10:55 AM and has received 937 views. There are currently 1 comments. Print this entry.

 Good morning folks,

Here's a couple new tips today to make life easier in SQL.  When analyzing queries there are two tools which are great for examining resource usage.  The first is the STATISTICS IO.  You can turn this on by setting the results to text, and prepending your query with the following:

This will allow you to view the table read and read-ahead data related to the query.  Once it's on it will stay on within the query window you use it in.

Here is a simple example of how it''s used:

Whenever I use this functioanlity I use it in tandem with an execution plan.  To activate your execution plan, right click on the query window in the query analyzer, and select "Include Actual Execution Plan".  You then run your query and a new tab will appear next to the results pane called "Execution Plan".  This will show the "path" that SQL takes in deciding how a query is processed.  When you hover over icons in this window you'll find that you can see the data pertaining to each specific element of the query as well.  Keep an eye out for "CONVERT" functions and "CAST" functions in this window, as preventing these by typing your data correct can save a lot of headaches.  Additionally you can see which indexes SQL uses to grab it's data.

This entry was posted on May 21, 2009 at 10:00 AM and has received 813 views. There are currently 0 comments. Print this entry.

One thing I discovered recently was the power of the SQL CASE statement. I had to do multiple joins on multiple tables and then proceed to select data from a certain field that can actually be a different field depending on certain circumstances, as one unified field. Sounds fun, right?

One way you can figure out which field to use is to return them all and take care of the display logic on the Coldfusion side of things. The problem I had with doing this was the query I was building was probably going to be used in multiple places and I didn't want to tote that Coldfusion logic everywhere when I could just handle it in the query itself.

The first thing I learned was that apparently you cannot use SQL CASE statements to decide which tables to join on. What I have concluded you do is join all the tables you need to, then use the CASE statements to figure out which fields to select or filter on.

This leads me into why I am now a big fan of CASE statements in SQL - you now have unified data to present across the pages.

[More]

This entry was posted on April 1, 2009 at 9:14 AM and has received 4236 views. There are currently 0 comments. Print this entry.

Hello everyone!  Let's talk SQL.  My names Jonathan and today I have a useful tip for a new view on queries.  Generally when someone queries two database tables that share an identity column, they join the data and end up with a collection of rows reflecting each result, essentially creating a "combined key".  To show this example more simply, take a look at the example below:

Fig-1

This query outputs a "client sales" (See Fig-1) result set that has 7 rows.  In this example such a small result set is hardly a problems, but let us imagaine that we have hundreds of clients, each with thousands of sales under their belt.  This result set would suddenly be very daunting.  Post-processing the result in your choice web service would be a performance headache, as to output it someone would have to either do a query of a query to show which clients have which sales, or this would have to be broken into a single cleint table query, and then individual queries  in a loop on the web server end.

Wouldn't it be great if there was a way to greatly reduce the row count, the number of queries, and the amount of processing on the web server.  The solution below does exactly that. 

Fig-2

The query above casts an nodeless XML result set into a varchar field and delimits it with comma's, creating a list (See Fig-2).  The last character is removed in the main select clause otherwise the last character would be a comma.

That's it!  You'll now be able to utilize new new field (in this case sale_list) in any list functions you may have. 

This entry was posted on February 25, 2009 at 5:13 PM and has received 586 views. There are currently 0 comments. Print this entry.

Here's an issue that has come up many times in my years of working with ColdFusion: a client wants a one-off report in Excel. I created a function that does just that, taking any CFQUERY as input and outputting the results as an Excel file that can be saved or opened locally by the user. Here's the function:

Ideally you'll create your query with column names that make sense to the user. Here's an example of an invocation of the above function from a CFM page with an employee query. Our database is SQL Server, so your mileage may vary when creating user-friendly column names.

I hope this helps somebody who needs to do occasional Excel exports. Note that Excel has a limit of 65,536 rows per sheet; a nice exercise would be to adapt this tool to check the number of rows in the query and be more flexible in its output options.

This entry was posted on January 19, 2009 at 12:50 PM and has received 4437 views. There are currently 4 comments. Print this entry.