ALTER ColdFusion Help

If you would like to alter an existing table, you may do so with the ALTER clause. The syntax for the alter clause is ALTER TABLE tabl e_name ADD IDROP IMODIFY ( co 1umn_defi ni ti ons). The pipe ( I) sign is used in the syntax to indicate that you can use any of the keywords: ADD, DROP, or MODIFY. You would not actually use the pipe sign in your clause. The column definitions would be the same as described in the “Create Table” section of this chapter

Listing 11.4 modifies the table structure that was created in Listing 11.3. The resulting table is shown in Figure 11.16.

<CFQUERY NAME-‘add_pk’ DATASOURCE-‘sybex’>
ALTER TABLE doctors
ADD COLUMN doctor_id counter PRIMARY KEY
</CFQUERY>
<CFQUERY NAME-‘drop_picture’ DATASOURCE-‘sybex’>
ALTER TABLE doctors
DROP speaker_picture
</CFQUERY>
<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’)
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5-
Chapter 11 – Listing 11.4</TITLE></HEAD)
-<BODY BGCOLORa’#FFFfFF’)
<Hl>Doctors Table Modified!</Hl>
</BODY>
</HTML)

FIGURE 11.16

FIGURE 11.16

DROP

DROP is used to remove entire tables from a database. Once this command has been executed, there is no way to retrieve the table. The syntax for dropping a table is DROP TABLE tab’ e_name.The code.in Listing 11.5 drops both tables created in this section.

<CFTRANSACTION)
<CFQUERY NAME-‘drop_doctors’ DATASOURCE-‘sybex’)
DROP TABLE doctors
</CFQUERY>
<CFQUERY NAME-‘drop_customers’ DATASOURCE-‘sybex’)
DROP TABLE Customers
<iCFQUERY>
</CFTRANSACTION>
<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5-
Chapter 11 – Listing 11.S</TITLE></HEAD>
<BODY BGCOLOR=’#FFFFFF’>
<H1>Doctors and Customers Tables Dropped!</H1>
</BODY>
</HTML>

Using Advanced CFQUERY Features

Chapter 10 covered the basics of using CFQUERY In. this section, you will learn some of the more advanced features of CFQUERYand other complimentary ColdFusion tags

Persistent Queries

Starting with version 4, Cold Fusion has offered the capability to use cached query data. This means that ColdFusion will store the results of a query in the cache to limit the connections to the database. This is useful in reducing the load on the database and the processing time of the query. This option is most useful for queries that return fairly static data. For example, let’s say you populate a select drop-down list with items from a table and this list is always the same except for periodic modification by a developer. You can query the table at the start of the day to retrieve the drop-down list results and have the results available in the cache for the rest of the day. This is an example of a persistent query.

To create a persistent query you need to specify one of the following attributes in yourCFQUERYtag

• CACHEIDAFTER
• CACHEDWITHIN

CACHEDAFTE is Rused to specify a certain date and time to use cached query data. ColdFusion will use cached query data if the date of the original query is after the date specified .

CACHEDWITHiIsNused to specify a timespan for using the cached query data. For example, you can specify that the cached data will be used for a spa:\ or 13 minutes. To create a timespan, use ColdFusion’s CreateTimeSpan function.

You can implement a CACHEDWITHINat·tribute and still have the option of not using the cache, as shown in Listing 11.6

<CFPARAM NAME-‘URL.ForceRead’ DEFAULT-‘N’>
<CFPARAM NAME-‘speaker_list’ DEFAULT-“Amas’, ‘Henderson’,
‘Bashir'”>
<CFIF URL.ForceRead IS ‘Y’>
<CFQUERY NAME-‘GetSpeakers’ DATASOURCE-‘sybex’~
SELECT speakers.speaker_first_name,
speakers.speaker_last_name ,
~peakers.speaker_id
FROM speakers
WHERE speakers.speaker_last_name IN
(‘PreserveSingleQuotes(speaker_list)#)
</CFQUERY>
<CFEtSE>
<CFQUERY NAME-‘GetSpeakers’ DATASOURCE-‘sybex’
CACHEDWITHINc”CreateTimeSpan(O,O,10,O)#’>
SELECT speakers.speaker_fi rst_name,
speakers.speaker_last_n~.
speakers.speaker_id .
FROM speakers •
WHERE speakers.speaker_last_name IN
(‘PreserveSingleQuotes(speaker_’ist),.)
</CFQUERY>
</CFIF>
<!DOCTYPE’HTML PUBLIC ‘-11«3C//DTD HTML 4.0
Transitional//EN’>
</BOOY>
</HTML> .’
<HTML><HEAD><TITLE>Sybex’- Mastering ColdFusion 4.S –
Chapter 10 – Listing 10.6</TITLE></HEAD>
<BODY>
<CFOUTPUT QUERY-‘GetSpeakers’>
<B>’speaker_id#.</B> #speaker_first_name#
‘speake,r_,ast_nameI<BR>
</CFOUTPUT>

The variable Force Read enables you to optionally do a direct read against the speaker table instead of the cached query data. You would call the template as follows:

Figure 11.17 displays the result without the CACHEDWITHpINarameter. Notice that the time to query is 30ms and the page processing time is 120ms

FIGURE 11.17

FIGURE 11.17

If you remove the ForceRead parameter ColdFusion retrieves the cached data. If you have Show Processing Time turned on in the debug settings of your ColdFusion server the time of processing will be Cached Query. This will be your indicator that the cached data was retrieved. You should also notice that the page processing time is much shorter. See Figure 11.18

FIGURE 11.18

FIGURE 11.18

Calling Stored Procedures by Using CFSTOREDPROC

When SQL statements are executed on a regular basis it is wise to create a stored procedure provided that your database supports it. Stored procedures are compiled SQL statements that are permanently stored in the database in executable format. These compiled SQL statements are known as functions and subprograms. Stored procedures are preparsed compiled and ready to be invoked by the database user

The advantages of using stored procedures are as follows:

• Response time is quicker.
• Statements are pre-parsed and in executable format.
• Stored procedures may call other procedures and functions.
• Stored procedures support modular programming structures.
•Stored procedures are more secure separating the CF environment from the Database Activity. Even if someone hacks and executes CF code on your server they will not be able to do more that what is defined in the stored procedure.

ColdFusion enables you to call a stored procedure through an ODBC or native connection a database by using the CFSTOREDPROC tag. You can call a stored procedure within your ColdFusion application in one of two ways:

• Using CFQUERY and specifying call parameters
•USing CFSTOREDPROC CFPROCPARAM and CFPROCRESUL

The simplest way of calling a stored procedure is by using the CFQUERY tag. The following is an example of code calling a stored procedure:

<CFQUERY NAME-‘ProcessProd~~ts’ DATASOURCE-‘Products’)
( call BookDB.dbo.sp-process-product( ‘#p_name#’ ) }
</CFQUERY>

The code required varies depending upon your database. Consult your database documentation for more details

You need to use CFSTOREDPROC, CFPROCPAR.~!·t and CFPROCRESUL T when calling a stored procedure that requires you to do the following:

• You need to specify input and/or output parameters for the query.
• You need to capture the return value.
• You need to select from multiple result sets in a stored procedure

CFSTOREDPROC

The CFTOREDPROC tag is used to connect to a database and a related stored procedure. The attributes are described in Table 11.4. The following code example has several attributes, many of which you may already be familiar with after using CFQUERY:

<CFSTOREDPROC PROCEDURE=’ procedure_name ,
DATASOURCE=’datasource_name’
USERNAMEz’ userjtame ‘
PASSWORD=” password ,
DBSERVERz’databas_server_name’
OBNAME-‘ database_name ,
BLOCKFACTORz’block_size
PROVIDER-‘COM_Provider’
PROVIDERDSN=’datasource’
DEBUG-‘Yes/No’
RETURNCODE-‘Yes/No’)
Use CFPROCRESULT and CFPROCPARAM here.
</CFSTOREDPROC>

TAB LE 11.4:

TAB LE 11.4:

TAB L E 1 1 .4:

TAB L E 1 1 .4:

The CFSTOREDPROC also returns a variable CFSTOREDPROC. Executi onTi me. This variable provides the execution time of the stored procedure in milliseconds.

In order for CFSTOREDPROC to be useful, you need to also use the CFPROCRESUL T and CFPROCPARAM tags nested inside the CFSTOREDPROC tag set.

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This