Whilst writing a ColdFusion based query to create a JSON response I thought I'd look up returning data values as a "Yes/No" string rather than "1/0". Then I wouldn't have to transform it in any way to use in the JSON build script.

The mySQL version allows you to do this by evaluating the value, and matching a string if the condition is met, like this:

This does not work in ColdFusion at all. An error is thrown:

After a little tweaking it seems that if you alias the field it does work. In the example code below I've simply aliased the field with its own name.

I'm not exactly clear why, as the error message above isn't all that helpful.

There are pro's and con's to both mySql and MS SQL, one of the pro's of mySql is the limit function. This allows you to set a starting record number, and select N number of records from there. But how can we do that in MS SQL?

This is usually used for pagination functions, IE SELECT the next N records from the current count.

Firstly declare two variables, a "rows per page" and a start record.

Next we need to write the query we want, but wrap it in a parent select. Remember at this point to do all your conditional processing on the query inside the parenthesis.

Next we can apply a where clause that filters down the results based on the two variables declared at the top of the script. This means we will only get back the required number of records, starting at our defined record number.

The whole script looks like this:

I am selecting *, but I recommend that you actually list your fields here, I tweaked it for this entry.

Not quite as nice as the mySql version tho is it:

One of the tables in an online application stores entire html templates in a blob field, this data is pulled out and used to build emails, replacing specific keywords.

I recently needed to update some of this data, but there is not an administration system for it, so a manual SQL script would have to do.

The problem I encountered was that creating a html template as a variable using cfsavecontent and inserting it into MySql was causing all the ' to appear as ?. I read a blog entry based on Coldfusion MX and setting the character encoding in your coldfusion administrator, for each datasource. Or you could do it in the template, like this:

This didn't seem to work at all, in fact I found that it would error whilst trying to insert the cfsavecontent variable using cfqueryparam. There seemed to be some sort of encoding issue between the two tags.

This didn't work either with Cfqueryparam, or as a non param input. It turns out that you simply need to escape the characters (double them, or use character representations). IE don't use a single quote, use two . In this way the MySql encoding treats the character as a character, rather than a potential command. I still think that this is something that can be fixed by setting the encoding type, but in this case there was a far simpler solution.

A while ago a colleague and I were working on a timesheet application in Flex. The idea was that you could commit a custom timebar object, generated in flex, and it would update the dataset in the back end using the ColdFusion flex gateway.

I came across the code recently, and decided to tidy it up a bit, and make the query dynamic, based on the Struct contents. The obvious limitation to this is that your Struct and your database schema have to match exactly.

I won't go into the Flex application here, but I've emulated its input arguments here with a pre-populated structure.

Notice that this code also calls the CFC object at the end. The data itself isn't massively important, it's a time object for recording tasks.

Next we have the function, which accepts a Struct() argument called 'taskStruct'. I then loop through the structure, and populate a SQL query using the keys from a collection. The only logic is a check to see if it is the last structure element, as this controls the ',' placement.

That will insert your Struct into a database, in small and tidy manner. It was somewhere around here that we started using cfproperty tags, and creating strongly typed objects for Flex.

I was recently migrating some data from one application to another and came across a stumbling block in MySQL. If you are trying to run a large SQL file backup restoration and the source file is quite large you may encounter the error : 'Packet too large' or 'MySQL server has gone away'. This is cuased by a MySQL server memory error.

If you look in the mySQLserver installation path directory you should see a 'my.ini' file. This controls the default settings for SQL server when you start the service.

Add in a line of code like this:

under the value of [mysqld]. Save the file and then restart mySQL server.

You should now be able to run:

show variables like 'max_allowed_packet';

And have it return your ini value.

As long as the value you have set it to is greater than the file size it should run successfully.

You can also run it as a set session variable command in your SQL environment, like this:

This will set the same value for that instance of the sql server.

Update:

There also seems to be a massive difference in performance in running the imports directly into a GUI environment, or through a shell command. I've had a 600mb file fail several times in the GUI, but running:

It has imported within around 10 to 15 minutes.