ColdFusion Muse - Coldfusion & Databases

Data Truncation Error: Migrating MySQL to MSSQL

Thu, 23 Jul 2009 17:41:00 -0500

I have one more tip as a follow up to my previous post on Migrating Between MySQL and MSSQL. It has to do with the dreaded "data trunction error". If you have used MSSQL you may have seen this error crop up from time to time. It is a common error and very easy to remedy. The error occurs when you have a character field with a length that is too short for the size of the string you are trying to insert. Check out this example....

[More]

Migrating Between MySQL to MSSQL

Mon, 13 Jul 2009 18:02:00 -0500

I recently did an emergency stint of troubleshooting for a site owner (a designer who owned a complex ColdFusion site) who was hit with the HTML injection issue on his site. He had done a good deal of work on his own and cleaned up the HTML as best he could. He was busy moving the sites to a more secure environment (a better hosting company, no more FTP, intrusion detection and solid VPN support). He had managed to travel a long way down the migration path before he ran into trouble. His new environment used MSSQL and his old environment used MySQL.

Now I love MSSQL and I think it is a wonderful choice (price notwithstanding), but had he contacted me before he decided to go this route I would have suggested that he stick with MySQL for the sake of compatibility. Unfortunately he had already "flipped the switch" before I got there and so there was a lot of "on the fly" changes to make just to get his site working correctly again. One of the biggest issues had to do with his choice for migrating the actual data. He had chosen to use an export tool to move the MySQL data into an Microsoft Access file. He then used Microsoft Access "upsize" wizard to send the data to the MSSQL server. The biggest flaw with this approach is that it resulted in missing dates which were not translated correctly from MySQL to Access to MSSQL. So we had to re-export the data in to SQL dumps, modify them and then run them against MSSQL.

The date problem is not a typical incompatibility with MSSQL, but there are several we ran into that we had to account for. Here they are in random order:

[More]

Tabular Data Stream Error (Who's Using a Cursor Select Method?)

Thu, 14 Aug 2008 14:27:00 -0500

It's not often I see an error that I have simply never seen before. Here's one that some of you may recognize. It happened on a query using SELECT * against a fairly limited table on an MSSQL 2005 database. There wasn't anything unusual about the query (except for the bad form of using the asterisk). Here's the error that was thrown:


rror Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol 
stream is incorrect. 
Parameter 2 (""""): Data type 0x38 is unknown. 
The specific sequence of files included or processed is: ******

The Fix

[More]

Migrating to MSSQL 2005 and UNION Queries

Fri, 18 Jul 2008 10:51:00 -0500

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.

[More]

Ask-a-muse: Proper Use of Cachedafter

Tue, 18 Mar 2008 17:43:00 -0500

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:

[More]

Coldfusion and Oracle 8i Performance

Sun, 09 Mar 2008 00:07:00 -0500

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

[More]

Creating Views With CF Query: DDL Follies

Wed, 14 Nov 2007 19:35:00 -0500

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.

[More]

Using Rowcount in T-SQL

Fri, 07 Sep 2007 13:45:00 -0500

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?

[More]

Handling Variable Form Data in a Stored Procedure

Mon, 13 Aug 2007 21:49:00 -0500

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: SELECT * FROM USERS WHERE userID IS NOT NULL AND UserName = '#form.username#' AND Address = '#form.address#' 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?

[More]

JDBC Drivers, Data Binding and Implicit Conversion

Sun, 25 Feb 2007 00:13:00 -0500

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:

[More]

Cfqueryparam Fails When Using "WHERE EXISTS"

Wed, 06 Dec 2006 14:10:00 -0500

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.

[More]

Why You Should Join AA (Access Anonymous)

Fri, 22 Sep 2006 13:43:00 -0500

I'm an Access hater too - so the idea of giving tips on it galls me just a bit. Recently, however, I had the opportunity to help a fellow with his Access driven Coldfusion site. This poor soul had moved his Coldfusion site from experthost.com to godaddy.com to save a few bucks. Saving money on hosting is almost never worth it in my opinion - but I won't go there for this post. After much heartache he got the Access DSN installed using the godaddy control panel. But, uh oh... the new Access driver is the one with Unicode support (at least that's what I surmise without talking to the perky but useless godaddy support). That means that lots of things are now broken.

[More]

More CF and DTS Troubleshooting

Fri, 25 Aug 2006 15:55:00 -0500

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.

[More]

Top and the UNION Query

Fri, 25 Aug 2006 09:10:00 -0500

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,
            dateAdded
      FROM    portalUsers
      WHERE   unApproved = 1
   UNION
      SELECT TOP 1 fname, lname,
            'Intranet' AS sourceTable,
            dateAdded
      FROM   intranetUsers
      WHERE   Approved = 0
   ORDER BY dateAdded
</cfquery>

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.

[More]

Inserting and Updating in a Single Query Statement

Fri, 23 Jun 2006 10:20:00 -0500

This is a tip about something I do regularly. No, I'm not talking about forgetting to put down the seat. I'm talking about coding. Let's say you have an application that collects user data in a wizard-like format using a few steps. You allow folks to go back and forth between steps and edit what they've done. How do you handle the insert on that first step without creating duplicates when users return to that step? There are several approaches to this problem:

[More]