I normally don't use the query of query functionality built into ColdFusion. But, for the project I am currently working on (top secret as it is at this point in time), I need to use query of query.

So, in my first (real) query from the database I returned four columns with about 400 records. This is what an interface I am working through actually runs in the background. The query looked something like this:

I needed to remove a few characters from the end of the "title" field because it had a ";1" or ";0" appended to the end of it (don't ask) based on whether or not they were a manager. So, I wrote a query of query to do this.

I get a really helpful error: Encountered "(. Incorrect Select Statement, Expecting a 'FROM', But Encountered '(' Instead, A Select Statement Should Have a 'FROM' Construct. What does that mean? I had no idea. Until I did some research. In what I read, it looks like in a query of query, you cannot alter the data of a column (cell). You can add to the query though. So, to return the title how I wanted it, I used this code:

So now the query of query qGetMembers returns four columns with a new one of title_final. You could even take this one step further (which I did) and do one more query of query to return the "title_final" column as "title" for consistency, but this works as is! This took me about four hours to figure out. Now that I know this, I definitely won't forget it, and I hope this helps someone out too, so they don't have to waste alot of time on the wonderful query of query.

On my team, like most teams, we have a development, test and production database. We move data from test (sometimes called build) every evening to production. In the past, and we still do some today, we have ColdFusion scheduled tasks that run every evening to move the data.

In the past few months, after hiring a full-time SQL Server DBA, we have been moving to Replication and removing the ColdFusion scheduled tasks to move data. Today, we ran into a snag. We had changed a table over to Replication that contained a Blob (image) column. When trying to insert into that table, we received this error from SQL Server:

It seems that when Replication was set up, there was a max length put on that field when data is moved over. To fix it, we needed to up the "limit". We ran this script:

Basically, we upped the limit to 10MB. Now, this takes care of all text, ntext and image columns in tables that are replicated, but it makes Replication slower. With the amount of data in our tables, we probably won't see much of a change if any in speed of the Replication job.

The past two days have been a blur for me. I have been upgrading from ColdFusion 7 to ColdFusion 8, in multi-server install, and then redeploying ColdFusion 7 (under CF8 and JRun) via an .ear file. It all went pretty smoothly with the help of Jason Betz one of my team members. We have to have both versions of CF running until we have our December code push, because I am still developing applications on CF 7 but am on the team to bring in CF 8.

When I was done deploying CF8 for one of my instances (let's call it ins1 for simplicity) and car filed all my settings over (yes a car file from the CF7 administrator works in the CF8 administrator) I tried to validate my data sources. We use Windows authentication for our data sources. We do this so we know who is doing what to the database, albeit our DEV database. We set it up through the Services Manager and have ColdFusion run as "me" instead of "system". This allows kind of a pass-through authentication to happen between ColdFusion and the database server. This worked for my CF8 instance. No problem. This is because the new macromedia_drivers.jar and the DDJDBCAuth04.dll is included with CF8. In a multi-instance install, these files are found in [JRun4 root]\servers\[server]\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib .

Now, I went to validate my data sources on my CF7 instance. I had it set up the same as my CF8 instance. Service runs as "me" and my car file and settings were correct. All my data sources failed! W00t! Well, they failed because I did not spawn this instance as a CF8 instance. It came from a CF7 ear file. So it was using the old drivers and did not have the needed DLL file for SQL authentication. I needed to download the new macromedia_drivers.jar and get the DDJDBCAuth03.dll and put it [JRun4 root]\servers\[cf7 server]\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib. There is an Adobe tech note on this here http://www.adobe.com/go/42dcb10a. So just a little tip for those developers out there that need to deploy CF7 off of CF8.

When I deal with multiple inserts/updates/deletes in SQL server and in stored procedures, I use XML to do this. This is a practice I learned from my friend, Bear, who happens to be an amazing Oracle DBA. For example, say I want to assign a group or many groups to a user, in my DAO, I would create an XML document like this:

Then I would pass that XML to a stored procedure (in my DAO) and have that procedure rip through the XML and do multiple inserts in an implicit transaction. So basically, if it fails it will roll back all of the inserts. Here is what a stored procedure (in SQL Server) would look like to do multiple inserts via XML.

[More]