ColdFusion Muse

Migrating to MSSQL 2005 and UNION Queries

Mark Kruger July 18, 2008 10:51 AM MS SQL Server, Coldfusion & Databases Comments (5)

For the most part, migrating your ColdFusion site from Microsoft's SQL 2000 server to SQL 2005 is a snap. Import the databases from 2000 to 2005, re-point your data sources to the new instance using the ColdFusion Administrator and you are done. No muss, no fuss. There is very little query code that you will need to change. Sometimes you needn't change anything in your ColdFusion code at all. Here is one that I found recently however that you may run across - especially if you are a fan of UNION queries. Here's the skinny.

Read More
  • Share:

Ask-a-muse: Proper Use of Cachedafter

Mark Kruger March 18, 2008 5:43 PM Coldfusion & Databases Comments (3)

Muse Reader Asks:
I'm trying to use cachedAfter in a query. I have tried cachedAfter="#dateAdd("d", 1, now())# but it doesn't seem to ever cache after a day has passed. What am I doing wrong? cachedwithin seems to work fine.

This is one of those obscure tags that gets very little use in the community. I confess to never actually having found a use for it in production. Still, it bears some looking at because it is a clever notion that might come in handy under certain circumstances. Here's a primer:

Read More
  • Share:

Coldfusion and Oracle 8i Performance

Mark Kruger March 9, 2008 12:07 AM Coldfusion & Databases Comments (2)

Recently I had the opportunity to work with a server setup that included two load balanced servers running Coldfusion 7 enterprise backstopped by a hefty Enterprise Oracle 8i server. The CF 7 servers were going down repeatedly. The database people blamed the web people who blamed the networking people who blamed the government. No one seemed to make any headway on the issue. I was engaged to get the CF 7 (and 2 CF 8 servers using a separate configuration) stable. I arrived on site with my change document in hand. I learned, or I was reminded of, a number of things during the experience that I will try to boil down into two or three posts - including:

  • Don't Accept the Default Settings - It's surprising how many enterprise servers I run into that have been installed but left with the "default settings". Naturally the default settings are simply inadequate to support any usage beyond the bare minimum.
  • Most Code Evolves Without Planning
  • Team Troubleshooting is Key - When systems are highly complex, no one person will ever have all the necessary information to explore all the possible solutions. It takes bringing together folks from the various disciplines (networking, database, Coldfusion, Linux etc.) to really make it work.
  • Big Oracle Tip - This will be the topic of this post

Read More
  • Share:

Creating Views With CF Query: DDL Follies

Mark Kruger November 14, 2007 7:35 PM MS SQL Server, Coldfusion & Databases Comments (2)

Most queries in your Coldfusion code do one of four things - Select, Insert, Update or Delete. Maybe you did not know that, given the proper permissions, you can do just about anything that can be done on the DB server from within a query. You can backup and restore, drop users, even execute shell commands. That's why you should never create a datasource using the SA user. Instead you should define what you want a datasource to do and create a user for that purpose. Still, sometimes it is useful to be able to do other things using Coldfusion and Cfquery.

For example, I have a generic table with rows that look like "col1, col2" that holds form data. In this particular application the customer creates custom forms to collect data from specific clients. All the forms look different. One might have fullname, address, city, Postal code, and the next one might see first name, last name zip. When the data is submitted it is put in col1, col2, col3. But he has a reporting tool that allows him to query tables from the database and run reports for his customer. What can we do to make it easier for him to report? Surely "select col1, col2" isn't going to do it. The answer is to use T-SQL Data Definition Language (DDL) to create a view for each customer.

Read More
  • Share:

Using Rowcount in T-SQL

Mark Kruger September 7, 2007 1:45 PM MS SQL Server, Coldfusion & Databases Comments (6)

Here's a tip for limiting the number of rows returned from a query. Now I know you think "that's easy - just use TOP." Ah... but what if TOP is not an option? Consider this example. You have a reporting stored procedure that you want to use to return multiple results sets to your Coldfusion Page. Easy - right?

Read More
  • Share:

Handling Variable Form Data in a Stored Procedure

Mark Kruger August 13, 2007 9:49 PM MS SQL Server, Coldfusion & Databases Comments (4)

Lots of projects have a requirement that interaction with a database must be done using stored procedures only. Stored procedures are generally quite easy to write, but there are some things that are slightly more difficult that using a straight CFQUERY. For example, perhaps you have seen code that handles a search form. You might see a query that looks something like this:

<cfquery name="get" datasource="#dsn#">
    SELECT * FROM USERS
    WHERE    userID IS NOT NULL
    
    <cfif NOT isEmpty(form.username)>
        AND UserName = '#form.username#'
    </cfif>
    <cfif NOT isEmpty(form.address)>
        AND Address = '#form.address#'
    </cfif>    
</cfquery>
Please note, I'm use a UDF called "isEmpty" that simply trims the string and checks the length. Also keep in mind that I'm not adding the required Cfqueryparam to save room. How would you duplicate this code in a T-SQL stored procedure?

Read More
  • Share:

JDBC Drivers, Data Binding and Implicit Conversion

You may know that MSSQL allows for "implicit conversion" between data types. For example if you have a character column and you pass it a number without single quotes (as in myCharCol = 1), MSSQL will automatically convert the value 1 into a character for the purpose of the query. On a Coldfusion 5 server this behavior carries through seamlessly even when you are binding data using cfqueryparam. However, if you are using cfqueryparam in a query on a CF 5 server and also relying on implicit conversion within the same query you should know that you may have a problem getting that cfquery to work in Coldfusion MX. The issue is that JDBC "prepares" the statement by validating against the schema. Here's an example:

Read More
  • Share:

Cfqueryparam Fails When Using "WHERE EXISTS"

Mark Kruger December 6, 2006 2:10 PM MS SQL Server, Coldfusion & Databases Comments (2)

I have always been an advocate of Cfqueryparam. Binding your variables innoculates you against SQL injection attack, often results in speed improvements and lessens the load on your database server. It may even help with the laundry (that's the word on the street). This morning I ran across an error that is produced by the correct use of cfquery param. It has to do with using the clause "WHERE EXISTS" in your query. Here's the query in question.

Read More
  • Share: