cf database i jeff peters [email protected]. why are you here? data and persistence odbc...
TRANSCRIPT
![Page 2: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/2.jpg)
Why Are You Here?
Data and Persistence
ODBC
Relational vs. Flat
SQL
CFQUERY, CFOUTPUT, CFLOOP
Practicum
![Page 3: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/3.jpg)
Data and Persistence
Persistence = Turn off, turn on—data still there.
Secondary storage (disk or other nonvolatile)
DBMS
Excel
Text file
![Page 4: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/4.jpg)
ODBC
Open DataBase Connectivity
Allows a common interface to many databases.
Does not homogenize SQL
Must be configured on the CF server
![Page 5: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/5.jpg)
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.
![Page 6: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/6.jpg)
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.
![Page 7: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/7.jpg)
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).
![Page 8: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/8.jpg)
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.
![Page 9: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/9.jpg)
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.
![Page 10: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/10.jpg)
Caching Queries
Improves performance by storing data in server memory.
Implemented through CFQUERY
CACHEDWITHIN attribute
![Page 11: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/11.jpg)
Practicum – ODBC
MS Access: CFDB101
webroot\cfun03\CFDB101.mdb
MS Excel: CFDB101Excel
webroot\cfun03\Members.xls
Text File: CFDB101Text
webroot\cfun03\TextDB\Members
![Page 12: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/12.jpg)
Practicum – CFQUERY
SELECT
INSERT
DELETE
UPDATE
![Page 13: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/13.jpg)
Practicum – CFQUERYSELECT
<cfquery name="qryGetMembers" datasource="CFDB101" dbtype="ODBC"> SELECT memberNumber, lastName, firstName, email, phone, memberSince FROM Members</cfquery>
<cfdump var="#qryGetMembers#">
![Page 14: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/14.jpg)
Practicum – CFQUERYSELECT
![Page 15: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/15.jpg)
Practicum – CFQUERYINSERT
<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>
![Page 16: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/16.jpg)
Practicum – CFQUERYINSERT
![Page 17: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/17.jpg)
Practicum – CFQUERYDELETE
<cfquery datasource="CFDB101" dbtype="ODBC"> DELETE FROM Members WHERE memberNumber = '4'</cfquery>
![Page 18: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/18.jpg)
Practicum – CFQUERYDELETE
![Page 19: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/19.jpg)
Practicum – CFQUERYUPDATE
<cfquery datasource="CFDB101" dbtype="ODBC"> UPDATE Members SET phone = '7035551212' WHERE memberNumber = '1'</cfquery>
![Page 20: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/20.jpg)
Practicum – CFQUERYUPDATE
![Page 21: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/21.jpg)
Practicum – CFOUTPUT<cfquery name="qryGetMembers" datasource="CFDB101" dbtype="ODBC"> SELECT lastName, firstName, memberSince FROM MembersORDER BY memberSince</cfquery>
<h3><cfoutput query="qryGetMembers"> <span style="color: red"> #DateFormat(memberSince,"mm/dd/yyyy")# </span>: #firstName# #lastName#<br></cfoutput></h3>
![Page 22: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/22.jpg)
Practicum – CFOUTPUT
![Page 23: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/23.jpg)
Practicum – CFLOOP<cfquery name="qryGetMembers" datasource="CFDB101" dbtype="ODBC"> SELECT lastName, firstName, memberSince FROM MembersORDER BY memberSince</cfquery>
<h3><cfloop query="qryGetMembers"> <cfset memberYears = DateDiff("YYYY",memberSince,Now())> <cfoutput> #firstName# #lastName# - Member for #memberYears# years. <br> </cfoutput></cfloop></h3>
![Page 24: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/24.jpg)
Practicum – CFLOOP
Thomas Jefferson - Member for 226 years.John Adams - Member for 226 years.George Washington - Member for 226 years.
![Page 25: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/25.jpg)
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>
![Page 26: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/26.jpg)
Bonus – Aqua Data Studio
http://www.aquafold.com
Interfaces with DB2, Informix, MySQL, Oracle, PostgreSQL, SQL Server, SyBase, any JDBC source.
![Page 27: CF Database I Jeff Peters jeff@grokfusebox.com. Why Are You Here? Data and Persistence ODBC Relational vs. Flat SQL CFQUERY, CFOUTPUT, CFLOOP Practicum](https://reader035.vdocuments.us/reader035/viewer/2022062519/5697bfae1a28abf838c9c739/html5/thumbnails/27.jpg)
Q & A