Dan Mackey On ColdFusion Development

ColdFusion, Java, .NET, AJAX, DHTML development and general programming. Daniel is owner and Administrator of CFTagStore.com and works for an Application Development Company in Ireland called Digital Crew. His interests cover all areas of programming in multiple languages with a particular interest in Internet Technologies.

All Articles tagged : MySQL
2007-08-02 16:46:00.0

Navicat 8 for MySQL Released

We have just upgraded our copies of the fantastic Navicat 8 in the office from version 7 and first impressions have left me disappointed.

The upgrade process was fast and easy and I had it installed and activated within minutes. The most important thing to me and the bit I was looking forward to was the code-completion when writing queries. Sometimes you have so many field names you cant remember the exact name of all of them so the code-completion would be a God send. Its there alright but not as good as I thought it would be.

For instance, its not really automatic while typing. To activate it for SQL statements/functions etc you have to press CTRL+SPACE and even then it only gives you the name of the function, not the syntax like Visual Studios Intellisense gives you.

Another problem is when your dealing with named tables. You get a blank code-completion window when you try and use p.fieldName from a query detailed as "FROM pages AS p" It does work if you use pages.fieldName in other words, you have to call the table name explicitly.

Another problem I found after using it literally 3 minutes, was a hard crash (and repeating exception window) after I clicked new stored procedure and then cancelled it without doing anything else. I am guessing the app was developed with Delphi as I have seen the same type of problem with Homesite.

One of the most annoying things I found was that they no longer open objects in the tree using a single click. You have to double-click databases/tables etc to open them which when your used to 3 generations of the app over the last 4 years, its quite annoying. I thought surely they would have a Preferences dialog but unfortunately this was not an option in here although they do have quite a few customisable sections.

The good part was that it imported all my settings from Version 7. The app is also slower to start up which is one of the things I loved with Version 7 - It was so fast. Accessing databases online is also slower for some reason.

Overall, there is no real benefit to upgrading to Version 8 so I would hold off until an update is released before shelling out more cash for a product that doesnt necessarily improve anything.

Find Out More : Navicat 8 Official Website

Tags: MySQL | Reviews | Tools
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [4] - Leave a comment
2007-02-08 17:49:00.0

Handy MySQL - ORDER BY FIELD

Had a slight problem today and found a neat solution.

My problem was I wanted to sort properties by status and date added but needed the status to show in a priority fashion.

For example, I wanted the order of the property status to be For Sale, For Rent, Sale Agreed and also to sort by date added.

I couldnt use ORDER BY propertyStatus, propertyDateAdded because then For Rent would come before For Sale.

The solution was to use ORDER BY FIELD

Syntax: ORDER BY FIELD(propertyStatus,'For Sale','For Rent','Sale Agreed'), propertyAdded DESC

This assumes you need some order though. Another good application of this would be ordering a field where its 'HIGH','MEDIUM','LOW'

Tags: MySQL | Tips
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [13] - Leave a comment
2006-10-20 01:41:00.0

Efficient MySQL database queries for pagination

Heres a nice little known gem of SQL to help with selecting limited amounts of records from a table. This can be used to pull paginated recordsets from a MySQL database efficiently.

Usually when I do pagination, I do a query on the database and limit the output using

The problem with this is that the whole query is executed and the results filtered out for you by ColdFusion.

I have only ever used the MySQL LIMIT clause with one parameter e.g : LIMIT 10 to return only 10 results but I didnt know you could tell it to start at a specific row and return the next 10 records.

So, the above can be cut down to:

which basically puts all the work on the database and tells it to fetch 10 records from the database starting at record 20.

The ORDER BY is important because the 20 is the row index so it will order the table first and then start at row 20.

A small tip but a valuable one none the less.

Tags: ColdFusion | MySQL | Tips
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [6] - Leave a comment
2006-06-22 12:16:00.0

MySQL Database tools - Navicat, Toad and CFMyAdmin

We generally use 2 databases MSSQL and MySQL for our applications. Our personal preference is MySQL but sometimes due to customer requirements we have to use MSSQL.

For ages we were looking for a MySQL manager program like MSSQL Enterprise and in the earlier days used to use MySQLFront, a great little app which is no longer being actively developed. In fact our ColdFusion based MySQL admin tool, CFMyAdmin was modelled on this great little app.

As time moved on and we needed more functionality and reliability, we found Navicat. We bought 4 licenses (best value ever - $139 per license for Enterprise Windows Edition) and havent looked back! The latest version 7.2.5 has absolutely everything we require and is FAST.

Yesterday, I found another FREE MySQL database tool that looks interesting called TOAD for MySQL.

Its extremely complex at first and the user interface isnt as polished as Navicat but the SQL tools and syntax colouring looks promising.

The only thing lacking in both TOAD and NaviCat is an intellisense type interface while writing your sql but hopefully this feature will be introduced shortly.

EDITED 26th June 2006 - Thank you Gary for your comment on the Intellisense. My error, it appears TOAD has intellisence built in. i will turn it on, check it and report back!

As I mentioned above, we developed a ColdFusion based MySQL manager called CFMyAdmin a few years ago and unfortunately, the development has stalled. We plan on picking this free community project up again in the near future, start from scratch and developing a more robust and powerful ColdFusion based MySQL manager.

The problem with CFMyAdmin was that it is extremely hard to make a connection to a MySQL database using a connection string. It works perfectly in ColdFusion 5 as CF5 supports dynamic connection strings but MX removed this option so we had to use Macromedias Java Factory classes to make a connection to the database by creating a DSN on the fly. A lot of thought will have to go into how to connect to the DBS before we can start developing.

Tags: ColdFusion | MySQL
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [3] - Leave a comment