Working with SQL and database connectivity has now been simplified, thanks to the built-in SQL Editor window within ColdFusion Builder.
Not everyone is a SQL guru, capable of writing intense JOIN statements across 5 tables (including multiple AND clauses), all in one go.
Chances are, a SQL statement will be written, added to the cfquery tags and the results displayed using a cfdump - classic step debugging.
ColdFusion Builder has helped to alleviate some of the stresses and strains from us non-SQL masters, and we are now able to access the SQL Editor window directly from within the code editor to write AND test returned data from pre-defined datasources.
Show me how
OK.
It's all about context
Access to the SQL Editor can be made by right-clicking anywhere within an active code editor window. From the context-menu, select the 'SQL Editor' option:
This will load up the SQL Editor window, as shown below.
From here, you can select the datasource you wish to work through from the drop-down list, and also change the ColdFusion server instance to access any datasources under the selected server.
Code Completion
The SQL editor offers user-friendly, clear code-formatting and colouring, which visually makes it a little easier to write and debug statements.
Code-completion is also available within the window. In this instance, after typing the opening 'SELECT' statement, pressing the space button will load up a sub menu listing the available database names, all thanks to the connection to the RDS.
After selecting the table name, typing a full stop/period will enable a second sub menu listing all columns within the selected table.
This is a fantastic time saver, and reduces the possibility of any typos or spelling issues that could cause conflicts or errors when running the query.
Write it all out
In my working example, I am creating a query against the 'monkehDatabase' and the 'kickAss' table to pull out the complete list of current items that officially 'kick ass'.
I have simple pleasures.
With the SELECT statement finished (albeit in this case a very simple one), simply click the 'Execute Query' button at the bottom of the window. The Editor will connect to the datasource through RDS and will open up the 'Query Result' tab to display the returned information.
Returned data
So, according to the monkehDatabase, what kicks ass?
Thanks to ColdFusion Builder's built-in SQL Editor, we have now easily got a visual return of all records within the table, in this case listing (using boolean values) what does and does not KICK A.
You can see here that, unlike Chuck Norris, ColdFusion and Guinness, the Teletubbies don't.
Beware: you want everything?
The SQL Editor will return ALL records within a table if you run a SELECT statement. There is no capping limit for development purposes; all records would be returned, which could possibly take some time depending on the size of the database and use up CPU that could be better used elsewhere.
To avoid this unless you want all records returned, you can of course add a 'TOP' clause to the SQL statement to limit the number of records brought back to you.
Insert the code
Once you are happy with the query you have written, fully-tested and suitable for your needs, ColdFusion Builder adds an extra level of help to ease development for you.
Clicking the 'OK' button in the SQL Editor window will insert the SQL statement you have just worked meticulously on directly into the code editor window, at the current position of the cursor.
It saves a mass of time either copying and pasting the statement, or writing it out again. More time to drink Guinness and watch Chuck Norris movies.
Easy as tubby custard
ColdFusion Builder offers a streamlined, simpler way to manage your data, develop your applications and optimise your work flow. Whether you are a hardcore SQL developer or not, the built-in SQL Editor will certainly assist you and help to make your life easier.
You can now add yourself to the list of things that kick ass. Good job!
Oh, and I'd better update the table... watch out Chuck.