Sunday, October 26, 2008

SQL Server Database Coding Standards


Tables: Rules: Pascal notation; end with an ‘s’
Examples: Products, Customers
Group related table names1

Stored Procs: Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance>
Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

Triggers: Rules: TR_<TableName>_<action>
Examples: TR_Orders_UpdateProducts
Notes: The use of triggers is discouraged

Indexes: Rules: IX_<TableName>_<columns separated by _>
Examples: IX_Products_ProductID

Primary Keys: Rules: PK_<TableName>
Examples: PK_Products

Foreign Keys: Rules: FK_<TableName1>_<TableName2>
Example: FK_Products_Orderss

Defaults: Rules: DF_<TableName>_<ColumnName>
Example: DF_Products_Quantity

Columns: If a column references another table’s column, name it <table name>ID
Example: The Customers table has an ID column
The Orders table should have a CustomerID column

General Rules: Do not use spaces in the name of database objects
Do not use SQL keywords as the name of database objects
In cases where this is necessary, surround the
object name with brackets, such as [Year]
Do not prefix stored procedures with ‘sp_’2
Prefix table names with the owner name3


• Each table must have a primary key
o In most cases it should be an IDENTITY column named ID
• Normalize data to third normal form
o Do not compromise on performance to reach third normal form. Sometimes, a little de-
normalization results in better performance.
• Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead
• In VARCHAR data columns, do not default to NULL; use an empty string instead
• Columns with default values should not allow NULLs
• As much as possible, create stored procedures on the same database as the main tables they
will be accessing


• Use upper case for all SQL keywords
• Indent code to improve readability
• Comment code blocks that are not easily understandable
o Use single-line comment markers(--)
o Reserve multi-line comments (/*.. ..*/) for blocking out sections of code
• Use single quote characters to delimit strings.
o Nest single quotes to express a single quote or apostrophe within a string
For example, SET @sExample = 'SQL''s Authority'
• Use parentheses to increase readability
o WHERE (color=’red’ AND (size = 1 OR size = 2))
• Use BEGIN..END blocks only when multiple statements are present within a conditional code
• Use one blank line to separate code sections.
• Use spaces so that expressions read like sentences.
o fillfactor = 25, not fillfactor=25
• Format JOIN operations using indents
o Also, use ANSI Joins instead of old style joins4
• Place SET statements before any executing code in the procedure.


• Optimize queries using the tools provided by SQL Server5
• Do not use SELECT *
• Return multiple result sets from one stored procedure to avoid trips from the application server
to SQL server
• Avoid unnecessary use of temporary tables
o Use 'Derived tables' or CTE (Common Table Expressions) wherever possible, as they
perform better6
• Avoid using <> as a comparison operator
o Use ID IN(1,3,4,5) instead of ID <> 2
• Use SET NOCOUNT ON at the beginning of stored procedures7
• Do not use cursors or application loops to do inserts8
o Instead, use INSERT INTO
• Fully qualify tables and column names in JOINs
• Fully qualify all stored procedure and table references in stored procedures.
• Do not define default values for parameters.
o If a default is needed, the front end will supply the value.
• Do not use the RECOMPILE option for stored procedures.
• Place all DECLARE statements before any other code in the procedure.
• Do not use column numbers in the ORDER BY clause.
• Do not use GOTO.
• Check the global variable @@ERROR immediately after executing a data manipulation statement
(like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
o Or use TRY/CATCH
• Do basic validations in the front-end itself during data entry
• Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type
conversions etc., to the front-end applications if these operations are going to consume more
CPU cycles on the database server
• Always use a column list in your INSERT statements.
o This helps avoid problems when the table structure changes (like adding or dropping a

• Minimize the use of NULLs, as they often confuse front-end applications, unless the applications
are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
o Any expression that deals with NULL results in a NULL output.
o The ISNULL and COALESCE functions are helpful in dealing with NULL values.
• Do not use the identitycol or rowguidcol.
• Avoid the use of cross joins, if possible.
• When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition,
if possible. This reduces error possibilities.
• Avoid using TEXT or NTEXT datatypes for storing large textual data.9
o Use the maximum allowed characters of VARCHAR instead
• Avoid dynamic SQL statements as much as possible.10
• Access tables in the same order in your stored procedures and triggers consistently.11
• Do not call functions repeatedly within your stored procedures, triggers, functions and batches.12
• Default constraints must be defined at the column level.
• Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword,
as these results in an index scan, which defeats the purpose of an index.
• Define all constraints, other than defaults, at the table level.
• When a result set is not needed, use syntax that does not return a result set.13
• Avoid rules, database level defaults that must be bound or user-defined data types. While these
are legitimate database constructs, opt for constraints and column defaults to hold the database
consistent for development and conversion coding.
• Constraints that apply to more than one column must be defined at the table level.
• Use the CHAR data type for a column only when the column is non-nullable.14
• Do not use white space in identifiers.
• The RETURN statement is meant for returning the execution status only, but not data.

1) Group related table names:


2) The prefix sp_ is reserved for system stored procedures that ship with SQL Server. Whenever SQL
Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the
master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the
owner. Time spent locating the stored procedure can be saved by avoiding the "sp_" prefix.

3) This improves readability and avoids unnecessary confusion. Microsoft SQL Server Books Online
states that qualifying table names with owner names helps in execution plan reuse, further boosting

False code:
FROM Table1, Table2
WHERE Table1.d = Table2.c

True code:
FROM Table1
INNER JOIN Table2 ON Table1.d = Table2.c

5) Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL
commands to analyze your queries. Make sure your queries do an "Index seek" instead of an "Index
scan" or a "Table scan." A table scan or an index scan is a highly undesirable and should be avoided
where possible.

6) Consider the following query to find the second highest offer price from the Items table:

FROM Products
FROM Products
ORDER BY Price Desc

The same query can be re-written using a derived table, as shown below, and it performs generally
twice as fast as the above query:

FROM Products

7) This suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and
SELECT statements. Performance is improved due to the reduction of network traffic.

8) Try to avoid server side cursors as much as possible. Always stick to a 'set-based approach' instead
of a 'procedural approach' for accessing and manipulating data. Cursors can often be avoided by using
SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. For a WHILE loop to
replace a cursor, however, you need a column (primary key or unique key) to identify each row

9) You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you
have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. So, if you don't have to
store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000) datatype instead.

10) Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at
runtime. IF and CASE statements come in handy to avoid dynamic SQL.

11) This helps to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:

• Keep transactions as short as possible.
• Touch the minimum amount of data possible during a transaction.
• Never wait for user input in the middle of a transaction.
• Do not use higher level locking hints or restrictive isolation levels unless they are absolutely

12) You might need the length of a string variable in many places of your procedure, but don't call the
LEN function whenever it's needed. Instead, call the LEN function once and store the result in a
variable for later use.

13) IF EXISTS (SELECT 1 FROM Products WHERE ID = 50)
Instead Of:

14) CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use
VARCHAR(100) in this situation. Variable-length columns have very little processing overhead
compared with fixed-length columns.

Friday, October 24, 2008

Cookie and Session variable in coldfusion

>>Download Source
Using Cookies

You've undoubtedly used cookies on the Web in some form or another—if you've ever logged in to a site or checked a "remember me" check box, for example. Sometimes, just visiting a site sets a cookie. Using cookies has a few advantages, such as being able to access them by using languages like JavaScript, ASP, and PHP. Cookies are stored on the site visitors' machines by their web browser (in a text file). Because the cookie information is stored on the user's machine rather than the server, cookies offer the performance advantage of not taking up any server resources.

It is important to note, especially if you are accessing cookies using a case-sensitive language such as JavaScript, that ColdFusion creates cookies using capital letters.
Cookies also have some limitations; they can hold only simple values (such as strings, numbers, and Booleans), they can be disabled easily by a user, their size is limited (4KB each), the number of cookies that can be set on a user's machine for a single domain is limited (20), and because they're in a text file on the client, they are not generally very secure.
To further limit the number of cookies you can set, ColdFusion automatically sets some cookies for its own use (CFID and CFTOKEN), reducing the number of available cookies you can set to 18 per host. Both CFID and CFTOKEN are used to track users throughout your site. When a user comes to your site, these two cookies are set and allow ColdFusion to identify that user's session. These two variables are needed for using Session or Client variables. Note that cookies are domain-specific; you cannot read cookies set by other websites, and they cannot access yours either.
There is another cookie, JSESSIONID, that ColdFusion uses in lieu of the CFID/CFTOKEN combination, if J2EE session variables are enabled in the ColdFusion Administrator. The JSESSIONID variable is set when a session is created and can be used to share Session variables between ColdFusion, JSP, and Java servlets. These topics are beyond the scope of this book, but there are some excellent resources at and It is important to note that when J2EE sessions are enabled in the ColdFusion Administrator, the CFID and CFTOKEN variables are not set, so you are able to set 19 cookies per host instead of 18. One other difference is that J2EE sessions expire as soon as users closes their browser windows, which is not the case with traditional ColdFusion session management.
You can use ColdFusion to set a cookie on a user's machine in one of two ways: either by directly setting a variable in the Cookie scope by using <cfsetCookie.UserID = 14>, or by using the <cfcookie> tag. When using the <cfcookie> tag, you are able to set extra attributes, such as when the cookie expires, whether or not the cookie should be "secure," and the domain and path that the cookie is valid for. Here's an example of the <span style="font-weight:bold;"><cfcookie> tag:</span>

<cfcookie name="UserID" value="14">

The preceding snippet will create a cookie named UserID with a value of 14. Because you haven't defined when this cookie will expire, it will reside in the user's memory until the browser is closed. At this point the cookie will be deleted. This is often referred to as a session cookie because it lasts for only a single user session. If you define when the cookie expires, it will be written to a text file (cookies.txt if you are using Netscape, or individual files if you are using Internet Explorer) on the user's computer and will remain there for the specified time. CFML allows you to define when the cookie expires in the following ways:
An exact date: <cfcookie name="xyz" expires="12/3/2004">
A fixed number of days: <cfcookie name="xyz" expires="100">
NOW: <cfcookie name="xyz" expires="NOW">
NEVER: <cfcookie name="xyz" expires="NEVER">
If you specify an expiration date of NOW, the cookie is deleted from the user's cookies.txt file or individual file on the user's local computer as soon as the <cfcookie> tag executes. Specifying an expiration date of NEVER will set the cookie to expire in 30 years.
Because cookies can be set to persist on a client machine for any duration, they are an excellent way to build "remember me" functionality into an application so users don't have to log in to a site each time they visit. Also, because cookies are stored on the client's machine, you don't have to worry about taking up large amounts of server memory or filling a database.
In the following simple example, you will see how you can set cookies that persist only as long as the user's browser remains open, and how to create cookies that last for a certain number of days.
1.Use Dreamweaver to create a file called getName.cfm within a new subfolder called Ch7 in your cfbook site, then enter the following code:

2.<cfform action="showName.cfm" method="post">
3. <cfinput type="Text" name="Name" value="" required="yes"
4. message="Please enter your name.">
5. <input type="Submit" value="Submit">
There is nothing interesting about the preceding code. You are simply creating a form to allow users to type in their names.
7.Create another file called showName.cfm within the same folder containing the following code:
8.<cfparam name="Form.Name" type="string">
9.<cfcookie name="Name" value="#Form.Name#">
10.<cfoutput>Your name is: #Cookie.Name#<br /></cfoutput>
11.<a href="anotherPage.cfm">Another page</a>
In this step, you are setting a cookie called Name to the value of the form field that the user filled out. You then display the value of the cookie along with a link to anotherPage.cfm.
12.Create a third file, anotherPage.cfm, and enter the following line into it:
Now test your files. Navigate to the getName.cfm file, enter your name in the text box, and click the Submit button. You name will be stored in a cookie, and you will see the cookie's value output to the window in showName.cfm.
If you then click on the hyperlink at the bottom of showName.cfm, you will go to anotherPage.cfm, which also displays the value of the cookie; you didn't have to explicitly pass the variable to anotherPage.cfm because it was able to retrieve it from the Name.cookie. You can even leave this page, navigate to another site, then return to anotherPage.cfm, and you will still see the name you entered in getName.cfm.
Because you didn't specify an expiration time for this cookie, it will remain in your memory until you close your browser. Therefore, if you close your web browser, open a new one, then navigate back to anotherPage.cfm, you will receive an "Element NAME is undefined in COOKIE" error; the cookie is no longer on your system.
This happens because the cookie expires as soon as the browser is closed—it was never even written to the disk. If you change the code in showName.cfm to the following snippet (showName2.cfm), the cookie will be saved on the user's hard drive and would still be defined after the browser was closed and reopened:
<cfparam name="Form.Name" type="string">
<cfcookie name="Name" value="#Form.Name#" expires="7">
<cfoutput>Your name is: #Cookie.Name#<br /></cfoutput>
<a href="anotherPage.cfm">Another page</a>
By adding the expires attribute to the <cfcookie> tag as shown, you are telling ColdFusion to save this cookie on the user's system for seven days, after which time it will expire. You could also set an actual date for the cookie to expire on.
Cookies can be useful if you want to track users around a site. You might want to create a snippet of code that creates a record of which users were viewing which pages and inserts into a database. You could then paste that snippet at the bottom of each web page and build your own simple stats package. You would be able to query the database and see how many times a certain page was browsed on a particular day, or track to see which pages were viewed by a particular user, and in which order.

Session Variables
There are a few important differences between Session variables and cookies. Cookies are stored on the user's computer, whereas Session variables are stored in the server's memory. Cookies are limited to simple values and lists, but Session variables can hold Recordsets, arrays, objects, or XML documents. Session variables use slightly more resources than cookies, but have fewer limitations.
One very important consideration when using Session variables is that you must be careful to "lock" the variable every time you read or change its value in versions of ColdFusion prior to MX. ColdFusion is able to tell which user the Session variables belong to by using the CFID and CFTOKEN (or JSESSIONID) cookies, so for sessions to work, a user's cookies must be enabled.
In ColdFusion MX and MX 6.1, you do not have to lock access to session variables for memory threading issues, but you do have to lock them anytime there is a possibility of a race condition. (A race condition is anytime one piece of code could change the value of a variable at the same time another piece of code tries to access it, which could result in invalid data being read.) Race conditions can be prevented if locks are used appropriately. This problem holds true with any of the variable scopes that are persisted in server memory: Session, Application, and Server. The main differences among these three scopes are as follows:
Session variables are tied to a specific user.
Application variables are available to all users of a certain application.
Server variables are available to all users in all applications on the server.
Because Session variables are stored within the server's memory, they have a much shorter lifespan than Client variables or Cookies. Session variables by default are stored in the server's memory for only 20 minutes. If a client is idle on your site (no page requests) for more than 20 minutes, the session variables will be deleted from the server's memory and will no longer exist.
As you saw in Chapter 1, Session variable timeouts (along with Application variables) can be set in the ColdFusion Administrator on the Memory Variables page in the Server Settings section. This is where you can disable or enable these two scopes entirely, or else set their default timeout and maximum timeout values. You can also define timeout values by using the <cfapplication> tag in your site, if you want to customize timeout values on a site-by-site basis.
It is also important to note that, if the timeout value within your <cfapplication> tag exceeds the maximum timeout value in the ColdFusion Administrator, the value from the ColdFusion Administrator will be used instead (the shortest of the two values is always the one used).
Using the <cflock> Tag
Let's look at a simple example showing how you use the <cflock> tag when reading or modifying the contents of a Session variable. This example locks access to the entire session scope by using the <cflock> tag's scope attribute. If few variables need to be locked, a better practice is to use the <cflock> tag's name attribute (all locks with the same name "obey" each other).
1.In Dreamweaver, open the showName.cfm file you made earlier in this chapter. Remove any existing code and add the following (see showName3.cfm in the code download):
2.<cfparam name="Form.Name" type="string">
3.<cflock timeout="15" type="exclusive" scope="session">
4. <cfset Session.Name = Form.Name >
6.<cflock timeout="10" type="readonly" scope="session">
7. <cfoutput>#Session.Name#</cfoutput>
You are using <cflock> tags to eliminate the chances of users seeing a different user's session variables. When you set a Session variable, you must set the lock type to "exclusive" so that no other threads are able to read or write to this variable while the current thread is accessing it.
9.Now view the getName.cfm template again, fill out the form, and submit it. You will either see the name displayed on the screen, as in the previous example, or you will get an error similar to the one shown in Figure 7-1. If you receive this error, you need to create an Application.cfm template in the same folder (or parent folder) and enable the session scope by adding the following code to it:
10.<cfapplication name="cfbook" sessionmanagement="yes">

1.Retest getName.cfm, and this time you should see your name being output to the browser. If you still receive an error message, check the ColdFusion Administrator and make sure that the Session scope hasn't been disabled.
Session variables, like Cookies, last a certain amount of time. You saw earlier in this chapter that it's possible to close the browser and navigate back to the anotherPage.cfm template and still view the value of the Cookie. The same holds true with Session variables, assuming they haven't expired. You can get it to persist for a prescribed length of time like so:
Open anotherPage.cfm again and change the code to the following (see anotherpage2.cfm in the code download):
<cflock scope="session" timeout="10" type="readonly">
Now close your browser, reopen it, and navigate back to the anotherPage.cfm template. You will still see your name displayed on the screen (unless the variables have timed out already). Even if the user closes the browser, the user's Session variables still remain in the server's memory until they time out.
But what if you wanted to have users' sessions end once they close their browsers? You need to set the Cookie.CFID and Cookie.CFTOKEN cookies to expire when a user's browser is closed. To achieve this, add the following code into your Application.cfm file below the current line (see Application2.cfm in the code download):
<cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")>
<cfcookie name="CFID" value="#Cookie.CFID#">
<cfcookie name="CFTOKEN" value="#Cookie.CFTOKEN#">
This code checks to see if variables named Cookie.CFID and Cookie.CFTOKEN already exist. We mentioned earlier in this chapter that these two variables are set automatically by ColdFusion and are used to track users throughout their visit to the site. If these two variables exist, you then create two cookies with the exact same names and values.
Although it may not be immediately obvious why you might bother doing this, if you examine the preceding code, you'll notice that you haven't defined an expiration date for these cookies. As we mentioned earlier in this chapter, if you do not define an expiration date, the cookies will not be saved to the users' disks and will expire when the users close their browsers.
So effectively, the users' CFID and CFTOKEN values will now expire when their values are closed. Note that this doesn't actually destroy the users' Session variables; it simply "disconnects" them from the current session. All of their session variables will still remain in server memory (though inaccessible unless the same CFID/CFTOKEN combination are passed) until they time out and are destroyed.
This technique can be very useful for logging out users from an application when their browsers close. If you weren't using the preceding code listing and a user exited the browser without hitting a logout button, the user would still be logged in if the Session variables weren't timed out. If that user (or a different user entirely) were to reopen the browser and navigate back to your site, the new visitor could still use the logged in functionality of the site.
Session variables aren't limited to simple strings. For an e-commerce site, you could create a user's shopping cart and store it in a Session variable instead of storing carts in a database. This has the benefit that, if a user abandons the cart and leaves your site, the Session variable will be deleted when the session times out. If you stored all the items in the user's cart within a database, you would need to run a cleanup routine to delete carts not modified in the past day or two.

>>Download Source

Tuesday, October 21, 2008

Reduce Blogger's feed size below FeedBurner's 512K limit?

Reduce Blogger's feed size below FeedBurner's 512K limit?

FeedBurner will not process an original feed from your blog if it is greater than 512K in size. (This limitation only applies to the actual size of the feed file itself and does not include any images or media files you may have linked to or embedded in your posts.) To get a feed larger than 512K back down to size on Blogger, you need to add a parameter to the address for your original feed's URL — the one that FeedBurner checks for updates in order to keep your FeedBurner feed current — that tells Blogger to shorten it to a specific number of posts. To add this parameter:

1. Sign in to your FeedBurner account.
2. Click the title of your FeedBurner feed on the My Feeds page.
3. On the feed management page that appears, click the Edit Feed Details... link just below your feed's title.
4. In the Original Feed Address field, change the address listed there from an example like the following:


to the following format:


(Note that max-results sets the number of posts you want to have appear in your FeedBurner feed. Any integer value between 1 and 500 is permitted.)

5. Click Save Feed Details. Your changes are immediately applied and FeedBurner re-burns the newly shortened (or lengthened) feed.

podcasters: Don't ever want an episode to drop off of your feed? Set this value to 500. (What happens on episode 501? Pandemonium!)

Bloggers: Do you write lengthy posts and come up against FeedBurner's 512K limit to total feed size? You will want to set max-results to a small number in order to keep the feed file size under control. You may need to experiment with the max-results setting to get the ideal feed size. How to tell the filesize of your feed? Enter its address into and look at the report it returns; there should be a value in kilobytes for the size of your feed in an "uncompressed" format.

Friday, October 17, 2008

Joins in Sql Server

Joins in Sql Server:

Joins actualy Performs Two or more Tables combined into a single result set.
Joins can also fetch with different dataBase,different Server also.

There are 5 types of joins available in sql server

1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join

1) inner Join : Two tables having the common column values go with inner join.

select * from emp inner join empsalary

2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid

3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid

4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion

select * from emp cross join employee

Self join :

Self join can check within the table called self join.

Cursors in SQL:

Cursors in SQL:

A cursor is a database object that help in naccessing and manupulating data in given result set.
The main advantage is that we can process the data row-by -row.

There are four types cursors.

1. Static:

It alwys displays the result set as it was when the cursor opened.
statis cursors are always read-only.

2. Dynamic:
Dynamic cursors are opposite of static cursors.It refers all changes made to the rows in their result set when scrolling through the cursor.

3. Forward- Only:
The forward - only cursors does not support ; it supports
only frtching the rows serially from the start to end of the cursors.

The keyset is the set of key values from all the rows that
qualified for the slect statement at the time the cursor was opened.

views in sql

Introduction to Views

Views are generally used to focus, simplify, and customize each user's perception of the database. You can use a view as a security mechanism by allowing a user to access data through the view without granting the user permission to directly access the underlying base tables of the view. You can also use views to improve performance
A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current database or other databases.
A view is a SELECT statement that’s stored with the database. To create a view you use CREATE VIEW statement. You can think of view as a virtual table that consists only of the rows and the columns specified in its CREATE VIEW statement. The table or tables listed in the FROM clause are called the case tables for the view. Since the view refers back to the base tables, it doesn’t store any data itself, and it always reflects the most current data in the base tables.
To use a view, you refer to it from another SQL statement. When you use any view in the FROM clause instead of table, as a result this SELECT statement extracts its result set from the virtual table that the view represents.
Because a view stored as an object in a database, it can be used by anyone who has access to the database.

--To Create a view

--To Execute a View.

A view can also be referred to as a viewed table because it provides a view to the under-lying base tables.
Advantages of Views

The data that you access through a view isn’t dependent on the structure of the database. Suppose a view refers to a table that you have decided to divide into two tables. To accommodate this change you simply modify the view, you don’t have to modify any statements that refer to the view. That means users who query the database using the view don’t have to be aware of the change in the database structure, and application programs that use the view don’t have to be modified.

You can also use views to restrict access to a database. To do that you include just the columns and rows you want to a user to have access in the view. Then you let the use access the data only through views.

Create and Manage Views
Now you have fair knowledge of views and how they work now lets get into more practle version.
CREATE VIEW view_name [(column_name_1 [,column_name_1]…)]


You use CREATE VIEW statement to create a view. The name you give the view must not be the same as the name of any existing table or view. The SELECT statement can refer to as many as 256 tables and can use any valid combination of joins, unions, or sub queries. You can even create a view based on another view rather than on a table, called a nested view. SQL Server views can be nested up to 32 levels. The SELECT statement for a view can’t include an INTO clause and it can include an ORDER BY clause only if the TOP keyword is used. To sort the rows in a view you have to include the ORDER BY clause in the SELECT statement that uses it. You can name the columns in view by coding a list of names in parentheses following the view or by coding the new names in SELECT clause. A column must be named if it’s calculated from other columns otherwise the name from he base table can be used. You can use WITH ENCRYPTION clause to keep users from examining the SQL code that defines the view. You can use WITH SCHEMABINDING clause to bind a view to the database schema. Then you can’t drop the tables on which the view is bases or modify the tables in a way that would affect the view. If you include the WITH SCHEMABINDING clause then you can’t use the all columns operator (*) in the SELECT statement. In addition you must qualify the names of the table s and view in the FROM clause with the name of the database owner.
You can use WITH CHECK OPTION clause to prevent a row from being updated through a view if it would no longer be included in the view. The WITH CHECK OPTION clause prevents a row in a view from being updated if that would cause the row to be excluded from the view.
How to create an updatable view

Once you create a view, you can refer to it in a SELECT statement just like any other table. In addition you can refer to it in INSERT, UPDATE and DELETE statements to modify an underlying table. To do that view must be updatable.

To create an updatable view you must meet he following requirements.

The select list can’t include a DISTINCT or TOP clause.
The select list can’t include an aggregate function.
The select list can’t include a calculated value.
The SELECT statement can’t include a GROUP BY or HAVING clause.
The view can’t include the UNION operator.

If a view is not updatable it’s called a read-only view.
How to delete or modify a view

To delete a view you use the DROP VIEW statement. In this statement you simply name the view you want to delete. Like the other statements for deleting database objects, this statement deletes the view permanently.

To modify a view you can use the ALTER VIEW statement. The syntax of this statement is the same as the syntax of the CREATE VIEW statement. If you understand the CREATE VIEW statement then you won’t have any trouble using the ALTER VIEW statement. Instead of using the ALTER VIEW statement to modify a view you can delete the view and the recreate it. If you have created stored procedures triggers that depend on the view or if you have modified the permissions for the view you should know that those stored procedures, triggers and permissions are deleted when the view is deleted. If that’s not what you want you should use ALTER VIEW statement instead.

The syntax of the DROP VIEW statement
DROP VIEW view_name

The syntax of the ALTER VIEW statement
ALTER VIEW view_name [(column_name_1 [, column_name_2]…)]

If you delete a table, you should also delete any views that are based on that table. Otherwise an error will occur when you run a query that refers to one of those views. To find out what views are dependent on a table display the table’s dependencies.

ALTER VIEW isn’t an ANSI-standard statement. Although it’s supported on other SQL based systems, its behavior on each system is different.
How to use views

You have seen how to use views in SELECT statements to retrieve data from one or more base tables. But you can also use views to update base tables.

How to update rows through a view
To update a view you simply name the view that refers to the table in the UPDATE statement.
UPDATE V_Shashi SET FN_NAME = ‘Shashi Kant Ray’

How to insert rows through a view
To insert rows through a view you use the INSERT statement as the way you use it to insert rows into table.
INSERT INTO V_Shashi values (‘shashi’,’satyam’)

How to delete rows through a view
To delete rows from a view you simply name the view that refers to the table in the delete statement.

To get the Source of view query the following information schema views.


difference between MSSQL and MYSQL

Some of the difference between MSSQL and MYSQL Syntax are as follows:

1) MSSQL support UDD(User Defined DataTypes), MYSQL does not support UDD

2) Use of -- for comment a line purpose in MSSQL, in otherside use /* */ or -- comments

3) Use prefix @symbol for variable in MSSQL, whereas in MYSQL remove @symbol.
e.g. declare @test int -- MSSQL Syntax
declare test int; --MYSQL Syntax

4) End marker(;) not required in MSSQL but in MYSQL all statement end with marker (;)

5) In MSSQL use SELECT getdate() for getting today's date , in MYSQL use SELECT now()

6) All queries should be available in Begin tran and Commit(Rollback) tran loop in MSSQL,
whereas MYSQL Start transaction and commit(Rollback) transaction.

7) Getting Top most record in MSSQL, Use SELECT TOP1 from Table_Name where
In MYSQl SELECT from Table_Name where limit 1;

8) For viewing table information use Sp_help Table_name in MSSQL, but in MYSQL use show table_name;

9) Use of + for concatenate two stings in MSSQLL whereas MYSQL SELECT concat('a','/','b')

10)In MSSQL SP_HELPTEXT for viewing the content of sp in otherside use the following syntax.
select routine_definition from information_schema.routines
where specific_name =
and routine_schema = ;

11)For assigning value to variable use Set @variable1 = 'test' in MSSQL,
but in MYSQL use Set variable =1 or set 1 into variable1;

12)In MSSQl, use of IF statement syntax,
Whereas in MYSQL,
if Then
leave sp_label;
end if;

13) In MSSQL Use RETURN Statement for returning values but in MYSQL use leave sp_level;

14) The convert datatype Syntax in MSSQL is select convert(type,expression) whereas in MYSQL select convert(expression,type);

15) In MSSQL the Dynamic SQL statement is
SELECT @a ='SELECT * from Table_Name where
execute (@a)
Prepare stmt1 from 'select * from Table_Name where column_name =?'
Set @a ='Value';
execute stmt using @a;

.NET/SQL Server job interview questions

SQL queries most asked in .NET/SQL Server job interviews. These tricky queries may be required in your day to day database usage.
This article demonstrates some commonly asked SQL queries in a job interview. I will be covering some of the common but tricky queries like:-
(i) Finding the nth highest salary of an employee.
(ii) Finding TOP X records from each group.
(iii) Deleting duplicate rows from a table.
NOTE : All the SQL mentioned in this article has been tested under SQL Server 2005.
(i) Finding the nth highest salary of an employee.
Create a table named Employee_Test and insert some test data as:-

CREATE TABLE Employee_Test
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

It is very easy to find the highest salary as:-

--Highest Salary
select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-
--3rd Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

The result is as :- 1200
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)
--nth Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

(ii) Finding TOP X records from each group
Create a table named photo_test and insert some test data as :-

create table photo_test
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)

insert into photo_test values(17,15,'photo/bb1.jpg');
insert into photo_test values(17,16,'photo/cricket1.jpg');
insert into photo_test values(17,17,'photo/base1.jpg');
insert into photo_test values(18,18,'photo/forest1.jpg');
insert into photo_test values(18,19,'photo/tree1.jpg');
insert into photo_test values(18,20,'photo/flower1.jpg');
insert into photo_test values(19,21,'photo/laptop1.jpg');
insert into photo_test values(19,22,'photo/camer1.jpg');
insert into photo_test values(19,23,'photo/cybermbl1.jpg');
insert into photo_test values(17,24,'photo/F1.jpg');

There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records).
Now, if you want to select top 2 records from each group, the query is as follows:-

select pgm_main_category_id,pgm_sub_category_id,file_path from
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

pgm_main_category_id pgm_sub_category_id file_path
17 15 photo/bb1.jpg
17 16 photo/cricket1.jpg
18 18 photo/forest1.jpg
18 19 photo/tree1.jpg
19 21 photo/laptop1.jpg
19 22 photocamer1.jpg
(iii) Deleting duplicate rows from a table
A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-
(a) Using a temporary table.
(b) Without using a temporary table.
(a) Using a temporary or staging table
Let the table employee_test1 contain some duplicate data like:-

CREATE TABLE Employee_Test1
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1: Create a temporary table from the main table as:-
select top 0* into employee_test1_temp from employee_test1
Step2 : Insert the result of the GROUP BY query into the temporary table as:-
insert into employee_test1_temp
select Emp_ID,Emp_name,Emp_Sal
from employee_test1
group by Emp_ID,Emp_name,Emp_Sal
Step3: Truncate the original table as:-
truncate table employee_test1
Step4: Fill the original table with the rows of the temporary table as:-
insert into employee_test1
select * from employee_test1_temp
Now, the duplicate rows from the main table have been removed.
select * from employee_test1
gives the result as:-
Emp_ID Emp_name Emp_Sal
1 Anees 1000
2 Rick 1200
3 John 1100
4 Stephen 1300
5 Maria 1400
6 Tim 1150
(b) Without using a temporary table

;with T as
select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
from employee_test1

from T
where rank > 1

The result is as:-

Emp_ID Emp_name Emp_Sal
1 Anees 1000
2 Rick 1200
3 John 1100
4 Stephen 1300
5 Maria 1400
6 Tim 1150

I hope that these queries will help you for Interviews as well as in your day database activities.

Few Interesting Questions and concepts
There are 3 tables Titles, Authors and Title-Authors (check PUBS db). Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.

SELECT authors.au_lname, COUNT(*) AS BooksCount FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titles.title_id = titleauthor.title_id GROUP BY authors.au_lname ORDER BY BooksCount DESC
Write a SQL Query to find first day of month?

There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get the difference of temperature among each other for seven days of a week?

SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference FROM day_temp a INNER JOIN day_temp b ON a.dayid = b.dayid + 1
or this query
Select, from temperature a, temperature b where

There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.

SELECT empid, SUM(salary) AS salaryFROM employeeGROUP BY empid WITH ROLLUP ORDER BY empid

Update With Case
EmpID EmpName Gender
1 Raja Male
2 Rani Female
In the above table using one query u need to change Gender male to female and who is female need to change male.
UPDATE Emp1 SET Gender=CASE Gender WHEN ‘Male’ THEN ‘Female’WHEN ‘female’ THEN ‘Male’END;
Query to find the maximum salary of an employee
Select * from Employee where salary = (Select max(Salary) from Employee)
Query to Find the Nth Maximum Salary
Select * From Employee E1 Where (3-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
Query to Find the 2nd Maximum Salary
select max(salary) as Salary from Emplo where salary!=(select max(salary) from Emplo)
SELECT MAX(E1.salary) FROM emplo E1 , emplo E2WHERE E1.salary< E2.salary

Thursday, October 16, 2008

Flex Tutorial

Flex Tutorial

Flex is an open source program designed to automatically and quickly generate scanners, also known as tokenizers, which recognize lexical patterns in text. Flex is an acronym that stands for "fast lexical analyzer generator. " It is a free alternative to Lex, the standard lexical analyzer generator in Unix-based systems. Flex was originally written in the C programming language by Vern Paxson in 1987.

Lex is proprietary but versions based on the original code are available as open source. These include not only Flex but components of OpenSolaris and Plan 9. Lexical analysis is the processing of character sequences such as source code to produce symbol sequences called tokens for use as input to other programs such as parsers.

What is Flex? Why Use It?

There seems to be some confusion as to why anyone would bother using Flex. I can understand this to a degree since I felt the exact same way when I first heard about Flex. But logically, there must be a reason why Adobe decided to continue it as one of their products; and that is exactly what I plan to explain here.

In a nutshell, Flex was designed in order to give Flash Developers an edge when developing Rich Internet Applications (RIAs). Although RIAs can be built from within Flash, there are various limitations, especially when it come to a developers time. I’m not going to get into the specifics, but as you become familiar with Flex, you’ll begin to understand why this is.

What makes a up a Flex Application?

A Flex application at it’s very basic level is a Flash .swf file embedded in a generated HTML file. Since Flex outputs it’s data as a .swf, this allows you to utilize everything that Flash Player is famous for; dynamic animations, sound and video handling, and of course, the Flash Drawing API. The HTML that Flex spits out has the necessary JavaScript code in order to detect whether the user has the Flash Player installed, or whether they need to update to the current player.

One of the major reasons why Flex is becoming more and more popular is because it utilizes ActionScript 3.0. In fact, developers we’re creating AS 3.0 applications in Flex even before AS 3.0 was released for Flash Developers. But, since Flash CS3 was released, AS 3.0 has become the new standard, and for me at least, is why developing in Flex has become even more exciting.

How is a Flex Application created?
One important thing to note, is that the Flex environment does not use a Timeline. This does not mean that it is limited. Rather, Flex has been developed more for programmers and less for designers. It ends up meeting somewhere in the middle, which is exactly where I found myself as a Designer/Programmer, whatever. Again, this will become apparent as you begin actually developing in Flex.

You design a Flex Application by selecting pre-defined visual components (i.e. Button, ComboBox, ListBox, DataGrid, etc) that you’re most likely familiar with from Flash. If you’ve never used components before, they’re wonderful. Although some will tell you that you can create far smaller (in size) components by developing them on your own, and while that may actually be true, the pre-defined components work well and are highly customizable – especially the latest set released with Adobe Creative Suite 3:

You then arrange your components on the stage in a orderly, easy to use, manageable user interface. There are various Containers that allow you to align your components however you’d like. As an example of one of these containers, there is one named the VBox. This Container tells everything that lives within it to align vertically to one another – extremely usefully when developing Forms.

You’ll then use be able to use styles and skins that will customize the entire look and feel of your application. CSS Formatting and the complete customization of the visual components (as I mentioned above) will make your Flex Application stand out above the rest.

Now that you have your visuals in place and you like the way your application looks, you can start to utilize ActionScript 3.0. Assign event handlers and create or import ActionScript functions or classes to make your Flex Application live up to it’s full potential. This will allow you to react to what the user does and to events that are generated by Flex itself. Also, and this is a HUGE also, you’re able to connect to ColdFusion and other Flex Data/Communication Services. Remember this one thing when you’re contemplating whether or not Flex is right for you: Flex and ColdFusion are meant for each other.

Now it’s time to publish your application and present it to your client or the world.
Honestly, there is so much more that could have been elaborated on in this article, but that’s what the later tutorials are there for. This was meant as an introduction or sorts to Flex in a Flash world. If you haven’t given it a shot, why not test it out for yourself. Until more tutorials are released here on, please visit the following links in order to satisfy your curiosity.

With Web users expecting richer and more complex interfaces, Rich Internet Applications (RIAs) are seeing a huge increase in popularity. Adobe Flex is the tool of choice for many web developers when it comes to building RIAs.

Flex used to be one of those technologies that was only used by large corporate organisations -- the first release of Flex was priced at around US$15,000 per CPU (a tad expensive for most developers) Since then, Flex has been released as open source software. Talk about a complete turnaround!

Flex is now in version 3.0, and runs on the Adobe Flash Player 9 runtime. Flex 3.0 has been such a success that Flex 4.0 has been announced for release in 2009. As a developer, getting in early and learning all you can now is a good idea -- standing out in the Flex community will soon become extremely difficult.

Developers are picking up Flex with great speed. One of the reasons for this is that Flex programming is relatively easy to learn if you're already familiar with XHTML. Of course, there's always a transition period when you're getting used to a new environment, but learning Flex won't take long!

Another drawcard is that Flex applications can be developed to run on a user's desktop thanks to the wonders of the Adobe AIR (Adobe Integrated Runtime) platform. Now you can develop and launch an RIA that runs in a web browser and as a desktop application. Deploying an application to a user's desktop with Adobe AIR is easy -- all that users need to do is click a link in the web browser to install the AIR runtime and your RIA on their machine. Now that's quick deployment!

That's enough rambling; let's learn the basics of what Flex is all about.
Why Use Flex?

If you're considering building a RIA, you have a few choices of technology, including Flex, Ajax, and Microsoft Silverlight. If you look at these options objectively, the development effort required for each (and the resulting user experience) is roughly the same. One of the benefits of Flex is its reach -- the Adobe Flash Player, upon which Flex applications run, is already installed on about a billion computers wordwide!

Of course, Ajax also uses technologies that are installed on almost every computer in the world -- JavaScript, XHTML, and CSS. One of the downfalls of Ajax, however, is that cross-browser compatibility can be difficult to achieve. What might work in one browser (for example, Firefox) might not work in another (such as Internet Explorer), so the debugging process has the potential to become difficult and long-winded.

Microsoft Silverlight, on the other hand, is similar to Flex in that it runs on a browser plugin. Silverlight, however, has yet to reach the installed userbase of the Flash player.

If you're just starting out with building RIAs, you should definitely try all of them to see which one you like best and find easiest to work with -- each of Flex, Silverlight and Ajax has its advantages and disadvantages. In my opinion, though, Flex is definitely the best RIA development technology available. Additionally, if you're a fan of the Adobe Creative Suite, it's good to know that many of these tools (Flash, Photoshop, Illustrator, Fireworks and so on) have Flex component integration built into them, which is a bonus no matter how you look at it.
Overview of the Flex Framework

A lot of people steer clear of the Flex framework because they think it's complicated. But generally speaking, a framework is just a set of reusable classes that can work together to provide a base for an application.

Take a house as an analogy: every house on the planet has a framework. Each house has a foundation and walls, and those walls can't stand without the foundation. Once the foundation has been laid and the walls are up, a roof can be applied and the interior designed and implemented, while work continues on the initial foundation.

If we apply this analogy to the Flex framework, we have a stack of logic -- the controller logic -- that has been made available for communicating with a database, handling security, writing to the file system, and so on. There are also the user interface elements -- buttons, canvases, dropdown lists, and so on. All of these also form the foundation of your Flex application -- the concrete slab, the timber beams and the bricks with which to build your house.

Flex is easy for web developers to learn because, at its core, it has a lot in common with (X)HTML, CSS, and JavaScript. Suppose you wanted to create a simple web page with a form button. In XHTML you'd type the following:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Button Example</title>

<form method="post" id="example" action="">
<input type="button" name="newButton" id="newButton" value="This is a button" onclick="checkForm()" />

When you view this markup in a web browser, you'll see a button displayed with the label "This is a button".

The XHTML button (click to view image)

To display something similar in Flex we use a form of markup called MXML. Here's the MXML markup for our previous example:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx=""
<mx:Button x="10" y="10" label="This is a button"


An Absolute Beginner's Tutorial On Flex 3

To run this simple page as a Flex application, we need to compile our MXML code using the Flex SDK. The result is shown below:

The first thing you'll notice is that MXML is an XML format. To indicate to the Flex compiler that we're defining an application, we use the <mx:Application/> element, in the same way we use the <html></html> tags to define a web page. We can then add other elements within the <mx:Application/> tag. In the above example, we've added a <mx:Button/> tag to create a button, just as we'd use an <input type="button" /> tag in a web page form.

As you can see, this is all very similar to constructing a traditional web page, and the framework provides you with everything you might use in XHTML (buttons, lists, etc.) and more. All you have to learn is the properties, methods and the names of the components in the framework, all of which are available from the Adobe Flex 3 language reference.

Of course, the Flex framework doesn't just consist of user interface components; it also contains actions that your application can utilise. For instance, there's a component called HTTPRequest, which your application can use to send and receive data from a server-side service (PHP, ASP.NET, etc.). When you run your application, you don't actually see the HTTPRequest, as it works in the background.

All these components have been bundled together by Adobe to form the Flex framework, so you don't have to create them from scratch.

Eventually, as you become more experienced with Flex, you'll want to create your own components that you'll use over and over. After a while, you'll have created a little library of your own that extends the Flex core framework to meet your individual needs.
What Do I Need to Get Started?

With the open sourcing of Flex 3, it's never been easier to begin building RIAs. To get started, you can use one of two options:

* The tool of choice for most serious Flex developers is Adobe Flex Builder 3, the official Flex IDE from Adobe. Flex Builder is also available as a professional version, which includes a Flex charting component. Flex Builder 3 retails for around US$376.00, and the professional version sells for around US$900.00.
* You'll also need your own programming editor, combined with the Flex 3 SDK, which is free. Yes: free as in beer.

Installing Flex Builder 3 is quite straightforward, as it comes with a user-friendly installer.

The Flex SDK, on the other hand, is a little trickier. To install the Flex SDK, download the zip file and extract it to a folder of your choice. The trick is that that folder should be in your path (meaning you should be able to execute files in that folder from the command prompt, regardless of your current location).

If you're planning on taking your Flex development seriously, I recommend you go ahead and purchase the Flex Builder 3 IDE. Flex Builder is based on the open-source Eclipse editor, which alone is an extremely powerful IDE. If you want to try before you buy, Flex Builder 3 is available for a 30-day trial. While it's great that the Flex 3 SDK is free, the benefits that Flex Builder 3 provides for Flex development over a standard text editor are many.
MXML is Easy!

Unlike other markup language acronyms, MXML doesn't actually stand for anything specific. (Although some people, and I'm one of them, like to think that it should stand for Magic eXtensible Markup Language.)

Macromedia created MXML in March 2004 before the organization was acquired by Adobe. MXML is primarily used to lay out a Flex application. Being a dialect of XML, standard validity rules apply: tags must be closed, and all the other XML rules apply.

Every component in MXML follows the same pattern:

1. You declare a namespace that tells Flex where to find a particular component.

2. You declare the component class you wish to use (e.g. Button) from that namespace.

3. You modify the available properties and methods using attributes, as illustrated below.

Because they're defined in the class structure of the component, the properties and methods that can be used from within each component vary.

You style the visual components of your application with Flex Cascading Style Sheets. These styles can be added in the same location as the properties and methods of a component, but discussing Flex Cascading Style Sheets is beyond the scope of this article.

MXML components can have child elements (just like XML). For instance, a container element such as Canvas can have child elements like a Button or a Label. The code below demonstrates this point:

<mx:Canvas x="53" y="64" width="192" height="94"
cornerRadius="20" borderStyle="solid"
backgroundColor="#A9A9A9" id="mainCanvas">

<mx:Button x="10" y="10" id="newButton"
label="This is a button"/>

<mx:Label x="10" y="57" id="newLabel"
text="This is a label"/>

If you compile and run this code, you'd see this web page:

By combining layout containers, such as Canvas, with other components, such as buttons and lists, it's possible to create great application designs in no time at all.

While MXML defines the structure of your Flex application, ActionScript 3.0 defines your application's behaviour.

Now, you may be thinking, "Hang on. If I can do so much with MXML, why do I need ActionScript 3.0?" Well here's the confusing part; MXML is actually a pretty form of ActionScript 3.0. In fact, MXML is converted to ActionScript 3.0 when you compile it. Let's look at an example that shows how similar MXML and ActionScript 3.0 are. The following code creates the same component (a Button), first in MXML, and then in ActionScript 3.0:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx=""
layout="absolute" creationComplete="init()">
<mx:Button label="This one is done by MXML" x="10" y="10" /> <mx:Script>
import mx.controls.Button;
//Init Function is executed when the application boots
private function init():void {
//Create a new button
var newButton:Button = new Button();
//Modify Properties
newButton.label = "This one is done by ActionScript";
newButton.x = 10;
newButton.y = 40;
//Add the new button to the stage (Screen)

The application that results when you compile this file will look like this:

MXML and ActionScript 3.0 buttons (click to view image)

As you can see, both approaches for creating a button produce the same result -- but there's far less typing involved with MXML than with ActionScript 3.0. Designing an application with ActionScript 3.0 would be a nightmare. MXML was created to simplify the work for you.

You still need to use ActionScript in your application, however; you'll need to define what happens when that button is clicked, for example. Look at it in this way: you design your application with MXML, and you make it work with ActionScript 3.0. By using MXML and ActionScript, you're separating the structural code from the programming logic. This is an important philosophy to remember when building Flex applications -- especially when you're building complex components down the track.

ActionScript 3.0 is an ECMAScript-based scripting language, which means that it adopts the ECMA scripting language standards. ActionScript 3.0 is a giant leap forward from is predecessor, ActionScript 2.0. The reason for this is that ActionScript 3.0 is now a truly object oriented programming (OOP) language. In fact, the entire framework of Flex is made up of object classes that have been written by Adobe.

If you want to develop complex RIAs, I'd recommend that you invest some time in understanding OOP. Most of the programming done in Flex is event-driven, which means that functions are run when a component triggers an event (for example, when a mouse clicks a button on the page). The Adobe Livedocs site has some great examples of object-oriented ActionScript.

The full details of ActionScript 3.0 syntax and OOP are beyond the scope of this article, but if you've done any JavaScript programming before, you are certainly well on your way.

Flex 3.0 is rapidly gaining steam; as a result there are some fantastic resources out there for anyone who wants to get started in building RIAs with Flex. Here's a sample:

<a href="" target="_blank">* Flex Livedocs</a>
<a href="" target="_blank">*</a>
<a href="" target="_blank">* FlexCoders</a>
<a href="" target="_blank">* Open Source Flex </a>
<a href="" target="_blank">*The Flex CookBook</a>
<a href="" target="_blank"> * Flex 3 Getting Started</a>


This article barely skimmed the surface of the Flex framework, although we did cover the basics of what the framework provides, and how MXML and ActionScript 3.0 work together. While this was a very gentle introduction to the concepts behind Flex, it should give you enough grounding in the concepts to go forth and experiment on your own.

Now that you've taken your first step in Flex development, the next step is to actually understand the components, play around with them to build your first application, and apply some ActionScript to really give it some life.


The LCID property uses the location identifier number to access information on display format issues, such as currency, date, and time, that are specific to a location or region. The location identifier number uniquely defines a geographic location or region. For example, the location identifier number for France is 1036.

Example demonstrates setting the locale to British English and using the VBScript FormatCurrency method to display the value 125 as currency with the £ symbol:


Session.LCID(= LocaleID)


Session.LCID = 2057
Dim curNumb
curNumb = FormatCurrency(125)
Response.Write (curNumb)

Setting Session.LCID explicitly affects all responses in a session.

If Session.LCID is not explicitly set in a page, it is implicitly set by the AspLCID metabase property. If the AspLCID metabase property is not set, or set to 0, Session.LCID is set by the default system locale.

Session.LCID can be set multiple times in one Web page and used to format data each time. Some locales need the matching codepage to be set to display characters properly. For example, to display dates and times in several locales on one page, the codepage must be set to UTF-8 (65001) to show all the characters.

If you set Response.LCID or Session.LCID explicitly, do so before displaying formatted output. Setting Session.LCID changes the locale for both the ASP application and the scripting engine. Using the VBScript function setLocale only changes the locale for the scripting engine.

If the locale of your Web page matches the system defaults of the Web client, you do not need to set a locale in your Web page. However, setting the value is recommended.

The LCID property sets or returns an integer that specifies a location or region. Contents like date, time, and currency will be displayed according to that location or region.


Parameter Description
LCID A locale identifier

response.write("Default LCID is: " & Session.LCID & "<br />")
response.write("Date format is: " & date() & "<br />")
response.write("Currency format is: " & FormatCurrency(350))


response.write("LCID is now: " & Session.LCID & "<br />")
response.write("Date format is: " & date() & "<br />")
response.write("Currency format is: " & FormatCurrency(350))


response.write("LCID is now: " & Session.LCID & "<br />")
response.write("Date format is: " & date() & "<br />")
response.write("Currency format is: " & FormatCurrency(350))


response.write("LCID is now: " & Session.LCID & "<br />")
response.write("Date format is: " & date() & "<br />")
response.write("Currency format is: " & FormatCurrency(350))


Default LCID is: 2048
Date format is: 12/11/2001
Currency format is: $350.00

LCID is now: 1036
Date format is: 11/12/2001
Currency format is: 350,00 F

LCID is now: 3079
Date format is: 11.12.2001
Currency format is: öS 350,00

LCID is now: 2057
Date format is: 11/12/2001
Currency format is: £350.00

Wednesday, October 15, 2008

CSS for Search Engine Optimization

CSS for Search Engine Optimization

Cascading Style Sheets (CSS) is a language that permits Web designers to attach styles such as spacing, color, font, etc. to HTML documents. Cascading style sheets are similar to a template, permitting Web developers to label styles for an HTML element and then apply it to the number of Web pages required. Thus, Cascading Style Sheets (CSS) are collections of formatting rules, which control the appearance of content in a Web page. With CSS styles you have great flexibility and control of the exact page appearance; from precise positioning of the layout to specific fonts and styles.

There are many benefits of using CSS. Maintenance of a Web site made with CSS is much easier compared to the ones which are table based. Aside from being able to make extensive changes with one CSS file, the code it generates makes it simpler to update. With CSS, when you decide to craft a change, you simply alter the style and that element is updated automatically anywhere it appears in the site, saving you an enormous amount of time. Without CSS you'd have edit each page independently. CSS generally requires less code compared to a table based layout, making your code lighter, cleaner and easier to maintain.
Cascading Style Sheets Benefits and Search Engine Optimization (SEO)

Another major benefit of CSS is that it makes your Web site SEO friendly. The reason behind this is simple. Search engines spiders are actually lethargic. They don't go through the bundles of HTML code to get to the indexed codes. Font tags and tables make HTML code cumbersome, and thus reduce the accuracy of the results. If you use external CSS files to determine the design attributes, the HTML code will be clean and will create better search engine rankings. With some knowledge of CSS you can change the code without destroying the visual layout. For instance, you could easily make the main content of your site to show up above the header or navigation menu in the code of your Web site; this will help to show search engine crawlers the importance of your content. I personally saw a huge boost in rankings in fully functional CSS Web sites. When I look at someone’s Web site that was built using old school HTML code with tags such as: TABLES, TD, TR, FONT and so on, I convert that site to a CSS layout. There are many tools on the Internet that shows the actual code over text ratio weight of your site. Modern search engines such as Google, Yahoo and MSN love light-weighted Web sites. They want to see your content; the text, not the code. With CSS everything is possible. You can place excessive code into an external file, thus leaving the actual page clean and simple.
Web Site Accessibility

CSS makes your Web site more accessible. By 2008, it's estimated that one-third of the world’s population will be using hand held devices to access theInternet. It's important that your site is accessible to them also. You can make an additional CSS document particularly for handheld devices like cell phones, which will be called up in place of the regular CSS document; which is not achievable with a tabular layout. CSS benefits accessibility chiefly by separating document structure from presentation.
Increases Download Speed of Your Website

CSS code downloads faster than tables. Browsers read through tables twice prior to exhibiting their contents; first to work out their structure and then to determine their content. Moreover, tables are shown on the screen as a whole, no part of the table will be displayed until the entire table is downloaded and rendered. Tables support the use of spaced images to assist with positioning. CSS generally requires less code than tables. All layout code can be placed in an external CSS document, which will be called up just once and then stored on the user’s computer; while the table layout stored in each HTML document must be loaded up each time a new page downloads. Also with CSS, you can manage the order of the items downloaded. You have the control to make the content appear prior to images, which tend to load slower than text.
Cross Browser Compatibility

To summarize, CSS makes your Web sites load faster, it saves on time and labor, links can be more attractive and dynamic, and you can add rollovers without using JavaScript. Currently all the major browsers ( Firefox, Explorer and Netscape) recognize CSS.

Tuesday, October 14, 2008

Tracking users and search engines spiders in coldfusion

Tracking users and search engines spiders in coldfusion

If you want to find out what CGI variables you have available, just create a <cfdump var = "#cgi#">

Example output from local host:



[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]


[empty string]






[empty string]






[empty string]


[empty string]


[empty string]


[empty string]




gzip, deflate






CFID=11202; CFTOKEN=60629250




[empty string]


Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461; .NET CLR 1.0.3705; .NET CLR 1.1.4322; Dreamweaver MX 2004 7.0.2052)






[empty string]




[empty string]
















[empty string]

Example code:




This tutorial will show who is visiting your pages, both users and search engine spiders. You can also see how a visitor goes from page to page on your site. It will also give you information about which page or URL the visitors came from. You will also get the name of the search engines spider and you can see which pages the spider has indexed.

What information we need to capture:

The date and time:
The Now() function returns the current date and time from the server.
If you are using a SQL database, use getDate() instead of Now().

The referring page information:
The CGI variable CGI.HTTP_REFERER will capture referring page information. Using the Cold Fusion LEFT string function strips the extra characters providing the number of characters you choose.

Some versions of IE 5.5, Opera, and AOL don't even recognize the http_referer command. Netscape 7 doesn't like it at all. Microsoft and Netscape have both realized this problem and aren't offering any resolutions.

The IP address of the client computer:
The CGI variable CGI.REMOTE_ADDR will capture the IP address of the client computer. Example:

The client hostname:
The CGI variable CGI.HTTP_HOST will capture the client hostname server.

The browser type of the visitor:
The CGI variable CGI.HTTP_USER_AGENT will capture the browser type of the visitor.

The page name on your site:
The name of the page is typed in on the pages you want to track.

For the home page:
<cfmodule template = "admin/act_tracker.cfm" page_track = "Home">

For the product page:
<cfmodule template = "admin/act_tracker.cfm" page_track = "Product">

If you?re using Fusebox, write this code in the fbx_layouts.cfm layout file on the root:
<cfmodule template = "admin/act_tracker.cfm" page_track = "#fusebox.circuit#">

In the examples above we have the tracker.cfm file in the directory admin. Change this if you?re using another path to tracker.cfm.

Creating the database:

Here's an example of an Access database for the user tracking:

Field Name

Data Type

Tracking_ID AutoNumber Primary Key.
Tracking_Date Date/Time The date and time.
Tracking_Referrer Text The referring page information.
Tracking_Client_Address Text The IP address of the client computer.
Tracking_Client_Host Text The client hostname.
Tracking_Client_Browser Text The browser type of the visitor.
Tracking_Resource Text The page name on your site.

Follow this link to see an animated tutorial how to add and manage your data source connections and Data Source Names (DSNs).

Setting global variables in the application file.

The application file:

<!--- The name of your database. --->

<cfset request.main_access_DB = "database_name">

<!--- The database username. --->

<cfset request.dbusername = "your_username">

<!--- The database password. --->

<cfset request.dbpassword = "your_password">

The act_tracker.cfm file:

The purpose of this file is to get the name of the page(s) you want to track, and save information to the database.

If you have access to the CustomTag directory on your server you can save the file there, or you can save the file in another directory and use cfmodule tag to call the act_tracker.cfm file.

This file needs one parameter, the name of the page you want to track. Below are some examples of the code you need to implement on the pages you want to track.

Here's an example using the cfmodule tag.
<cfmodule template="admin/act_tracker.cfm"
page_track = "Home">

In the example the act_tracker.cfm file is located under the admin directory and the page we want to track is 'Home'.

If you want to track a page called 'Products' in the directory products:
<cfmodule template="admin/act_tracker.cfm"
page_track = "Products">

Or if you track your index.cfm file:
<cfmodule template="admin/act_tracker.cfm"
page_track = "The index file">

If you are using FuseBox, put the code in the fbx_layouts.cfm file:
<cfmodule template="admin/act_tracker.cfm"
page_track = "#fusebox.circuit#">

Notice: The #fusebox.circuit# contains the fuseaction which is being processed.

Here?s code for the act_tracker.cfm file:

<!--- The date and time. --->

<cfset Tracking_Date = Now()>

<!--- The referring page information. --->

<cfparam name="cgi.http_referer" type="string"default="">

<!--- The IP address of the client computer. --->

<cfparam name="cgi.remote_addr" type="string"default="">

<!--- The client hostname. --->

<cfparam name="cgi.http_host" type="string"default="">

<!--- The browser type of the visitor. --->

<cfparam name="cgi.http_user_agent" type="string"default="">

<!--- The page name on your site. --->

<cfparam name="attributes.page_track" type="string" default="Page Name">

<!--- Ok we have that data. Insert them into our database. --->

<cfquery name="tracker_insert" datasource="your_datasource" username="your_username" password="your_password">

INSERT INTO tracker_db















If you?re running SQL delete the
<!--- The date and time. --->
<cfset Tracking_Date = Now()>
and replace the insert code:
INSERT INTO tracker_db
VALUES (getdate(),

The dsp_tracker.cfm file:

This file will display the contains in the database. You can specify in the query what you want to display.

Here's an example displaying all the fileds and the last 100 records:

<cfquery name="User_tracking" datasource="# request.main_access_DB#" username="#request.dbusername#" password="#request.dbpassword #">

SELECT * FROM tracker_db

order by Tracking_ID Desc


<cfoutput query="User_tracking" maxrows="100">

#Tracking_Date# #Tracking_Referrer# #Tracking_Client_Address# #Tracking_Client_Host# #Tracking_Client_Browser# #Tracking_Resource# <br>


You can also add an automatic refrech on the dsp_tracker.cfm file, example refrech = 30 sec.

Monday, October 13, 2008

poll in coldfusion


Have you ever wanted to have your own website poll in ColdFusion. If so, then hopefully the tuto-
rial below will allow you to finally add one. Or perhaps it will provide you with the incentive to
improve this one.

Some important poll features:

- Writes a cookie once the poll has been used so that the voter may not vote in the current poll

(and is once again useable).

- Checks to see if a person tries to use their browser’s back button to make multiple votes and
informs them this is not allowed.

- Checks to see if the user made a selection when voting and informs them if they haven’t made
a selection.

- Checks to see if a user accessed the results page without hitting the ‘Vote’ button and then dis-
plays the current votes without adding a vote to the poll.

- Allows for users to view previous polls and results.

- Displays the results for all polls using a graphical bar as well as the numerical vote count (indi-
vidual and total) and vote results as percentages.

I also created an administration side to this system so the website owner can add, edit or remove
polls online. You will find more information on this towards the end of this tutorial. Of course, you
should only upload these pages to a secure area of your website so random people don’t edit your
polls ;)

This poll system has been created so that the poll question/answers or results are displayed in a
webpage’s header or footer using ColdFusion’s ‘cfinclude template’ code.


From here on in, it gets harder. But hopefully it’ll all make sense once you get into it. The next

<cfif IsDefined(”cookie.poll”)>
<cfif “#Cookie.Poll#” is “#CurrentID.QuestionID#”>
Display the poll results
Show the poll form with ‘Vote’ button
Cookie does not exist. Show the poll form with ‘Vote’ button

What we check for first, is if the cookie for the poll already exists.

<cfif IsDefined(”cookie.poll”)>

Next, we check to see if the existing cookie is current. We do this by checking to see if the cookie’s
value is the same as the last (most recent) ‘QuestionID’ in the ‘Questions’ table of our database.
We do this by returning one row of ‘QuestionID’ in descending order.

<cfquery datasource=”fusionpoll” name=”CurrentID” MAXROWS=”1”>
SELECT Questions.QuestionID
FROM Questions

<cfif “#Cookie.Poll#” is “#CurrentID.QuestionID#”>

If the cookie is current, then the user has already voted and we display the current poll results. To
do this, we need to query the database for the current question, answers and votes.

<cfquery datasource=”fusionpoll” name=”Totals”>
SELECT Questions.Question, SUM(Answers.Votes) AS TotalVotes
FROM Questions INNER JOIN Answers
ON Questions.QuestionID=Answers.QuestionID
WHERE Questions.QuestionID=#Cookie.Poll#
GROUP BY Questions.Question

<!--- Query the answers and the number of votes for each one. --->

<cfquery datasource=”fusionpoll” name=”Results”>
SELECT Answers.Answer, Answers.Votes
FROM Answers
WHERE Answers.QuestionID=#Cookie.Poll#
ORDER BY Answers.AnswerID

Once we have done this, we display the results using cfoutput. Below is the code I used to display
the results.

<table width=”175” border=”0” align=”center” cellpadding=”2” cellspacing=”0”>

<!--- Display the question --->

<cfoutput query=”Totals”>
<td><div align=”center”><strong>#Question#</strong></div></td>

<!--- Display the answers and their number of votes. --->

<cfoutput query=”Results”>

<!--- Set the percent of total votes for each answer. --->

<cfset Percent=Round((Votes / Totals.TotalVotes) * 100)>
<td><div align=”left”>#Answer#</div></td>

<!--- Display the percentage of votes in a graph format. --->

<td><div align=”left”><img align=”middle” src=”images/poll-graph.gif” width=”#Percent#%”
<td><div align=”right”><em>#Votes# votes - #Percent#%</em></div></td>

<!--- Display the total number of votes --->

<cfoutput query=”Totals”>
<td><div align=”right”><strong>Total:</strong><em><strong>
</strong>#Int(TotalVotes)# votes</em></div></td>

The way you display the code is entirely up to you of course, but there are some things to take
notice of in the code above. For example:

<cfset Percent=Round((Votes / Totals.TotalVotes) * 100)>

This code sets ‘Percent’ as the percentage an individual vote has received by dividing the
‘Answers.Votes’ by the total number of votes(as defined in the previous ‘Totals’ query) and multi-
plying that number by 100. This is then used to set the width of the graphical display for the poll.


In the image attributes, we have set the width of the image as the same percentage we calculated
previously. The last important note in the above code is that #Int(TotalVotes)# votes displays the
total number of votes for the current poll.

Continuing on with the vote.cfm page, the next step covers what to do if the cookie is not current.
If the cookie is not current, then the user has not yet voted in this poll and therefore needs to
view the question and answer choices with the ‘Vote’ button so they can vote. To make this possi-

database. We discover what the latest question is in the same way we did when checking to see if
the cookie was current.

<cfquery datasource=”fusionpoll” name=”Current” MAXROWS = “1”>
SELECT Questions.QuestionID
FROM Questions
ORDER BY Questions.QuestionID DESC

<cfset display = #Current.QuestionID#>

<cfquery datasource=”fusionpoll” name=”Question”>
SELECT Questions.QuestionID, Questions.Question, Answers.AnswerID, Answers.Answer
FROM Questions INNER JOIN Answers
ON Questions.QuestionID = Answers.QuestionID
WHERE Questions.QuestionID = #display#
ORDER BY Answers.AnswerID

The first query (’Current’) selects the latest question from the ‘Questions’ table thanks to our hav-
ing the MAXROWS set at “1” and by querying in descending order. Then we set ‘display’ as the
current question so we can select the corresponding answers from the ‘Answers’ table. All that is
required then is to display the form using the selected query output.

<form action=”results.cfm” method=”post” name=”Poll”>
<table border=”0” align=”center” cellpadding=”4” cellspacing=”0”>
<cfoutput query=”Question” group=”QuestionID”>

<!--- Pass the question id as a hidden variable. --->

<input type=”hidden” name=”QuestionID” value=”#QuestionID#”>
<td colspan=”2”><div align=”center”><strong>#Question#</strong></div></td>

<!--- Display the answers and their checkboxes --->


<td width=”10”><input type=”radio” name=”AnswerID” value=”#AnswerID#”></td>
<td><div align=”left”>#Answer#</div></td>
<td colspan=”2” align=”center”><input name=”Submit” type=”submit” value=”Vote”></td>
<td colspan=”2” align=”center”><div align=”center”>[<a
Poll Results</a>]
[<a href=”results.cfm”>Previous Poll Results</a>]</div></td>

You should notice two links to results.cfm after the ‘Vote’ button in the code above. These will be
explained a little bit later.

Next, we have to check if none of the previous rules applied. What to do if the cookie did not ex-ist at all. If this is the case, then we simply use the same code as above to show the current poll
question and multiple choice answers with the ‘Vote’ button. If a cookie does not exist, the person
has not yet voted at all.


The next page is the results.cfm page which is where our vote.cfm page sent the form details
to. Unlike vote.cfm however, results.cfm is not placed in the header or footer of the website. The
results.cfm pageincludes the footer file (which then includes the vote.cfm page).

Shown below is a basic rundown of the layout of this page.

<cfif isDefined(’Form.QuestionID’)>

<cfif IsDefined(”cookie.poll”)>
<cfif “#Cookie.Poll#” is “#CurrentID.QuestionID#”>
Show warning for attempted multiple votes
Set Cookie, add vote to database and display the answer selected by the vote
Show warning for no answer selected
<cfif isdefined (’URL.QuestionID’)>
<cfif check.AllVotes is “0”>
Show warning for no votes in current poll
Display poll results
Show the droplist for the previous polls

The first check we must perform is to see if the page was reached by somebody using the form.
We do this by checking for ‘QuestionID’ from the votes.cfm page.

<cfif isDefined(’Form.QuestionID’)>

And next we check to see if the poll user made a selection when they hit the ‘Vote’ button. And we
do this by checking for ‘AnswerID’ from the votes.cfm page.

<cfif isDefined(’Form.AnswerID’)>

And the next check is to stop people from voting more than once by using their browser’s back
button. We check for the existence of a cookie.

<cfif IsDefined(”cookie.poll”)>

vote.cfm page. If it is current, we display a message telling the user they can not vote more than

<cfquery datasource=”fusionpoll” name=”CurrentID” MAXROWS=”1”>
SELECT Questions.QuestionID
FROM Questions

<cfif “#Cookie.Poll#” is “#CurrentID.QuestionID#”>
<div align=”center”>Sorry, you can only vote once.</div>
<cfinclude template=”foot.cfm”>

It is important to have the ‘cfabort’ code in this part of the webpage or else you will end up with
two footers in your page display. Once we have performed those checks, we know the cookie is
not current (or existing) so we set a new cookie.

<cfcookie name=”Poll” value=”#Form.QuestionID#” expires=”never”>

The value of the cookie is set as the ‘QuestionID’ value so that we can later tell if a cookie is
current when we add a new poll question. The cookie’s expiration is set to “never” because we
will simply update it whenever we add a new poll to our website. If you set your cookie to expire
before you set a new poll, people will be able to vote again.

Once the cookie is set and the vote has been made, we must update the ‘Answers’ table with the
new vote count. We select the current vote count from the ‘Answers’ table for the current ques-
tion and chosen answer (from vote.cfm) and then set ‘NewVotes’ as the current vote plus one new
vote. Then we update the votes where the ‘QuestionID’ and ‘AnswerID’ are the same as the ones
passed through from our vote.cfm page.


FROM Answers
WHERE QuestionID=#QuestionID# AND AnswerID=#AnswerID#
<cfset NewVotes=LastVote.Votes + 1>
<cfquery datasource=”fusionpoll” name=”NewVote”>
UPDATE Answers
SET Votes=#NewVotes#
WHERE QuestionID=#QuestionID# AND AnswerID=#AnswerID#

Next, we create queries so we can display the new vote count for all answers in the current poll.
Once this is done, you simply display the ‘QuestionID’ and ‘AnswerID’ to show the user how they
voted in the poll.

<cfquery datasource=”fusionpoll” name=”showanswer”>
SELECT Answers.Answer, Answers.AnswerID
FROM Answers
WHERE Answers.AnswerID = #AnswerID#

<cfquery datasource=”fusionpoll” name=”showquestion”>
SELECT Questions.QuestionID, Questions.Question
FROM Questions
WHERE Questions.QuestionID = #QuestionID#

However, if the user had hit the ‘Vote’ button without making a selection from the radio button list,
we would need to tell them of their mistake and not update the database with any votes.


<!--- No radio button selected, so tell the user to try again --->

<div align=”center”>Sorry, you didn’t select anything. Please select an answer before hitting the

Next, we check to see if the page was accessed without somebody using the poll form. For exam-
ple, an option I’ve added to the poll is for a user to view the current poll votes before they vote for
themselves (this is one of the links I mentioned earlier that appears under the ‘Vote’ button of the
poll). So we must check to see if that is what they are doing.

<cfif isdefined (’URL.QuestionID’)>

However, we will get an error if nobody has voted yet when somebody tries to check for the cur-
rent results. So we have to provide an alternative for if this happens. If the total vote count for the
current question is zero, we tell the poll user that there are no current votes and ask them to be
the first person to vote.

<cfquery datasource=”fusionpoll” name=”check”>
SELECT SUM(Votes) AS AllVotes
FROM Answers
WHERE Answers.QuestionID=#URL.QuestionID#

<cfif check.AllVotes is “0”>

<div align=”center”>There are currently no votes for this poll. Vote now to be the first.</div>

Otherwise we query the database and display the current results for the poll. This is done in the
same fashion as we did earlier in the vote.cfm page.

The final way for somebody to access the results.cfm page without using the form is when they
are looking for previous poll results (this is the other link I mentioned previously). We now display
the previous poll droplist which is dynamically populated by querying the database.

What we must do is query the ‘Questions’ table to look for the current poll. We then set the cur-
rent poll ‘QuestionID’ as ‘Current’. Next we query the ‘Questions’ table again looking for any ‘Ques-
tionID’ not equal to the current question and display them in a descending order. There is no need

We then dynamically populate the droplist with the questions.

<form action=”pastresults.cfm” method=”post”>
<cfquery datasource=”fusionpoll” name=”CurrentID” MAXROWS=”1”>
SELECT Questions.QuestionID
FROM Questions
<cfset Current=CurrentID.QuestionID>
<cfquery datasource=”fusionpoll” name=”PastPolls”>
SELECT Question, QuestionID
FROM Questions
WHERE QuestionID <> #Current#
ORDER by QuestionID DESC
<select name=”QuestionID”>
<cfoutput query=”PastPolls”>
<option value=”#QuestionID#”>#Question#</option>
<input type=”submit” name=”Submit” value=”Show me”>

The form values are sent to pastresults.cfm, which like results.cfm, is its own page and not used
in the header or footer. This page then displays the results of the requested past poll in the same
way that results.cfm displayed the current poll results.

That then completes the user/voting side of the poll system.

Now we have to create the administration side of the poll system. I have used the same header
and footer system I used in my previous pages, but you do not have to for these pages to all
work. It’s just how I like to do things :)

The pages you need to create are:




The first page I would create is polls.cfm as it is the administration page that gives you access to
the pages that add, edit and delete polls. The first link you see on the page is a simple link to the
‘Add a poll’ page (polladd.cfm). The links to edit and delete polls however require some simple
ColdFusion. First we must query the database to get the ‘Question’ and ‘QuestionID’ details.

<cfquery name=”GetPolls” datasource=”fusionpoll”>
SELECT Questions.QuestionID, Questions.Question
FROM Questions
ORDER by QuestionID DESC

We can then display the poll questions and offer links to the edit and delete pages where the
‘QuestionID’ is carried onto those pages.

polladd.cfm and polladdaction.cfm

The first administering page we will look at is the polladd.cfm page. This is a simple HTML page
where the question gets passed through to the polladdaction.cfm page through the formfield
‘Question’ and the answers all get passed through as the formfield ‘Answer’. It is important that
you inform whoever is creating these polls on your website that each answer is to be provided by
some predetermined dilimiter. For this tutorial, I will use the colon (:) as my dilimiter.

For example, if the poll question was Where did I find this Hip Poll System., then when creating
the answers, they would be typed out as TwoHipDudes:webWICKED:I don’t know:What poll!.!.

The tricky part of the polladdaction.cfm page is the looping of the ‘Answer’ field to make sure that
each provided answer gets added to the ‘Answers’ table with the matching ‘QuestionID’.

<cfquery name=”getLastID” datasource=”fusionpoll”>
SELECT MAX(QuestionID) as LastID


<cfloop list=”#form.Answer#” index=”FormAnswer” delimiters=”:”>
<cfquery name=”InsertAnswers” datasource=”fusionpoll”>
INSERT INTO Answers(QuestionID, Answer)

The first query above shows how we get the most recent ‘QuestionID’ and then insert it with our
loop of provided answers. The second query shows how we set ‘Form.Answer’ in the loop to add
each new existence of the formfield to a new row in the ‘Answers’ table. Because each answer was
seperated by the colon (:) in the formfield in the polladd.cfm page, the polladdaction.cfm page
loops until all answers have been added to the database. This is because we have set the delimit-
ers in the cfloop code.

polldeleteconfirm.cfm and polldeleteaction.cfm

The polldeleteconfirm.cfm page simply provides the option to delete the selected question from
the database. The ‘QuestionID’ is passed on from the polls.cfm page. If you click the ‘Delete’ but-
ton then the poll is deleted by the processing page – polldeleteaction.cfm.

The poll is removed by polldeleteaction.cfm by deleting the question and corresponding answers
from both the ‘Questions’ and ‘Answers’ tables.

<cfquery name=”DeleteQuestion” datasource=”fusionpoll”>
FROM Questions
WHERE QuestionID = #Form.QuestionID#
<cfquery name=”DeleteAnswer” datasource=”fusionpoll”>

FROM Answers
WHERE QuestionID = #Form.QuestionID#

pollupdate.cfm and pollupdateaction.cfm

The last function of the administration system is the editing feature. This is the trickiest part of the
administration process. Calling the answers from the ‘Answers’ table and then editing them re-
quires they be edited separately as they are in the same formfield name, but with separate values.

<cfoutput query=”GetAnswerDetails”>
<form method=”post” action=”pollupdateaction.cfm”>
<table width=”100%” border=”0” cellspacing=”0” cellpadding=”2”>
<td width=”25%”><p><strong>Answer:</strong></p></td>
<td width=”75%”>
<input type=”text” name=”Answer” size=”40” maxlength=”50”
Value=”#GetAnswerDetails.Answer#”> <input type=”submit” name=”SubmitButton” value=”Make
the changes”>
<input type=”Hidden” name=”AnswerID” Value=”#GetAnswerDetails.AnswerID#”>

For each answer in the corresponding poll there will be a form element created with its own sub-
mit button. So to edit an answer, you make your changes and press the corresponding ‘Make the
changes’ button. The changes are then updated when the form is sent to pollupdateaction.cfm.

Well, that’s it. Hopefully the tutorial makes sense and you are now able to add a poll to your web-
site. Make sure you check out the included working poll system that came with this tutorial and
see the poll working for a better idea of how everything works. The file you downloaded contains
everything you need to add the poll to your own site. You could use the database, edit the head-
ers and footers and have a fully operational poll onsite before you know what to do with it :)

This tutorial has been offered for free. I would however appreciate an email letting me know if u used in your project .