Category: SQL
qBrowser - new CF open-source project
Posted on Nov 29, 2005
Ryan Guill just released a new open-source project he calls qBrowser. It allows you to test queries against a ColdFusion datasource in your browser. It will also code highlight your executed queries. It can save both a library and a history of your executed queries. It's still in its early stages, but can be a useful tool. Personally, I test my MS SQL queries using SQL Query Analyzer and think it is an excellent tool, but I have coworkers and friends who rarely touch it (no idea why) for whom this will be useful. Also, it works against any CF datasource, not just MS SQL, so it will work on datasources that don't have a tool like Query Analyzer. Finally, all it takes to install it is to unzip the files to your web root and browse...quick and easy. Good work Ryan, I am looking forward to seeing the project progress! (oh, and I added it to the CF open source list)
Handling Unique IDs
Posted on Oct 21, 2005
Recently, in my spare time I have been working with Doug Hughes' Reactor framework. It is still in an early beta release, but so far I am very excited by the project. Something about the way it works suits my programming style and feels comfortable for me. I have been having alot of discussion with Doug via his blog about handling certain issues that have arisen, the most recent over how to handle calling insert or update for the record. The question seems simple but in part comes down to differing ways of handling creating unique IDs for records, and, perhaps, how my means of handling this may differ from common practice (I am not sure to be honest if this is the case or not - which is the reason for my post)
Creating an Offset in MS SQL
Posted on Oct 14, 2005
Sorry for the lack of posts recently, but I have been stuck in the land of copy/paste internationalization (which is both a bad way to do things and pure torture to implement...but more on that later). Anyway, I was modifying Barney Boisvert's TreeManager CFC for a side project I still maintain. The component was written for MySQL but for the most part the only MySQL specific code appears to be the use of LIMIT instead of TOP. However, in one case (sortChildren()) he uses LIMIT with the following syntax: LIMIT 5,1. Well, in case you aren't aware (I wasn't), the first item in the list is an offset (i.e. it will get the top 1 after the first 5). Turns out that there does not appear to be a MS SQL equivalent to this (Pete Freitag had discussed this and asked about a MS SQL equivalent but I did not see an answer). However, the following query appears to work (it was tested against the categories table in Northwind):
FROM categories
WHERE categoryid NOT IN (SELECT TOP 4 categoryid FROM categories)
Using that, you could change lines 447-451 of treemanager.cfc to:
FROM #table#
WHERE #variables.id# IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#getChildIds(parentID)#" list="true" />)
AND id NOT IN (SELECT TOP #i - 1# #variables.id# FROM #table#)
ORDER BY #lpos#
I am in the early stages, so I haven't tested that yet, but it should work. If anyone knows a better method to acheive this, please let me know. Once I get all the code MS SQL compatible in treemanager.cfc, I hope to send it off to Barney so he can make it available.
When to Use SQL Stored Procedures?
Posted on Jun 17, 2005
This seems to keep coming up in my work and discussions and I wanted to lay out my opinion on the topic and let others chime in as well. While I am a firm believer in the benefits of stored procedures, there seems to be a cadre of people who feel that all queries can benefit from being encapsulated in a stored procedure...that somehow performance is always better when using a stored procedure...that stored procedures are rock and inline queries are scissors. This, IMHO, is simply not true.
There has to be a better way!?
Posted on May 13, 2005
Sorry for the lack of posts recently. I am working on a number of items which I hope to post more about soon (g11n in particular)...but for the moment I want to share an interesting problem. I think maybe I am being blind here and ended up making this query more complex than it has to be (and I am hoping someone can straighten me out - or maybe tell me this is correct).
The problem is with a query that I am rebuilding (the original was not mine). For arguments sake let's say this, the pages that call this query send a list of brands for a product. Brands and products have a many to many relationship, and what we need to know is which products are in all of the brands on the list.