CF Database I

1
CF Database I
Jeff Peters jeff_at_grokfusebox.com
2
Why Are You Here?

• Data and Persistence
• ODBC
• Relational vs. Flat
• SQL
• CFQUERY, CFOUTPUT, CFLOOP
• Practicum

3
Data and Persistence

• Persistence Turn off, turn ondata still there.
• Secondary storage (disk or other nonvolatile)
• DBMS
• Excel
• Text file

4
ODBC

• Open DataBase Connectivity
• Allows a common interface to many databases.
• Does not homogenize SQL
• Must be configured on the CF server

5
Relational vs. Flat

• Relational Tables may be linked (related) to
  make storage more efficient.
• E.F. Codd
• Flat Tables cannot be linked. Think of a
  spreadsheet.

6
CFQUERY

• Allows any SQL statement to be run.
• Returns a recordset depending on SQL.
• Can pass authentication attributes
• USERNAME
• PASSWORD
• Can cache data for better performance.

7
CFOUTPUT

• Refers to a CFQUERY recordset to generate output
  to the browser.
• May be nested using the GROUP attribute.
• May NOT be nested without the GROUP attribute
  (see CFLOOP).

8
CFLOOP

• Refers to a CFQUERY recordset does not produce
  output to the browser.
• CFLOOP may also be used for incremental loops,
  lists, and objectsnot the scope of this session.

9
SQL

• Structured Query Language
• Common syntax for interacting with a database.
  (Beware variations)
• SELECT, INSERT, UPDATE, DELETE
• More complex commands available related to DBA
  functions.

10
Caching Queries

• Improves performance by storing data in server
  memory.
• Implemented through CFQUERY
• CACHEDWITHIN attribute

11
Practicum ODBC

• MS Access CFDB101
• webroot\cfun03\CFDB101.mdb
• MS Excel CFDB101Excel
• webroot\cfun03\Members.xls
• Text File CFDB101Text
• webroot\cfun03\TextDB\Members

12
Practicum CFQUERY

• SELECT
• INSERT
• DELETE
• UPDATE

13
Practicum CFQUERYSELECT
ltcfquery name"qryGetMembers"
datasource"CFDB101" dbtype"ODBC"gt
SELECT memberNumber, lastName,
firstName, email, phone,
memberSince FROM Members lt/cfquerygt ltc
fdump var"qryGetMembers"gt
14
Practicum CFQUERYSELECT
15
Practicum CFQUERYINSERT
ltcfquery datasource"CFDB101" dbtype"ODBC"gt
INSERT INTO Members (
memberNumber, lastName,
firstName, email, phone,
memberSince ) VALUES (
4, 'Adams', 'John Q.',
'jqada_at_grokfusebox.com', '7035554444',
CreateODBCDate(Now())
) lt/cfquerygt
16
Practicum CFQUERYINSERT
17
Practicum CFQUERYDELETE
ltcfquery datasource"CFDB101" dbtype"ODBC"gt
DELETE FROM Members WHERE memberNumber
'4' lt/cfquerygt
18
Practicum CFQUERYDELETE
19
Practicum CFQUERYUPDATE
ltcfquery datasource"CFDB101" dbtype"ODBC"gt
UPDATE Members SET phone '7035551212'
WHERE memberNumber '1' lt/cfquerygt
20
Practicum CFQUERYUPDATE
21
Practicum CFOUTPUT
ltcfquery name"qryGetMembers"
datasource"CFDB101" dbtype"ODBC"gt
SELECT lastName, firstName,
memberSince FROM Members ORDER BY
memberSince lt/cfquerygt lth3gt ltcfoutput
query"qryGetMembers"gt ltspan style"color
red"gt DateFormat(memberSince,"mm/dd/yyyy")
lt/spangt firstName lastNameltbrgt lt/cfoutputgt lt
/h3gt
22
Practicum CFOUTPUT
23
Practicum CFLOOP
ltcfquery name"qryGetMembers"
datasource"CFDB101" dbtype"ODBC"gt
SELECT lastName, firstName,
memberSince FROM Members ORDER BY
memberSince lt/cfquerygt lth3gt ltcfloop
query"qryGetMembers"gt ltcfset memberYears
DateDiff("YYYY",memberSince,Now())gt ltcfoutputgt
firstName lastName - Member for
memberYears years. ltbrgt
lt/cfoutputgt lt/cfloopgt lt/h3gt
24
Practicum CFLOOP
Thomas Jefferson - Member for 226 years. John
Adams - Member for 226 years. George Washington -
Member for 226 years.
25
Practicum Caching
ltcfquery name"qryGetMembers"
datasource"CFDB101" dbtype"ODBC"
cachedwithin"CreateTimeSpan(1, 0, 0, 0)"gt
SELECT memberNumber, lastName,
firstName, email, phone,
memberSince FROM Members lt/cfquerygt
26
Bonus Aqua Data Studio

• http//www.aquafold.com
• Interfaces with DB2, Informix, MySQL, Oracle,
  PostgreSQL, SQL Server, SyBase, any JDBC source.

27
Q A