(This is a re-post from my work blog, but I thought I'd post it here for everyone else.)
ANT has a SQL task which allows you to execute SQL statements via JDBC which can be very handy in a number of projects. Currently I'm using this task to execute a directory of generated sql files, each of which defines a stored procedure for MS SQL Server. Here is what a typical one of these stored procdure SQL files looks like:
The new beta for Firebug was released over the weekend so I'm happy to say I'm releasing ColdFire 1.2 into the wild. Head over to the RIA Forge site to check it out.
So here is something I ran into recently. Take a look at the following queries:
<cfquery name="insertNow1" datasource="scratch">
INSERT INTO TestTable
(
TestText
,TestDateTime
)
VALUES
(
'Test without queryparam'
,#Now()#
)
</cfquery>
<cfquery name="insertNow2" datasource="scratch">
INSERT INTO TestTable
(
TestText
,TestDateTime
)
VALUES
(
<cfqueryparam value="Test with queryparam" cfsqltype="cf_sql_varchar">
,<cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp">
)
</cfquery>
You may expect the above two queries to give you the same results, but they don't. If you don't use cfqueryparam the datetime value is only stored to the second precision.
ID TestText TestDateTime ------ ---------------------------------- ---------------------------- 1 Test without queryparam 2008-02-12 15:31:51.000 2 Test with queryparam 2008-02-12 15:31:51.717
This is because the string representation of a coldfusion.runtime.OleDateTime object is only precise to the second. (Try dumping Now() and you will see you get a time stamp similar to {ts '2008-02-12 15:31:51'}. Notice no milliseconds.) This is just something to keep in mind, especially if your application depends on timestamps.
I ran into something today that I've never had to do before: I needed to select rows from a SQL Server database table where a column had a newline character (CR+LF). After a little searching I found one approach.
SELECT *
FROM Table
WHERE PATINDEX('%'+CHAR(13)+CHAR(10)+'%',Column) > 0
I hadn't used SQL Server's PATINDEX function before, but it looks like it could be pretty useful. If anyone has another approach please let me know.
This is something that drives me crazy. The other day I needed to debug a problem report so I asked the developer to send me SQL he was using to generate the report. The SQL looked something like this:
In MS SQL Query Analyzer for SQL 2000 you can search for just about any database object using the Object Search. To access Object Search just press the F4 key while in Query Analyzer. Since my co-worker turned me on to this feature I've found myself using it quite a bit. It is great for finding column and table names in large databases as it allows you to do wildcard searches. Say for example you can't remember if a column is named "LastLogIn" or "LastLoggedIn" and say you also can't remember which table it is stored in. Object Search to the rescue. Just select the "Column" object type and search for "LastLog%" and you will get a list of all columns that start with LastLog, as well as what tables they are stored in.
Here is another update for the CF Debug Extension to the SQL Explorer Plugin. The extension wasn't properly handling yes/no true/false values for cf_sql_bit queryparams. This update fixes the issue.
To get the latest version of the extension click the download link at the bottom of this entry, between the send and del.icio.us links. (You may need to right click and "Save Link As...")
Well, I found out some other people are using my CF Debug SQL Explorer Extension so I'm releasing this update. In my original extension I forgot to escape single quotes, so this version fixes that. (I noticed I forgot to handle this while looking through Ben Nadel's code for a bookmarklet which does something similar to this extension.) This release also includes the source if anyone is interested, just look in the .jar. Installation instructions are still the same and you can find those in the related entry. Enjoy!
Have you ever wanted to be able to copy a query from ColdFusion's debugging output and run it in SQL tool to see the results? No big deal really, unless you happen to be using cfqueryparam. (You are using cfqueryparam aren't you?) If your query does use cfqueryparam you will notice that the SQL listed in the debugging output has question marks wherever you used a queryparam. The type and value of each queryparam is listed in the debugging output just below the query, but in order to run your query in a SQL tool you have replace each of the question marks in the SQL statement with the actual queryparam value, remembering to quote values as necessary. Still not a big deal, until you have to do this for a 500 line SQL statement with 50 or more queryparams. Then it gets to be a little annoying.
Often times there are several different ways to write a SQL query to return a desired result set. Some times the ordering of JOINs and WHERE predicates or the approach taken in the query (nested queries vs. joins for example) can make a big difference on query performance. You won't really notice a difference on smaller databases, but on queries which search thousands or millions of records you may see a big difference in performance simply by changing the order of where predicates. So the question becomes, when given two or more queries which return the same results, how do you determine which one performs best?
Well if you are using MS SQL Server just copy the queries into the same Query Analyzer window, turn on the Show Execution Plan (Ctrl+K), and run the queries (F5). Once the queries execute go to the Execution Plan tab and note their relative costs. For example if you run two queries you may have results which look something like:
In this case you would want to go with Query 2 as it has the lowest cost relative to the batch.
Another thing I should point out here is that some times you will notice that two seemingly "different" queries have the exact same cost relative to the batch. If you look at the execution plan you may notice that the query optimizer generated the exact same execution plan for these queries. In these cases it may not make much of a difference which query you choose. (This happened a me a couple of weeks ago. I had 4 different versions of a query, each of which was optimized to the same execution plan by SQL Server's optimizer.)
Anyway, I'm sharing this little Query Analyzer tip because I discovered that not a lot of CF developers are aware of this feature, or at least not as many as I though would be. Also, I'm relatively new to SQL Server so feel free to chime in with any other Query Analyzer tips you may have.
