Skip to: Site menu | Main content

Email Facebook LinkedIn Twitter Google

Blog...

Qualify your table name.

Thanks to Tim for reminding me what a headache this can be!

I'm guilty of creating SQL statements like so:


SELECT testfield FROM sometable

Today that caused a real problem. An error that was incredibly difficult to find.

The error message was:


11 >
= 11 null

Seriously, that was the error message displayed by Coldfusion.

The answer was that we should've qualified the table name like so:


SELECT testfield FROM [sometable]

You can take it a step further and do this for your columns as well. I usually do not qualify my column names unless it's necessary, but qualifying your table name is easy and may save your hair.

Returning a uniqueidentifier from MS SQL Server in Coldfusion.

I ran into a problem trying to return the newly created identity from a table. The identity column was a uniqueidentifier type.

Table structure:

UniqueIDuniqueidentifier
TextFieldvarchar(50)

I needed to retrieve the new UniqueID value after an INSERT statement.

The default value for UniqueID is set to newid().

[More]

Scarce Flex Information.

I'm really surprised at the lack of information for good data access strategies in Flex.

I have a Flex application that will use Coldfusion as the back end, but I'd like it to easily adapt to a web service that could be written in PHP, Java, ASP, etc. I can't find any good resources that demonstrate a best practices for this kind of flexibility. There's a lot of talent out there so I know someone has already tackled this problem.

One field, One piece of data.

Today I ran across some old code that reminded me why being "clever" can get you into trouble. The code basically collects a bunch (over 30) data fields that are submitted via a web form. At the time the client was continually adding new data points. In an effort to make adding new data points easier we began to place a delimited list of key/value pairs into a single text field in the database. What a great idea! Now we don't have to add a new field to the table every time we add one to the form......Wrong! Today the client asked for the ability to sort/search on one of those data points. Something that should have been as easy as adding an ORDER BY clause to my query has turned into a refactoring job.