Home » Adobe Manuals » Computer Software » Adobe 38043740 » Manual Viewer

Adobe 38043740 Developing Applications - Page 411

Reading data from a database, SQL notes and considerations, Results of a SELECT statement

UPC - 883919135168

Add to My Manuals
Save this manual to your list of manuals

Page 411 highlights

DEVELOPING COLDFUSION 9 APPLICATIONS 406 Accessing and Using Data SQL notes and considerations When writing SQL in ColdFusion, keep in mind the following guidelines: • If you use a ColdFusion variable in your SQL expression, and the variable value is a string that contains single quotes, place the variable in a PreserveSingleQuotes function to prevent ColdFusion from interpreting the quotation marks. The following example shows this use: SELECT Name, Address1, Address2, City, Phone FROM Centers WHERE City IN (#PreserveSingleQuotes(List)#) • There is a lot more to SQL than what is covered here. It is a good idea to purchase one or several SQL guides for reference. • To perform a successful query, the data source, columns, and tables that you reference must exist. • Some DBMS vendors use nonstandard SQL syntax (known as a dialect) in their products. ColdFusion does not validate the SQL; it is passed on to the database for validation, so you are free to use any syntax that your database supports. Check your DBMS documentation for nonstandard SQL usage. Reading data from a database You use the SQL SELECT statement to read data from a database. The SQL statement has the following general syntax: SELECT column_names FROM table_names [ WHERE search_condition ] [ GROUP BY group_expression ] [HAVING condition] [ ORDER BY order_condition [ ASC | DESC ] ] The statements in brackets [] are optional. Note: There are additional options to SELECT depending on your database. For a complete syntax description for SELECT, see the product documentation. Results of a SELECT statement When the database processes a SELECT statement, it returns a record set containing the requested data. The format of a record set is a table with rows and columns. For example, if you write the following query: SELECT * FROM employees WHERE DeptID=3 The query returns a database table. Because the data returned to ColdFusion by a SELECT statement is in the form of a database table, ColdFusion lets you write a SQL query on the returned results. This functionality is called query of queries. For more information on query of queries, see "Accessing and Retrieving Data" on page 410. The next example uses a SELECT statement to return only a specific set of columns from a table: SELECT LastName, FirstName FROM employees WHERE DeptID=3 Filtering results The SELECT statement lets you filter the results of a query to return only those records that meet specific criteria. For example, if you want to access all database records for employees in department 3, you use the following query: SELECT * FROM employees WHERE DeptID=3 Last updated 1/20/2012

Section	Page
Contents	3
Chapter 1: What’s New	6
What’s new in ColdFusion 9.0 Update 1	6
What’s new in ColdFusion 9	10
ORM support	10
Flex and AIR integration	10
Adobe AIR applications	10
Enhanced Flash Remoting	10
Language enhancements	10
CFScript	10
onServerStart	11
Other language enhancements	11
Integration with other products and technologies	11
New Ajax controls	11
Integration with SharePoint	12
Office file interoperability	12
Integration with Apache Solr server	12
Performance enhancements	12
Granular control over caching	12
In-memory files	13
Improved Clustering	13
Other performance enhancements	13
Database enhancements	13
DataDirect	13
datasource is optional attribute	13
Code Analyzer	13
Service features	14
ColdFusion as a service	14
Other enhancements	14
Server Manager	14
Built-in support for portlet standards	14
PDF functionality	15
IMAP support	15
JRE specifications	15
Chapter 2: Introduction	16
Using the Developing ColdFusion Applications guide	16
About Adobe ColdFusion 9 documentation	16
Documentation set	16
Viewing online documentation	16
Chapter 3: Introducing ColdFusion	17
About Internet applications and web application servers	17
About web pages and Internet applications	17
About web application servers	17
About ColdFusion	18
Saving ColdFusion pages	19
Testing ColdFusion pages	19
Elements of ColdFusion	19
Verity Search Server	21
ColdFusion Administrator	21
About J2EE and the ColdFusion architecture	21
About ColdFusion and the J2EE platform	21
Chapter 4: The CFML Programming Language	22
Elements of CFML	22
CFML Basics	22
Comments	22
Tags	23
Tag syntax	23
Built-in tags	24
Custom tags	25
Tags as functions and operators	25
cflock	26
cftransaction	27
cfthread	27
Functions	34
Built-in functions	34
Implicit Get and Set Functions	34
User-defined functions	36
ColdFusion components	36
Constants	37
Variables	37
Expressions	37
Data types	37
Flow control	38
cfif, cfelseif, and cfelse	39
cfswitch, cfcase, and cfdefaultcase	39
cfloop and cfbreak	40
cfabort and cfexit	41
Character case	41
Special characters	42
Reserved words	42
CFScript	43
Using ColdFusion Variables	43
Creating variables	44
Variable naming rules	44
Variable characteristics	45
Data types	45
Numbers	46
Strings	47
Lists	48
Boolean values	48
Date-Time values	48
Binary data type and binary encoding	50
Complex data types	50
Multiple references to an object	53
Assigning objects to scopes	53
Copying and duplicating objects	53
Using a query column	53
Using periods in variable references	54
Understanding variables and periods	54
Creating variables with periods	56
Data type conversion	56
Operation-driven evaluation	56
Conversion between types	57
Evaluation and type conversion issues	58
Examples of type conversion in expression evaluation	60
About scopes	61
Scope types	61
Creating and using variables in scopes	63
Using scopes	65
Ensuring variable existence	66
Testing for a variable’s existence	66
Using the cfparam tag	67
Validating data	68
Passing variables to custom tags and UDFs	68
Passing variables to CFML tags and UDFs	68
Passing variables to CFX tags	69
Using Expressions and Number Signs	69
Expressions	69
Operator types	70
Operator precedence and evaluation ordering	73
Using functions as operators	73
Using multiple assignments in one expression	74
Using number signs	75
Using number signs in ColdFusion tag attribute values	75
Using number signs in tag bodies	76
Using number signs in strings	76
Nested number signs	77
Using number signs in expressions	77
Dynamic expressions and dynamic variables	77
About dynamic variables	78
About dynamic expressions and dynamic evaluation	78
Dynamic variable naming without dynamic evaluation	79
Using dynamic evaluation	80
Using the IIF function	83
Example: a dynamic shopping cart	84
Using Arrays and Structures	87
About arrays	88
Basic array concepts	88
About ColdFusion arrays	88
Basic array techniques	89
Referencing array elements	89
Creating arrays	89
Using implicitly created arrays	92
Adding elements to an array	92
Deleting elements from an array	93
Copying arrays	94
Populating arrays with data	94
Populating an array with the ArraySet function	95
Populating an array with the cfloop tag	95
Populating an array from a query	96
Array functions	97
About structures	98
Structure notation	98
Referencing complex structures	99
Creating and using structures	101
Creating structures	101
Using implicitly created structures in functions and tags	102
Adding and updating structure elements	102
Getting information about structures and keys	103
Copying structures	104
Deleting structure elements and structures	106
Looping through structures	106
Structure examples	107
Example file newemployee.cfm	107
Example file addemployee.cfm	109
Structure functions	110
Extending ColdFusion Pages with CFML Scripting	111
About CFScript	111
Comparing tags and CFScript	112
Language Enhancements in ColdFusion 9	113
New tag equivalents in CFScript	113
Script Functions added in ColdFusion 9	114
Reserved words introduced in ColdFusion 9	114
What is supported in CFScript	114
Tag equivalents in CFScript	114
Reserved words	116
Script functions	116
The CFScript language	116
Identifying CFScript	116
Variables	117
Expressions and operators	117
Statements	117
Statement blocks	117
Comments	118
Differences from JavaScript	118
CFScript limitation	119
Using CFScript statements	119
Using assignment statements and functions	119
Using conditional processing statements	119
Using looping statements	121
Using continue	125
Using break	125
for-in construct (for arrays)	125
var declaration within for loop	126
Defining components and functions in CFScript	126
Basic Syntax	126
Setting attributes	128
Specifying page encoding	128
Accessing component metadata	129
Support for creating custom metadata	129
Explicit function-local scope	130
Using system level functions	130
Import and new operations using cfimport	131
Handling exceptions	132
Exception handling syntax and rules	132
Exception handling example	133
CFScript example	133
Using Regular Expressions in Functions	136
About regular expressions	136
Using ColdFusion regular expression functions	137
Basic regular expression syntax	137
Regular expression syntax	138
Using character sets	138
Finding repeating characters	139
Case sensitivity in regular expressions	139
Using subexpressions	140
Using special characters	140
Using escape sequences	142
Using character classes	143
Using backreferences	144
Using backreferences in replacement strings	145
Omitting subexpressions from backreferences	146
Returning matched subexpressions	146
Specifying minimal matching	148
Regular expression examples	148
Regular expressions in CFML	149
Types of regular expression technologies	149
Chapter 5: Building Blocks of ColdFusion Applications	151
Creating ColdFusion Elements	151
About CFML elements that you create	151
Including pages with the cfinclude tag	151
Using the cfinclude tag	152
Recommended uses	153
About user-defined functions	153
Recommended uses	153
For more information	154
Using ColdFusion components	154
Recommended uses	154
For more information	154
Using custom CFML tags	155
Calling custom CFML tags	155
Recommended uses	156
For more information	156
Using CFX tags	156
Calling CFX tags	156
Recommended uses	157
For more information	157
Selecting among ColdFusion code reuse methods	157
Writing and Calling User-Defined Functions	158
About user-defined functions	158
Creating user-defined functions	158
Determining where to create a user-defined function	159
About creating functions using CFScript	159
Defining functions in CFScript	159
About creating functions by using tags	161
Defining functions by using the cffunction tag	161
Rules for function definitions	163
Calling user-defined functions	163
Working with arguments and variables in functions	164
Good argument naming practice	164
Passing arguments	164
Passing complex data	165
About the Arguments scope	166
Function-only variables	169
Referencing caller variables	170
Using arguments	170
Handling errors in UDFs	171
Displaying error messages	171
Providing status information	172
Using exceptions	174
A user-defined function example	176
Defining the function using CFScript	176
Defining the function using the cffunction tag	177
Using UDFs effectively	177
Using functions in ColdFusion component	177
Using Application.cfm and function include files	178
Specifying the scope of a function	178
Using function names as function arguments	179
Handling query results using UDFs	180
Identifying and checking for UDFs	181
Using the Evaluate function	181
Using recursion	181
Building and Using ColdFusion Components	182
About ColdFusion components	182
CFCs and object-oriented programming	182
When to use CFCs	183
Creating ColdFusion components	184
Tags for creating CFCs	184
Elements of a CFC	184
Building ColdFusion components	185
Defining component methods	185
Defining CFCs with related methods	186
Placing executable code in a separate file	186
Initializing instance data	187
Defining and using method parameters	188
Providing results	191
Using ColdFusion components	194
Tags for using CFCs	195
CFC invocation techniques	195
Instantiating CFCs	195
Invoking CFC methods with the cfinvoke tag	196
Using components directly in CFScript and CFML	198
Invoking CFC methods with forms and URLs	199
Accessing CFCs from outside ColdFusion and basic HTML	200
Specifying the CFC location	201
Passing parameters to methods	201
Passing parameters to methods by using the cfinvoke tag	201
Passing parameters in direct method invocations	202
Passing parameters in a URL	203
CFC variables and scope	203
The This scope	203
The Variables scope	203
The Arguments scope	205
Other variable scopes	205
Function local variables	205
Using CFCs effectively	206
Structuring and reusing code	206
Invoke a packaged component method with the cfinvoke tag	208
Building secure ColdFusion components	209
Using introspection to get information about components	210
Display metadata for a CFC	212
ColdFusion component example	212
Creating and Using Custom CFML Tags	213
Creating custom tags	213
Creating and calling custom tags	214
Securing custom tags	216
Accessing existing custom tags	216
Passing data to custom tags	216
Passing values to and from custom tags	216
Using tag attributes summary	218
Custom tag example with attributes	218
Passing custom tag attributes by using CFML structures	220
Managing custom tags	221
Executing custom tags	221
Accessing tag instance data	221
Handling end tags	222
Processing body text	223
Terminating tag execution	224
Nesting custom tags	224
Passing data between nested custom tags	225
Variable scopes and special variables	226
High-level data exchange	226
Example: ancestor data access	227
Building Custom CFXAPI Tags	229
What are CFX tags?	229
Before you begin developing CFX tags in Java	230
Sample Java CFX tags	230
Setting up your development environment to develop CFX tags in Java	230
Customizing and configuring Java	231
Writing a Java CFX tag	231
Calling the CFX tag from a ColdFusion page	231
Processing requests	232
Life cycle of Java CFX tags	233
ZipBrowser example	234
Approaches to debugging Java CFX tags	235
Outputting debugging information	235
Debugging in a Java IDE	236
Using the debugging classes	236
Developing CFX tags in C++	237
Sample C++ CFX tags	237
Setting up your C++ development environment	238
Compiling C++ CFX tags	238
Locating your C++ library files in UNIX	238
Implementing C++ CFX tags	238
Debugging C++ CFX tags	238
Registering CFX tags	239
Chapter 6: Developing CFML Applications	240
Designing and Optimizing a ColdFusion Application	240
About applications	240
Elements of a ColdFusion application	241
The application framework	241
Reusable application elements	241
Shared variables	241
Application events and the Application.cfc file	242
Other application-level settings and functions	242
Specifying settings per application	243
Application security and user identification	244
Structuring an application	244
How ColdFusion finds and process application definition pages	244
Defining the directory structure	245
Defining the application and its event handlers in Application.cfc	246
Defining application-level settings and variables	246
Using application event handlers	248
Managing the application with Application.cfc	249
Managing sessions in Application.cfc	250
Managing requests in Application.cfc	251
Handling errors in Application.cfc	252
Example: a complete Application.cfc	254
Migrating from Application.cfm to Application.cfc	257
Using an Application.cfm page	257
Naming the application	258
Setting the client, application, and session variables options	258
Defining page processing settings	258
Setting application default variables and constants	258
Processing logins	258
Handling errors	259
Example: an Application.cfm page	259
Optimizing ColdFusion applications	261
Caching ColdFusion pages that change infrequently	261
Caching parts of ColdFusion pages	262
Using Amazon S3 storage	263
Accessing Amazon S3	263
Bucket operations	264
Object operations	265
New attributes in cfdirectory action=\	266
ACLObject	267
Access control functions	267
storeSetACL	267
storeAddACL	268
storeGetACL	269
StoreGetMetadata	270
StoreSetMetadata	270
Standard keys	271
Working with in-memory files	272
Example	273
Example	273
Using in-memory files	273
Supported functions	274
File operations	275
Document and image actions	275
Custom tags	275
Using in-memory files in tags	275
Adding permissions	276
Accessing VFS information	276
Deleteing in-memory files	276
Limitations	277
Optimizing database use	277
Providing visual feedback to the user	280
Handling Errors	280
About error handling in ColdFusion	280
Understanding errors	281
About error causes and recovery	281
ColdFusion error types	281
About ColdFusion exceptions	282
How ColdFusion handles errors	284
Error messages and the standard error format	285
Determining error-handling strategies	286
Handling missing template errors	286
Handling form field validation errors	287
Handling compiler exceptions	287
Handling runtime exceptions	287
Specifying custom error messages with the cferror tag	288
Specifying a custom error page	288
Creating an error application page	289
Logging errors with the cflog tag	290
Handling runtime exceptions with ColdFusion tags	292
Exception-handling tags	292
Using cftry and cfcatch tags	293
Using the cftry tag: an example	296
Using the cfthrow tag	300
Using the cfrethrow tag	301
Example: using nested tags, cfthrow, and cfrethrow	302
Using Persistent Data and Locking	306
About persistent scope variables	306
ColdFusion persistent variables and ColdFusion structures	307
ColdFusion persistent variable issues	308
Managing the client state	308
About client and session variables	309
Maintaining client identity	309
Ensuring CFToken uniqueness and security	311
Providing Session security	311
Configuring and using client variables	312
Enabling client variables	312
Using client variables	314
Configuring and using session variables	316
What is a session?	316
Configuring and enabling session variables	317
Storing session data in session variables	318
Standard session variables	318
Getting a list of session variables	319
Creating and deleting session variables	319
Accessing and changing session variables	319
Ending a session	320
Configuring and using application variables	320
Configuring and enabling application variables	321
Storing application data in application variables	321
Using application variables	321
Using server variables	322
Locking code with cflock	323
Sample locking scenarios	323
Using the cflock tag with write-once variables	325
Using the cflock tag	325
Considering lock granularity	327
Nesting locks and avoiding deadlocks	327
Examples of cflock	329
Example with application, server, and session variables	329
Example of synchronizing access to a file system	332
Example of protecting ColdFusion extensions	332
Using ColdFusion Threads	333
About ColdFusion threads	333
Creating and managing ColdFusion threads	333
Starting a thread	334
Suspending a thread	334
Ending a thread	335
Joining threads	336
Using thread data	337
Thread scopes	337
Locking thread data and resource access	339
Metadata variables	339
Working with threads	340

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1,000
1,001
1,002
1,003
1,004
1,005
1,006
1,007
1,008
1,009
1,010
1,011
1,012
1,013
1,014
1,015
1,016
1,017
1,018
1,019
1,020
1,021
1,022
1,023
1,024
1,025
1,026
1,027
1,028
1,029
1,030
1,031
1,032
1,033
1,034
1,035
1,036
1,037
1,038
1,039
1,040
1,041
1,042
1,043
1,044
1,045
1,046
1,047
1,048
1,049
1,050
1,051
1,052
1,053
1,054
1,055
1,056
1,057
1,058
1,059
1,060
1,061
1,062
1,063
1,064
1,065
1,066
1,067
1,068
1,069
1,070
1,071
1,072
1,073
1,074
1,075
1,076
1,077
1,078
1,079
1,080
1,081
1,082
1,083
1,084
1,085
1,086
1,087
1,088
1,089
1,090
1,091
1,092
1,093
1,094
1,095
1,096
1,097
1,098
1,099
1,100
1,101
1,102
1,103
1,104
1,105
1,106
1,107
1,108
1,109
1,110
1,111
1,112
1,113
1,114
1,115
1,116
1,117
1,118
1,119
1,120
1,121
1,122
1,123
1,124
1,125
1,126
1,127
1,128
1,129
1,130
1,131
1,132
1,133
1,134
1,135
1,136
1,137
1,138
1,139
1,140
1,141
1,142
1,143
1,144
1,145
1,146
1,147
1,148
1,149
1,150
1,151
1,152
1,153
1,154
1,155
1,156
1,157
1,158
1,159
1,160
1,161
1,162
1,163
1,164
1,165
1,166
1,167
1,168
1,169
1,170
1,171
1,172
1,173
1,174
1,175
1,176
1,177
1,178
1,179
1,180
1,181
1,182
1,183
1,184
1,185
1,186
1,187
1,188
1,189
1,190
1,191
1,192
1,193
1,194
1,195
1,196
1,197
1,198
1,199
1,200
1,201
1,202
1,203
1,204
1,205
1,206
1,207
1,208
1,209
1,210
1,211
1,212
1,213
1,214
1,215
1,216
1,217
1,218
1,219
1,220
1,221
1,222
1,223
1,224
1,225
1,226
1,227
1,228
1,229
1,230
1,231
1,232
1,233
1,234
1,235
1,236
1,237
1,238
1,239
1,240
1,241
1,242
1,243
1,244
1,245
1,246
1,247
1,248
1,249
1,250
1,251
1,252
1,253
1,254
1,255
1,256
1,257
1,258
1,259
1,260
1,261
1,262
1,263
1,264
1,265
1,266
1,267
1,268
1,269
1,270
1,271
1,272
1,273
1,274
1,275
1,276
1,277
1,278
1,279
1,280
1,281
1,282
1,283
1,284
1,285
1,286
1,287
1,288
1,289
1,290
1,291
1,292
1,293
1,294
1,295
1,296
1,297
1,298
1,299
1,300
1,301
1,302
1,303
1,304
1,305
1,306
1,307
1,308
1,309
1,310
1,311
1,312
1,313
1,314
1,315
1,316
1,317
1,318
1,319
1,320
1,321
1,322
1,323
1,324
1,325
1,326
1,327
1,328
1,329
1,330
1,331
1,332
1,333
1,334
1,335
1,336
1,337
1,338
1,339
1,340
1,341
1,342
1,343
1,344
1,345
1,346
1,347
1,348
1,349
1,350

Match case Limit results 1 per page

406

DEVELOPING

COLDFUSION 9 APPLICATIONS

Accessing and Using Data

Las

upda

d 1/20/2012

SQL notes and considerations

When writing SQL in ColdFusion, keep in mind the following guidelines:

•

If you use a ColdFusion variable in your SQL expression, and the variable value is a string that contains single

quotes, place the variable in a PreserveSingleQuotes function to prevent ColdFusion from interpreting the

quotation marks. The following example shows this use:

SELECT Name, Address1, Address2, City, Phone

FROM Centers

WHERE City IN (#PreserveSingleQuotes(List)#)

</cfquery>

•

There is a lot more to SQL than what is covered here. It is a good idea to purchase one or several SQL guides for

reference.

•

To perform a successful query, the data source, columns, and tables that you reference must exist.

•

Some DBMS vendors use nonstandard SQL syntax (known as a dialect) in their products. ColdFusion does not

validate the SQL; it is passed on to the database for validation, so you are free to use any syntax that your database

supports. Check your DBMS documentation for nonstandard SQL usage.

Reading data from a database

You use the SQL SELECT statement to read data from a database. The SQL statement has the following general syntax:

SELECT column_names

FROM table_names

[ WHERE search_condition ]

[ GROUP BY group_expression ] [HAVING condition]

[ ORDER BY order_condition [ ASC | DESC ] ]

The statements in brackets [] are optional.

Note:

There are additional options to SELECT depending on your database. For a complete syntax description for

SELECT, see the product documentation.

Results of a SELECT statement

When the database processes a SELECT statement, it returns a

record set

containing the requested data. The format of

a record set is a table with rows and columns. For example, if you write the following query:

SELECT * FROM employees WHERE DeptID=3

The query returns a database table. Because the data returned to ColdFusion by a SELECT statement is in the form of

a database table, ColdFusion lets you write a SQL query on the returned results. This functionality is called

query of

queries

. For more information on query of queries, see “

Accessing and Retrieving Data

” on page

410.

The next example uses a SELECT statement to return only a specific set of columns from a table:

SELECT LastName, FirstName FROM employees WHERE DeptID=3

Filtering results

The SELECT statement lets you filter the results of a query to return only those records that meet specific criteria. For

example, if you want to access all database records for employees in department 3, you use the following query:

SELECT * FROM employees WHERE DeptID=3