cf database i n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CF Database I PowerPoint Presentation
Download Presentation
CF Database I

Loading in 2 Seconds...

play fullscreen
1 / 27

CF Database I - PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on

CF Database I. Jeff Peters jeff@grokfusebox.com. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CF Database I' - tamber


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cf database i
CF Database I
  • Jeff Peters
  • jeff@grokfusebox.com
why are you here
Why Are You Here?
  • Data and Persistence
  • ODBC
  • Relational vs. Flat
  • SQL
  • CFQUERY, CFOUTPUT, CFLOOP
  • Practicum
data and persistence
Data and Persistence
  • Persistence = Turn off, turn on—data still there.
  • Secondary storage (disk or other nonvolatile)
  • DBMS
  • Excel
  • Text file
slide4

ODBC

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

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.
slide6

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.
slide7

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).
slide8

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.
slide9

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.
slide10

Caching Queries

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

Practicum – ODBC

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

Practicum – CFQUERY

  • SELECT
  • INSERT
  • DELETE
  • UPDATE
practicum cfquery select
Practicum – CFQUERYSELECT

<cfquery name="qryGetMembers" datasource="CFDB101" dbtype="ODBC">

SELECT memberNumber,

lastName,

firstName,

email,

phone,

memberSince

FROM Members

</cfquery>

<cfdump var="#qryGetMembers#">

practicum cfquery insert
Practicum – CFQUERYINSERT

<cfquery datasource="CFDB101" dbtype="ODBC">

INSERT

INTO Members

(

memberNumber,

lastName,

firstName,

email,

phone,

memberSince

)

VALUES (

4,

'Adams',

'John Q.',

'jqada@grokfusebox.com',

'7035554444',

#CreateODBCDate(Now())#

)

</cfquery>

practicum cfquery delete
Practicum – CFQUERYDELETE

<cfquery datasource="CFDB101" dbtype="ODBC">

DELETE

FROM Members

WHERE memberNumber = '4'

</cfquery>

practicum cfquery update
Practicum – CFQUERYUPDATE

<cfquery datasource="CFDB101" dbtype="ODBC">

UPDATE Members

SET phone = '7035551212'

WHERE memberNumber = '1'

</cfquery>

practicum cfoutput
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>

slide23

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>

slide24

Practicum – CFLOOP

Thomas Jefferson - Member for 226 years.

John Adams - Member for 226 years.

George Washington - Member for 226 years.

slide25

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>

slide26

Bonus – Aqua Data Studio

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