Connecting to Databases

Connecting to Databases relational databases • tables and relations • accessed using SQL • database -specific functionality – transaction processing • commit and rollback – different SQL dialects – auto-numbered table rows – programming 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 common tasks • locking mechanisms – exclusive locks – read-only (sharing) locks) • database connection – DSN – userID – password common tasks • • • • • query the database retrieve a result set process the result set error handling release resources Database Connectivity with Perl CGI 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 CSV files • • • • • • Comma Separated Value text format use DBI module with DBD:CSV module allows SQL manipulation of CSV files only simple queries possible slower than conventional text processing good prototyping approach before moving to an RDBMS Perl DBI module • abstract set of tools to interact with (relational) databases • uses database specific drivers (DBD) to implement commands – DBD modules • DBD:ODBC • DBD:Oracle • DBD:CSV etc interaction with databases • prepare – parse SQL – validate SQL – create a statement handle • execute – passes statement to database – database constructs query results 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 Example ... use DBI; my $db = DBI->connect ("dbi:Oracle:fridge", "user", "pass"); my $sql = $db->prepare ("SELECT * FROM FREEZER"); $sql->execute(); @items = $sql->fetchrow_array(); ... 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 database connectivity with PHP 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 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 Example query ($sql); while ($q->fetchInto($item)) { ... } ?> PHP with PEAR DB • powerful approach • current technology of choice for small to midsize 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 database connectivity with JSP and servelets 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 JDBC • advantages – object to relational mapping – tables become objects with standard properties and methods – database independence – full Java support for distributed computing JDBC driver types • Type 1 – bridges to client libraries – requires “client” software • example:JDBC-ODBC bridge • Type 2 – – – – native API drivers JDBC driver calls native methods native methods provided by database vendors requires “client” software JDBC driver types • Type 3 • generic “client” API • uses sockets to call database-specific middleware • most flexible • can provide simultaneous access to multiple databases 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 Example public class Fridge { public static void main(String [] args) { String url = "jdbc:msql://fridge.com/fridge"; Connection db = null; ... try { db = DriverManager.getConnection (url,"user","pass"); Statement sql = db.createStatement (); 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 ();} } } 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 database connectivity with ASP 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 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 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 ADO Objects • • • • Error ADODB.Error Parameter ADODB.Parameter Property ADODB.Property Stream ADODB.Stream Example <% Set db = Server.CreateObject ('ADODB.Connection') str = "driver=MySQL; ; uid='user'; pwd='pass'; database=Fridge" 'open the connection. db.Open str 'create a recordset Set r = Server.CreateObject ('ADODB.Recordset') ‘... continued ... 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 %> 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 database connectivity with ColdFusion 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 CFQUERY syntax SQL statements • Accessed with Example SELECT * FROM FREEZER 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