ColdFusion Muse

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:

Eliminating Duplicates In a Query When Distinct is not an Option

Mark Kruger October 27, 2006 6:28 PM MS SQL Server Comments (7)

Here's a problem perhaps you have had. You want to select unique email addresses, first names and last names out of a database for a newsletter or to sell them a new mortgage or whatever. Being the nice guy that you are you don't want to send them multiple messages, so you want to eliminate duplicates, right? the problem is that SELECT DISTINCT... doesn't always work in this instance. For example, John Doe put his information in as John Doe in one case and John H. Doe in another. Selecting distinct for name and email will give you a duplicate name with the same email. Now obviously you could solve this problem in your Coldfusion code - but wouldn't it be nice to fix up the query?

Read More
  • Share:

Farcry Indexing Script for MS SQL

Mark Kruger October 13, 2006 10:54 AM farcry, MS SQL Server Comments (4)

One of the downsides of a complex CMS is performance. I do not mean that a CMS can't perform well - it certainly can. I only mean that there is less margin for error when so many things are happening with each request. The more complex the application, the more the developer should pay attention to scalability issues. Probably nothing impacts your performance more than your database. I often tell customers who are prepared to spend money on hardward to buy the best server you can afford for your database server. In fact, a cheap web server and a solid Database server beat a great web server with a pokey db server every time. Anyway, I have been working with Farcry for some time now. I have used the information from Daemon on DB Indexing Strategies to make decisions about my Microsoft SQL Server indexing.

The suggestions they make are a bit generic. I have compiled my suggestions for indexing into an indexing script. I like using primary keys where it makes sense (where I need a unique index). Here's a tip. If you are in the habit of setting your primary keys in Enterprise Manager and you want a column other than the primary key column (or columns) to have a clustered index, make sure you create the cluster index first then set your primary key. Why? Because EM will automatically create a PK as a clustered index - which means you will need to drop it to create a clustered index anywhere else. Any way, here's the indexing script. Keep in mind that it will probably throw errors when you run it (null columns and the like) - so you may need to use it as a guide instead of simply running it in EM. If you have a pristine installation and you want the whole ting (constraints and defaults as well as just indexes and keys) give me a shout.

  • Share:

MS SQL's Casting Causes Consternation

Mark Kruger October 2, 2006 6:02 PM MS SQL Server Comments (4)

If you are using MSSQL Server an Cfqueryparam, you should be aware of the old implicit datatype conversion. This helpful feature uses an order of precedence to accommodate code that is written to pass in a sql_variant data type. To put it another way, lets say you are comparing a column of the type "varchar" to the the number 1 or 2. If it was me, I would write "WHERE myCol = '1' OR myCol = '2'" - or perhaps use an "IN" clause. This code will always work. It is saying "compare a character '2' to the character column myCol". Since they are both of the same type there is no conflict - but what happens if you mismatch the type?

Read More
  • Share:

Limiting the Databases Seen in MS SQL Enterprise Manager

Mark Kruger September 23, 2006 11:44 AM MS SQL Server Comments (0)

Did you ever connect to a an SQL server using Enterprise Manager where the server in question had a hundred or so databases installed? If you have you will know there is a significant delay when you expand the tree to find your database. Then you will have to sort through all those other databases to find yours. The other databases are listed there even though you do not have permissions to access them. Not only is this a security risk, it is annoying and time consuming. There is a solution....

This tip comes from Russ (a.k.a. Snake) on the CF Guru list. Russ pointed us to this KB Article from Microsoft includes a stored procedure that modifies the process of creating that browse list and shows the connecting EM only the databases they are entitled to see. This speeds up the process of using EM. Plus, you really don't want everyone in the world to see that you named your database "Pookey". In my own test I found that it worked exactly as advertised - but make sure you read the whole article. There's an easy roll-back plan, just make sure (as always) that you understand how it is supposed to work.

  • Share:

More CF and DTS Troubleshooting

Mark Kruger August 25, 2006 3:55 PM MS SQL Server, Coldfusion & Databases Comments (2)

For those of you that have made use of my blog post on DTS and Coldfusion and the subsequent blog on Troubleshooting DTS and Coldfusion, I have a 2 more useful tips. One has to do with a way to trap errors on the server and get information about what's going on on the client - the second tip has to do with protocol selection and conflict.

Read More
  • Share:

Top and the UNION Query

Mark Kruger August 25, 2006 9:10 AM MS SQL Server, Coldfusion & Databases Comments (7)

A "UNION" query can be very useful. Take sorting for example. If I want to select 2 types of data from the same table using different conditions and order by my conditions union is a great tool. See my previous post on Using UNION in your queries. If you also like using TOP you may run into an unexpected behavior when using UNION. Let's say you had 2 tables, portalUsers and intranetUsers and you wanted to get the most recent entries in each that were unapproved. If you try something like this:

<cfquery name="myquery" datasource="mydsn">
      SELECT    TOP 1 fname, lname,
            'Portal' AS sourceTable,
      FROM    portalUsers
      WHERE   unApproved = 1
      SELECT TOP 1 fname, lname,
            'Intranet' AS sourceTable,
      FROM   intranetUsers
      WHERE   Approved = 0
   ORDER BY dateAdded      
You might expect to get 2 records equal to the most recently added portal user and the most recently added intranet user - right? You could take the first record and you would be good to go. Actually, unless the "portalUsers" table has a clustered index ordered by dateAdded, this is not the case.

Read More
  • Share: