CFPROCRESULT ColdFusion Help

Using the CFPROCRESUL T tag allows you to access the data returned by the CFSTOREDPROC tag by defining a name for the result set. The CFPROCRESUL T tag is nested within the CFSTOREDPROC tag. Because stored procedures can return multiple result sets you can specify multiple CFPROCRESUL T tags. They are described in Table 11.5. CFPROCRESUL T has several attributes, as you will see in the following code example:

<CFPROCRESUlT NAMEa’query_name’
RESUlTSET-‘number_of_resultset’
MAXROWS-‘maxrows’>

TAB L E 1 1 . 5:

TAB L E 1 1 . 5:

CFPROCPARAM

CFPROCPARAMis used to identify and check the data type of the parameter being passed to the stored procedure. CFPROCPARAMtakes several attributes (see Table 11.6

<CFPROCPARAMTYPE-‘IN/OUT/INOUT’
VARIABLE-‘variable_name’
OBVARNAME-‘OB_variable_name’
VALUE-‘parameter_value’
CFSQLTYPE-‘parameter_datatype’
MAXLENGTH-‘length’
SCALEa’decimal_places’
NULL-‘yes/.no>

TABLE 11.6:

TABLE 11.6:

A separate CFPROCPARAM tag is required for each procedure parameter. If the TYPE attribute is set to in or i nout, you can refer to the resulting variable by the name specified in the VARIABLE attribute.

If you are using positional notation in your stored procedure then you must add your CFPROCPARAM tags in the exact order required by the stored procedure. Alternatively if you are using named notation you need to specify the DBVARNAME attribute and make sure it corresponds to the name in the stored procedure

An example of what a stored procedure call might look like if using CFSTOREDPROC is shown in Listing 11.7

<CFSTOREDPRO€ PROCEDURE-‘speakers’ ‘
DATASOURCE-‘s1bex;
USERNAME-‘administrator’
PASSWORD-‘drowssap’
DBSERVER-‘motlagh’
DBNAME-‘sybex’
DEBUG-‘Yes’
RETURNCOOE-‘Yes’>
<CFPROCRESULT NAME-‘speakers_1’ RESULTSET-‘l’>
<CFPROCRESULT NAME~’speakers_2′ RESULTSET-‘2’>
<tFPROCPARAM TYPE-‘In’
CFSQLTYPE-‘CF_SQL_NUMERIC’
DBVARNAME-‘ speaker_i d’
VALUE-‘tform.speaker_idt’
NULL-‘No’>
</CFSTOREDPROC>
<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5 –
Chapter 10 – Listing 10.6<jTITLE></HEAD>
<BOOY>
<CENTER>
<H2>Speakers – Top 1000 Book Sales Rank</H2>
<CFOUTPUT>
There are tspeakers_1.recordcount# results.
</CFOUTPUT>
<CFOUTPUT QUERY·’speakers_1′>
#speaker_name# – #book_sales#
</CFOUTPUT>
<H2>Speakers – Top 1000-10000 Book Sales. Rank</H2>
<CFOlrrpUT>
There al’e #speakers_2.recordcount# results.
</CFOUTPUT>
<CFOUTPUT QUERY·’ sr.’akers_2 ‘>
#speaker_name# – #book_sales#
</CFOUTPUT>
</CENTER>
</BODY>
</HTML>

Stored procedures are generally found when working with enterprise database systems such as Oracle and SQL Server. Throughout this book you will be working with Microsoft Access and will not encounter stored procedures. While stored procedur can be used with Access it is highly uncommon. For more information about creating and using stored proc(· lures. consult your database system’s documentation and the ColdFusion documentation.

CFQUERYPARAM

CFQUERYPARAM is used to check what the data type of the query parameter is. It can be used for data validation and conversion of data’ types. It can also be used to update long text fields when used ill a SQL UPDATE statement. CFQUERYPARAM is placed in your SQL statement where the value you want to verify or validate is located. CFQUERYPARAMhas several attributes (see Table 11.7) and would be written as follows:

<CFQUERYPARAMVALUE-‘parameter_value’
CFSQLType-‘ parameter_type ,
MAXLENGTH-‘maximum_length’
SCALE-‘decimal_places’
NULL-‘Yes/No’
LIST-‘YES/NO’
SEPARATOR-‘liscseparator_characteo-‘>

TAB L E 1 1 7:

TAB L E 1 1 • 7:

The following CFSQLType values can be converted to numbers: CF_SQL_SMALLI NT,
CF_SQL_INTEGER,CF_SQL_REAL,CF_SQL_FLOAT,CF_SQL_DOUBLE,CF_SQL_TINYINT,
CF_SQL_MONEY,CF_SQL_MONEY4,CF_SQL_DEClMAL,CF_SQL_NUMERIC,and
CF_SQL_BIGINT .

The following CFSQLType values can be converted to date values: CF_SQL_DATE, CF_SQL_TIME and CF_SQL_TI~.{ESTAMP.

If a value cannot be converted to numeric data ColdFusion will throw an error. An example of using CFQUERYPARAMshisown in Listing 11.8. Figure 11.19 shows how the data appears in Access

<CFTRANSACTION>
<1 This query adds a new speaker to the speakers table —>
<CFQUERY NAME-‘add_speaker’
OATASOURCE-‘sybex’
OBTYPE-‘OO8C’>
INSERT INTO speakers (
speaker_first_name,
speaker_last_name
date_added
)
VALUES (
‘Kristin’ ,
<‘CIFoQIUoEtaRlgYhP’A,RAM VALUE-‘jCreateOOBCDate(Now())j’
CFSQLTYPE-‘CF_SQL_OATE’>
)
</CFQUERY>
<1 This query will retrieve the last inserted
speaker_id va1ue —>
<CFQUERY NAME-‘get_last_record’
OATASOURCE-‘sybex’
OSBETLYEPCET-‘MDAOXBCCs’pe>aker_id) as new_id
FROM speakers
</CFQUERY>
<! This query adds a new book to the speaker_books table —>
<CFQUERY NAME-‘add_speaker_book’
OATASOURCE-‘sybex’
OBTYPE-‘OOBC’>
INSERT INTO speaker_books (
speaker_id,
book_title,
book_price,
bookJank”
book_des,c,
book.Jlicture,
book_isbn,
book.Jlublished
)
VALUES (
*get_last_record.new_id#,
‘Mastering ColdFusion 4’ ,.
<CFQUERYPARAM VALUE-‘35.99’ CFSQLTYPE-‘CF_SQL_MONEY4’>,
1246,
<CFQUERYPARAM VALUE-‘Allaire’s Cold Fusion is the
leading development tool for building complex,
interactiv~ Web sites (it competes with Microsoft Visual
InterOev). Mastering Cold Fusion is aimed at experienced
HTML programmers who need to learn the ins and outs of
the software, as well as upgraders to the new version.
One of the authors, Kristin Motlagh, is a memher of Team
Allaire, a select group of Cold Fusion experts, and she
unqerstands what new users need to know to get up to
speed with this complex product. The book includes
information on both the Cold Fusion Application Server
and CF Studio, as well as reference material on Cold
Fusion tags, SQL, and other topics. The companion CO
comes with an evaluation version of the product, an
exensive collection of custom tags created by the
authors, and a wide variety of sample applications and
code from the book.’ CFSQLTYPE-‘CF_SQL_LONGVARCHAR’>,
,http://images. amazon ..com/images/P/0782124526 .01.LZZZZ7ZZ. gif’ ,
‘0782124526’ ,
<CFQUERYPARAM VALUE-‘#CreateOOBCOate(‘6/1/1999′)#’
CFSQLTYPE-‘CF_SQL_OA,E’>
)
</CFQUERY>
</CFTRANSACTION>
<!OOCTYPE HTML PUBLIC ‘-//W3C//OTO HTML 4.0 Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5-
Chapter 10 – Listing 10.7</TITLE></HEAD>
<BooY>
<CENTER>
<H2>The record has been inserted successfully!</H2>
<H3>Your new record IO is:
<CFOUTPUT QUERY-‘get_last_record’>#new_id#</CFOUTPUT></H3>
<.,’CENTER>
</BodY>
</HTML>

FIGURE·11.19

FIGURE·11.19

Query Functions

There are seven query-related functions that can be used in the development of your application They are as follows:

• IsQuery O returns TRUEif the value is a query and FALSEif it is not.
• Que rySetCe 11() resets the value of a column cell. .
• QueryAddCo1umn () adds a query column populating it with values from a one dimensional array.
• quoted value list  returns a list of values for each record with each list item enclosed in quotes.
• QueryAddRowO adds rows to an existing query result set.
• Va1ueL; stO returns a list of values for each record.
• QueryNewO returns an empty query

More detailed information on these functions can be tound in Appendix C, “Cold-Fusion Function Reference.”

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This