However, you need to do a tiny bit of setup work before you can use the functions. You need to load the timezone info from your system into the mysql db.

Thankfully, the MySQL Developers have included a very handy script that will do just that from a zoneinfo files which are used on Linux and on Mac's.

Obviously change the mysql params or add a -p if you have a password or are connecting to a remote server.

Check out the MySQL developer site for full details on MySQL timezone support

Cheers, Mark

I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial.

The cut down query below demonstrates how it works.

• First we drop the temporary table in case one has been left lying around.
• Then we select a query into the temporary table which include some complex subqueries.
• We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
  • 'yes' if all users for a class are selected
  • 'partial' if some users are selected
  • 'no' if none are selected.

Mostly posted for my future reference but hope it helps out.

Cheers, Mark

I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data.

A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it.

So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:

Instead cast the bit to an integer in MySQL like so:

Voila, it works like expected.

Cheers, Mark Lynch

Enable security Manager

NOTE the example from the adobe site has the quotes in the wrong place. Note you also need to change [cf_webapp_root] to match the location on your machine.

Datasources

After I enabled Datasource security I began to receive the following error, a bit of digging reminded me that the MySQL connector tries to do some autoconfiguration for coldfusion to optimise it.

It was failing to load the configuration file which is inside the mysql connector jar file /com/mysql/jdbc/configs/coldFusion.properties

I haven't gotten to the bottom of why it couldn't be loaded but adding the following to the datasource query string fixed it up:

I would suggest adding some of the settings from this file as paramaters in your datasource settings as per previous post

Note: This was using the following:

• ColdFusion 7.0.2 Cumulative Hotfix 1 Multiserver install
• MySQL connector/J 5.0.8

Hope it helps. Cheers, Mark

We weren't intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem.

A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07.

On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn't doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4.

So I added the parameter directly to the querystring:

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase.

Hope it helps.

Cheers, Mark

Summary of Problem

CF 7.0.2 Cumulative Hotfix 2 and 3 breaks the use of Multiple SQL statements with MySQL connectorJ connector.

This is a regression as it works perfectly in CF 7.0.2 and CF 7.0.2 with Cumulative hotfix 1.

I have tested the following configurations:

• CF7.0.2 MySQL connectorJ 5.0.8 - works
• CF7.0.2 hotfix 1 MySQL connectorJ 5.0.8 - works
• CF7.0.2 hotfix 2 MySQL connectorJ 5.0.8 - broken
• CF7.0.2 hotfix 3 MySQL connectorJ 5.0.8 - broken
• CF7.0.2 MySQL connectorJ 3.1.10 - works
• CF7.0.2 hotfix 1 MySQL connectorJ 3.1.10 - works
• CF7.0.2 hotfix 2 MySQL connectorJ 3.1.10 - broken
• CF7.0.2 hotfix 3 MySQL connectorJ 3.1.10 - broken

Steps to reproduce bug:

1. Set up a CF 7.0.2 Multiserver install with built in webserver

2. Download and install ConnectorJ JDBC driver for MySQL and install as per Adobe Technote http://www.adobe.com/go/6ef0253

3. Create a mysql database named "test" the and create a table with the following script:

4. In CF Admin create a DSN named dsn_test with following settings

• JDBC Url: jdbc:mysql://127.0.0.1:3306/test?allowMultiQueries=true
• Driver Class: com.mysql.jdbc.Driver
• Driver name: (leave blank)
• username: (valid user for your db - default: root)
• password: (valid password for your db - default: 'blank')
• Place the following file in the /opt/jrun4/servers/cfusion/cfusion-ear/cfusion-war directory

This code should function correctly and return a cfdump of the auto generated primary key as per expected screenshot.

Next apply Coldfusion Cumulative hotfix 2 and restart coldfusion as per Adobe Technote http://www.adobe.com/go/kb400996

Re run the test and no query set will be returned from the driver. The qTest variable does not get set as per the broken screenshot.

Related Resources

CF Updates page http://www.adobe.com/support/coldfusion/downloads_updates.html#mx7

CF 7.0.2 Cumulative hot 3 http://www.adobe.com/go/kb402465

CF 7.0.2 Cumulative hot 2 http://www.adobe.com/go/kb400996

CF 7.0.2 Cumulative hot 1 http://www.adobe.com/go/kb400074

MySQL ConnectorJ http://www.mysql.com/products/connector/j/

MySQL ConnectorJ 3.1.10 Download: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.10.tar.gz/from/pick#mirrors

If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.

This will allow you to do queries like the following to return autoincrement values:

Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.

Firstly, CFMX used UTF8 by default so it's already configured. You need to make sure that your MySQL DB table has a Charset of utf8 as per example below:

Then you need to add the following params to the connection string:

• characterEncoding=UTF8
• characterSetResults=UTF8

I also add the "allowMultiQueries=true" string which allows you to execute multiple queries in the one statement. It is disabled by default as it can open up a window for SQL Injection attacks, but this can be mitigated by always using CFQUERYPARAM's.

So the final connection string looks like this:

Hope it helps, Cheers, Mark