Connecting to Databases

greenbeansneedlesSoftware and s/w Development

Dec 13, 2013 (4 years and 19 days ago)

61 views

Connecting to Databases

connecting to DB


DB server typically a standalone application


Server runs on
localhost
for smaller sites


i.e. Same machine as web server


Separate machine for larger sites


DB server examples:


MySQL, PostgreSQL
-

free


SQL Server, Oracle
-

commercial

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


data source name (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

DBM Example


File1.pl

dbmopen(%vals,"test.db",0666) ||
die("Can't open DB!");

$vals{'name1'}="Douglas";

$vals{'name2'}="Adams";

dbmclose(%map);


File2.pl

dbmopen(%vals,"test.db",0666) ||
die("Can't open DB!");

print $vals{'name1'};

print $vals{'name2'}

dbmclose(%map);


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


compatibility 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


E.g. @mysql_query(…), pg_query(…)


fast


optimised to database


support all functionality


non
-
standard interface


significant code
-
rewriting to change database


frameworks like Cake help

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

<?php

require_once ('DB.php');

$db = DB::connect
("mysql://user:pass@server/fridge");


$sql = "SELECT * FROM FREEZER";


$q = $db
-
>query ($sql);


while ($q
-
>fetchInto($item)) {



...


}


?>

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

database connectivity with JSP
and servlets

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

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 incompatibilities


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

<CFQUERY


NAME = "query_name"


DATASOURCE = "datasource_name"


DBTYPE = "dbtype"


CONNECTSTRING = "connection_string"

>


SQL statements

</CFQUERY>


Accessed with

<CFOUTPUT QUERY = "query_name">

Example


<CFQUERY NAME = "fridge_query"


DATASOURCE = "Fridge"


DBTYPE = "ODBC"


CONNECTSTRING = "DRIVER=MicroSoft Access Driver
(*.mdb);DBQ=D:
\
dbfiles
\
Fridge.mdb;FIL=MSAccess
;UID=user;PWD=pass"


>



SELECT * FROM FREEZER


</CFQUERY>


<!
---
results now contained in a query object
called fridge_query
---
>

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