Transcript CF Database I

CF Database I
Jeff Peters
[email protected]
Why Are You Here?
Data and Persistence
ODBC
Relational vs. Flat
SQL
CFQUERY, CFOUTPUT, CFLOOP
Practicum
Data and Persistence
Persistence = Turn off, turn on—data still
there.
Secondary storage (disk or other nonvolatile)
DBMS
Excel
Text file
ODBC
Open DataBase Connectivity
Allows a common interface to many
databases.
Does not homogenize SQL
Must be configured on the CF server
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.
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.
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).
CFLOOP
Refers to a CFQUERY recordset; does not
produce output to the browser.
CFLOOP may also be used for incremental
loops, lists, and objects—not the scope of this
session.
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.
Caching Queries
Improves performance by storing data in
server memory.
Implemented through CFQUERY
CACHEDWITHIN attribute
Practicum – ODBC
MS Access: CFDB101
webroot\cfun03\CFDB101.mdb
MS Excel: CFDB101Excel
webroot\cfun03\Members.xls
Text File: CFDB101Text
webroot\cfun03\TextDB\Members
Practicum – CFQUERY
SELECT
INSERT
DELETE
UPDATE
Practicum – CFQUERY
SELECT
<cfquery name="qryGetMembers"
datasource="CFDB101"
dbtype="ODBC">
SELECT memberNumber,
lastName,
firstName,
email,
phone,
memberSince
FROM Members
</cfquery>
<cfdump var="#qryGetMembers#">
Practicum – CFQUERY
SELECT
Practicum – CFQUERY
INSERT
<cfquery datasource="CFDB101" dbtype="ODBC">
INSERT
INTO Members
(
memberNumber,
lastName,
firstName,
email,
phone,
memberSince
)
VALUES (
4,
'Adams',
'John Q.',
'[email protected]',
'7035554444',
#CreateODBCDate(Now())#
)
</cfquery>
Practicum – CFQUERY
INSERT
Practicum – CFQUERY
DELETE
<cfquery datasource="CFDB101" dbtype="ODBC">
DELETE
FROM Members
WHERE memberNumber = '4'
</cfquery>
Practicum – CFQUERY
DELETE
Practicum – CFQUERY
UPDATE
<cfquery datasource="CFDB101" dbtype="ODBC">
UPDATE Members
SET phone = '7035551212'
WHERE memberNumber = '1'
</cfquery>
Practicum – CFQUERY
UPDATE
Practicum – CFOUTPUT
<cfquery name="qryGetMembers"
datasource="CFDB101"
dbtype="ODBC">
SELECT lastName,
firstName,
memberSince
FROM Members
ORDER BY memberSince
</cfquery>
<h3>
<cfoutput query="qryGetMembers">
<span style="color: red">
#DateFormat(memberSince,"mm/dd/yyyy")#
</span>: #firstName# #lastName#<br>
</cfoutput>
</h3>
Practicum – CFOUTPUT
Practicum – CFLOOP
<cfquery name="qryGetMembers"
datasource="CFDB101"
dbtype="ODBC">
SELECT lastName,
firstName,
memberSince
FROM Members
ORDER BY memberSince
</cfquery>
<h3>
<cfloop query="qryGetMembers">
<cfset memberYears = DateDiff("YYYY",memberSince,Now())>
<cfoutput>
#firstName# #lastName# - Member for #memberYears# years.
<br>
</cfoutput>
</cfloop>
</h3>
Practicum – CFLOOP
Thomas Jefferson - Member for 226 years.
John Adams - Member for 226 years.
George Washington - Member for 226 years.
Practicum – Caching
<cfquery name="qryGetMembers"
datasource="CFDB101"
dbtype="ODBC"
cachedwithin="#CreateTimeSpan(1, 0, 0, 0)#">
SELECT memberNumber,
lastName,
firstName,
email,
phone,
memberSince
FROM Members
</cfquery>
Bonus – Aqua Data Studio
http://www.aquafold.com
Interfaces with DB2, Informix, MySQL,
Oracle, PostgreSQL, SQL Server, SyBase,
any JDBC source.
Q&A