Connecting to Databases

1
Connecting to Databases
2
relational databases

• tables and relations
• accessed using SQL
• database -specific functionality
• transaction processing
• commit and rollback
• different SQL dialects
• auto-numbered table rows
• programming

3
connection technologies

• ODBC
• Access, SQLServer, most databases have an ODBC
  interface
• Excel, CSV files
• JDBC
• most databases have JDBC drivers
• OLE DB
• Windows only
• specific database drivers
• client libraries
• database specific APIs

4
common tasks

• locking mechanisms
• exclusive locks
• read-only (sharing) locks)
• database connection
• DSN
• userID
• password

5
common tasks

• query the database
• retrieve a result set
• process the result set
• error handling
• release resources

6
Database Connectivity with Perl CGI
7
DBM files

• classic Perl data format
• essentially persistent associative arrays
• elements are key-value pairs
• look up values using keys
• perform efficient insertion/deletion of values

8
CSV files

• Comma Separated Value text format
• use DBI module with DBDCSV module
• allows SQL manipulation of CSV files
• only simple queries possible
• slower than conventional text processing
• good prototyping approach before moving to an
  RDBMS

9
Perl DBI module

• abstract set of tools to interact with
  (relational) databases
• uses database specific drivers (DBD) to implement
  commands
• DBD modules
• DBDODBC
• DBDOracle
• DBDCSV etc

10
interaction with databases

• prepare
• parse SQL
• validate SQL
• create a statement handle
• execute
• passes statement to database
• database constructs query results

11
interaction with databases

• fetch
• query results pulled into Perl data structures
• processing then carried out on fetched data
• deallocation
• release of database resources at end of
  processing
• clears memory, removes any locks
• handled automatically

12
Example

• ...
• use DBI
• my db DBI-gtconnect ("dbiOraclefridge",
  "user", "pass")
• my sql db-gtprepare ("SELECT FROM
  FREEZER")
• sql-gtexecute()
• _at_items sql-gtfetchrow_array()
• ...

13
summary of Perl DBI

• powerful approach
• DBD modules available for most databases
• available on most platforms
• compatability problems with different Perl
  versions
• CGI not suitable for high-end systems
• speed and security problems
• flock command
• exclusive locks or sharing locks
• resources released when filehandles are destroyed

14
database connectivity with PHP
15
database-specific extensions

• written in C
• non-portable
• bound to one database
• fast
• optimised to database
• support all functionality
• non-standard interface
• significant code-rewriting to change database

16
PEAR DB Library

• database independent abstraction layer
• provided as standard with PHP
• sits on top of DB client libraries
• code more portable to other databases
• supports only standard DB functionality
• runs slightly slower than specific extensions

17
Example

• lt?php
• require_once ('DB.php')
• db DBconnect ("mysql//userpass_at_server/fridg
  e")
• sql "SELECT FROM FREEZER"
• q db-gtquery (sql)
• while (q-gtfetchInto(item))
• ...
• ?gt

18
PHP with PEAR DB

• powerful approach
• current technology of choice for small to
  mid-size 3-tier systems
• easy to implement
• portable across different platforms
• specific extensions can optimise performance
• limited use for high-end applications
• because of general PHP scaleability issues

19
database connectivity with JSP and servlets
20
JDBC

• Java API for Databases
• packaged with Java Enterprise
• interacts with three major database architectures
• relational
• main design focus of JDBC
• object-oriented
• data and methods bundled together in a class
  hierarchy
• object-relational

21
JDBC

• advantages
• object to relational mapping
• tables become objects with standard properties
  and methods
• database independence
• full Java support for distributed computing

22
JDBC driver types

• Type 1
• bridges to client libraries
• requires client software
• exampleJDBC-ODBC bridge
• Type 2
• native API drivers
• JDBC driver calls native methods
• native methods provided by database vendors
• requires client software

23
JDBC driver types

• Type 3
• generic client API
• uses sockets to call database-specific middleware
• most flexible
• can provide simultaneous access to multiple
  databases

24
JDBC driver types

• Type 4
• database specific, but networked
• talk directly to database using sockets
• use proprietary network protocols
• almost never documented
• purchased directly from database vendors

25
Example

• public class Fridge
• public static void main(String args)
• String url "jdbcmsql//fridge.com/fridge"
• Connection db null
• ...
• try
• db DriverManager.getConnection
  (url,"user","pass")
• Statement sql db.createStatement ()

26
Example

• String query "SELECT FROM FREEZER"
• ResultSet r sql.executeQuery (query)
• while (r.next())
• // do some stuff
• catch(Exception e)e.printStackTrace ()
• try db.close ()
• catch(Exception e)e.printStackTrace ()

27
summary of JDBC

• integrates well with JSP/servelet programming
• full power of Java programming
• robust error and exception handling
• suitable for mid-to-high-end 3- and multi-tier
  systems
• too sophisticated for simpler systems
• useful tools to ease development
• IDEs NetBeans, Eclipse
• Jakarta Turbine

28
database connectivity with ASP
29
ADO

• ActiveX Data Objects
• provide an API for accessing databases
• use OLE DB, ODBC
• Windows platform specific
• ASP processor creates standard objects to
  manipulate and retrieve data
• Command, Connection, Recordset, Record, Field,
  Error, Parameter, Property, Stream

30
ADO Objects

• Command ADODB.Command
• allows the manipulation of database commands
• execute method
• Connection ADODB.Connection
• represents a connection to a database
• has methods to execute, commit, rollback etc

31
ADO Objects

• Recordset ADODB.Recordset
• represents the records returned by a DB query
• iterates through records
• sorts and moves records
• adds and deletes records
• Record ADODB.Record
• represents a row in a RecordSet
• Field ADODB.Field
• represents a data field in a Record

32
ADO Objects

• Error ADODB.Error
• Parameter ADODB.Parameter
• Property ADODB.Property
• Stream ADODB.Stream

33
Example

• lt
• Set db Server.CreateObject ('ADODB.Connection')
  
• str "driverMySQL uid'user' pwd'pass'
  databaseFridge"
• 'open the connection.
• db.Open str
• 'create a recordset
• Set r Server.CreateObject ('ADODB.Recordset')
• ... continued ...

34
Example

• ... continued ...
• 'construct the SQL for the query
• sql "SELECT FROM FREEZER"
• 'get the data into the recordset
• r.Open sql, db
• ...
• 'release resources
• Set r Nothing
• Set db Nothing
• gt

35
summary of ASP and ADO

• powerful set of tools
• platform specific (Windows)
• versioning incompatabilities
• different versions of Windows
• different versions of ADO
• difficult to use
• improved under .NET framework
• not worth the effort for low-end systems
• PHP a better choice

36
database connectivity with ColdFusion
37
ColdFusion

• database connection through CFQUERY tag
• ODBC
• most databases
• standard functionality only
• OLE DB (Windows only)
• Access and SQL Server as standard
• others available
• Native drivers
• database specific
• database-specific extensions
• not portable

38
CFQUERY syntax

• ltCFQUERY
• NAME "query_name"
• DATASOURCE "datasource_name"
• DBTYPE "dbtype"
• CONNECTSTRING "connection_string"
• gt
• SQL statements
• lt/CFQUERYgt
• Accessed with ltCFOUTPUT QUERY "query_name"gt

39
Example

• ltCFQUERY NAME "fridge_query"
• DATASOURCE "Fridge"
• DBTYPE "ODBC"
• CONNECTSTRING "DRIVERMicroSoft Access Driver
  (.mdb)DBQD\dbfiles\Fridge.mdbFILMSAccessUID
  userPWDpass"
• gt
• SELECT FROM FREEZER
• lt/CFQUERYgt
• lt!---results now contained in a query object
  called fridge_query ---gt

40
summary of ColdFusion approach

• powerful tag-based DB interface
• no DB-specific knowledge required
• wide range of databases supported
• good for medium to high end systems
• data locking through CFLOCK