When you query a database with ColdFusion and your result set contains null values ColdFusion represents those values as an empty string. This is great as long as you're aware of it, you can just check the length of the string and if it's greater than 0 you know it isn't null and isn't empty.
The problem is what do you do when you want to insert or update a null into the database? If the value will always be null you can just hard code it like this...
However this doesn't help us in most cases where the value could contain something or it could be null. To do this we need to use the "null" attribute of the cfqueryparam tag. If the null attribute is set to false nothing happens and the value is used as normal. However if the null attribute is set to true, the value attribute is ignored and a null is sent to the database.
In this example if the length of the address passed in from the form is greater than 0 the value is used. However if the length is 0, the value is ignored and null is sent to the database.
Also note that this functionality is available in cfscript as shown below...
Sunday, 7 July 2013
Sunday, 2 June 2013
SQL Server 2012 Paging With OFFSET And FETCH
Please Note: If you're running a version of SQL Server earlier than 2012 this method for paging won't work. I have described a similar method for paging that works with all versions SQL Server 2005 and later here.
SQL Server 2012 provides us with the OFFSET and FETCH clause. We can use this clause to implement paging in a much easier and more readable way than in previous versions of SQL Server.
Let's use the same simple example we used in the earlier post...
And we want to view the 2nd page of a 10 record per page listing. Unlike in earlier versions of SQL Server, we don't really have to change the query at all, we just add the OFFSET and FETCH clause to the end of the query...
The above query will return records 11 to 20. There are just two things to keep in mind: The first is that to use the OFFSET and FETCH clause you have to include an ORDER BY and the second is that you can't use TOP in the same query.
Now if we want the next page, we just increase the offset by 10, the rest of the query can stay exactly the same...
And there you have it, too easy!
SQL Server 2012 provides us with the OFFSET and FETCH clause. We can use this clause to implement paging in a much easier and more readable way than in previous versions of SQL Server.
Let's use the same simple example we used in the earlier post...
And we want to view the 2nd page of a 10 record per page listing. Unlike in earlier versions of SQL Server, we don't really have to change the query at all, we just add the OFFSET and FETCH clause to the end of the query...
The above query will return records 11 to 20. There are just two things to keep in mind: The first is that to use the OFFSET and FETCH clause you have to include an ORDER BY and the second is that you can't use TOP in the same query.
Now if we want the next page, we just increase the offset by 10, the rest of the query can stay exactly the same...
And there you have it, too easy!
Sunday, 26 May 2013
SQL Server Paging
Please Note: If you have SQL Server 2012 there is a slightly better built-in option available, which I have described here. The method described in this post works for all versions of SQL Server 2005 and later.
This post explains a very efficient way of retrieving pages of data from SQL Server. Rather than retrieving a whole data set and filtering it down with code when generating the display, which could become very slow given a large number of rows, we will only select the 10-20 rows you will be displaying at any one time.
Let's take the following query for example, which returns a list of all the members in the database that have a status of 1.
Let's imagine we want to to view the 2nd page of a 10 record per page listing, in other words we want the rows between 11 and 20. If we used the above query we would have the entire record set returned, then we can use our programming language of choice to loop over the 10 records we want and display them as required. Not very efficient given that this query could potentially return a very large number of records.
Luckily in SQL Server there is a way to reduce this result set to just the records you need for each request.
This query creates a Common Table Expression which selects the data we want while numbering each row using the ROW_NUMBER() function. Then we select the rows we want from the CTE by using the BETWEEN clause. This method is immensely faster than a straight up select query, especially on large data sets, making your pages load significantly faster.
This post explains a very efficient way of retrieving pages of data from SQL Server. Rather than retrieving a whole data set and filtering it down with code when generating the display, which could become very slow given a large number of rows, we will only select the 10-20 rows you will be displaying at any one time.
Let's take the following query for example, which returns a list of all the members in the database that have a status of 1.
Let's imagine we want to to view the 2nd page of a 10 record per page listing, in other words we want the rows between 11 and 20. If we used the above query we would have the entire record set returned, then we can use our programming language of choice to loop over the 10 records we want and display them as required. Not very efficient given that this query could potentially return a very large number of records.
Luckily in SQL Server there is a way to reduce this result set to just the records you need for each request.
This query creates a Common Table Expression which selects the data we want while numbering each row using the ROW_NUMBER() function. Then we select the rows we want from the CTE by using the BETWEEN clause. This method is immensely faster than a straight up select query, especially on large data sets, making your pages load significantly faster.
Saturday, 4 August 2012
ColdFusion Local Scope
The following code demonstrates the problem...
As you can see, function2 overwrites the data variable that we are trying to use in function1. By adding the var keyword to our variable declarations we ensure that the variable is local to that function and cannot be accessed outside of it, meaning that even if the same variable name is used outside of the function it wont be overwritten.
The problem may seem pretty obvious here in this example but in a much bigger application it could cause a lot of problems. Perhaps someone else wrote function2, it may do something completely unrelated to your data variable and it almost certainly wouldn't be coded right bellow function1 like it is in this example.
Running into a problem like this could take a while to figure out if you're not looking out for it so remember, ALWAYS var scope your function variables!
Output: GONE
As you can see, function2 overwrites the data variable that we are trying to use in function1. By adding the var keyword to our variable declarations we ensure that the variable is local to that function and cannot be accessed outside of it, meaning that even if the same variable name is used outside of the function it wont be overwritten.
Output: Hello world
The problem may seem pretty obvious here in this example but in a much bigger application it could cause a lot of problems. Perhaps someone else wrote function2, it may do something completely unrelated to your data variable and it almost certainly wouldn't be coded right bellow function1 like it is in this example.
Running into a problem like this could take a while to figure out if you're not looking out for it so remember, ALWAYS var scope your function variables!
Sunday, 26 February 2012
JQuery Autocomplete Using ColdFusion CFCs
I recently had to create a form text field that used jQuery autocomplete on a ColdFusion website and
I thought it was pretty cool the way it worked so I thought I would share. Here it is in action...
This form can be broken into 3 fairly simple parts: The basic HTML that displays the form, the CFC that queries the database and the jQuery that ties it all together. Let's start off by looking at the form code...
Nothing to see here really, just an input text field and a submit button that doesn't do anything yet. Next up is the CFC we use to query the database...
The queryNames function is fairly similar to any other function you might write to query a database except it limits the amount of rows returned to 10. If you don't do this your autocomplete list will come out too long and could possibly take a while to load if you have a lot of rows in your table.
And finally we bring the two together with jQuery...
Here we use Ajax to call the queryNames method in our cfc, passing in the current value of the text field, then jQuery uses the response to populate the autocomplete widget. Pretty cool, huh?
Hope this helped!
This form can be broken into 3 fairly simple parts: The basic HTML that displays the form, the CFC that queries the database and the jQuery that ties it all together. Let's start off by looking at the form code...
Nothing to see here really, just an input text field and a submit button that doesn't do anything yet. Next up is the CFC we use to query the database...
The queryNames function is fairly similar to any other function you might write to query a database except it limits the amount of rows returned to 10. If you don't do this your autocomplete list will come out too long and could possibly take a while to load if you have a lot of rows in your table.
And finally we bring the two together with jQuery...
Here we use Ajax to call the queryNames method in our cfc, passing in the current value of the text field, then jQuery uses the response to populate the autocomplete widget. Pretty cool, huh?
Hope this helped!
Subscribe to:
Posts (Atom)