Onceyou have mastered CFQUERY and building static SQL statements, you will start wondering about how to dynamically build SQL queries within your ColdFusion application. With ColdFusion you can build these dynamic queries quite easily. You can insert dynamic parameters in your SQL statement or within your CFQUERY tag. These dynamic parameters are ColdFusion variables such as:
• Client, session, or application variables
• Form fields
• URL parameters
Any or all of these ColdFusion variables can be used as substitutes for values in the CFQUERY tag and within the SQL query. Creating dynamic queries means being able to do any of the following:
• Set your data source name as an application variable.
• Store a list of column names in a variable for the SEtECT statement or for the ORDER BY clause.
• Use CFI F tclgSto build a column list for the SELECT statement or for building conditions in the WHERE clause or in the ORDER BYclause.
• Use IsD~fi ned to check whether J variable is defined for inclusion in your WHERE clause or ORDER BYclause.
• Store your SQL statement in a variable, pass it around from page to page, and place it within the CFQUERY tag.
Examples of dynamic queries can be seen in later chapters of this book. For examples, please refer to the following chapters:
• Chapter 15, “Forms”
• Chapter 16, “Validating Form Data”
• Chapter 17, “Implementing the Cold Fusion Web Application Framework”
Where Do We Go from Here?
You covered quite a bit of SQL in this chapter. You should have a good grasp of how to connect to a Cold Fusion data source and manipulate data. in a database. In the next chapter, you will learn some more advanced techniques for communicating with databases. Some of the topics that were just touched on briefly in this chapter will be expanded upon. You will learn how to join tables, optimize your SQL, troubleshoot your queries, and much more.