Connecting to a Database through Programs

1
Connecting to a Database through Programs

• Database Systems

2
DB Connections

• Typically only DBA talk directly to a database
• End users generally connect to a database through
  some type of program
• Program code
• Forms

3
Connection Part 1

• Database tables are not part of a program
• So you have to establish a connection to them
• Connections are categorized in 2 categories
• READ
• Insert, Update and Delete

4
Not all DBs are Alike

• Not all databases act the same way, you have to
  know which way your database acts
• This determines the facilities needed for your
  connection
• Then you have to remember that relational
  database systems speak SQL

5
Connection Introduction

• Because database applications today reside in a
  complicated environment, various standards have
  been developed for accessing database servers.
• Some of the important standards are
• OBDC (Open Database Connectivity) is the early
  standard for relational databases.
• OLE DB is Microsofts object-oriented interface
  for relational and other databases.
• ADO.NET (Active Data Objects) is Microsofts
  standard providing easier access to OLE DB data
  for the non-object-oriented programmer.

6
The Role of the ODBC Standard
7
Open Database Connectivity (OBDC)

• The Open Database Connectivity (ODBC) standard
  provides a DBMS-independent means for processing
  relational database data.
• It was developed in the early 1990s by an
  industry committee and has been implemented by
  Microsoft and many other vendors.
• The goal is to allow a developer to create a
  single application that can access databases
  supported by different DBMS products without
  needing to be changed or recompiled.

8
ODBC Architecture
9
OBDC Components (Continued)

• OBDC consists of a data source, an application
  program, a driver manager, and a DBMS driver.
• A data source is the database and its associated
  DBMS, operating system, and network platform.
• An ODBC data source can be a relational database,
  a file server, or a spreadsheet.
• An applications program issues requests to create
  a connection with a data source.

10
OBDC Components

• A driver manager determines the type of DBMS for
  a given ODBC data source and loads that driver in
  memory.
• A DBMS driver processes ODBC requests and submits
  specific SQL statements to a given type of data
  source.

11
Conformance Levels

• Levels of conformance balance the scope of the
  OBDC standard.
• There are two types of conformance levels
• ODBC conformance levels concern the features and
  functions that are made available through the
  drivers application program interface (API).
• A driver API is a set of functions that the
  application can call to receive services.
• SQL conformance levels specify which SQL
  statements, expressions, and data types a driver
  can process.

12
Summary ofOBDC Conformance Levels
13
Summary ofSQL Conformance Levels
So, why do conformance levels matter? You pick
the ODBC driver you use based on your needs
14
How do you connect?

• Depends on the database and the programming
  environment
• Access can establish a DNS so that Access may be
  used as a front end to a database (ie front end
  to an oracle db)
• Java establishes a ODBC connection via the JDBC
  library
• C, VB.Net use a Microsoft provided Managed
  Provider, ADO.Net or OBDC

15
The Steps whatever the case

• Establish a connection
• Open the connection
• Select data using an Object with SQL statement
• Retrieve the Data
• Data is not used directly from the database
• Retrieved into hold place such as a variable or
  object
• Display the data
• OR Issue a Insert, Update or Delete command
• Send data to the database
• Usually number of rows affected is returned

16
Example

• Sample Code
• http//science.kennesaw.edu/mcmurray/dbTables/lab
  java/Customers.java
• JDBC walkthrough
• http//www.javacoffeebreak.com/articles/jdbc/index
  .html

17
Where does the data go?

• Insert, Update, Delete
• Pushing data out to the Database
• What is returned is the number of rows affected
• What happens when you read select data?
• Somehow you have to store the data in memory

18
Data retrieval

• How much data are you retrieving?
• One row its fairly trivial
• Use variables
• Lots of Rows
• Considerations to make and it will depend on the
  technology being used

19
Cursor

• Used by Oracle PL/SQL is known as cursor
• Way data is accessed row by row
• Can be utilized in Java

20
Example

• To retrieve data with SQL one row at a time you
  need to use cursor processing.
• Cursor processing is done in several steps
• Define the rows you want to retrieve. This is
  called declaring the cursor.
• Open the cursor. This activates the cursor and
  loads the data.
• Note that declaring the cursor doesn't load data,
  opening the cursor does.
• Fetch the data into variables.
• Close the cursor.
• http//www.fluffycat.com/SQL/Cursor-Oracle/

21
Cursor References

• http//www.exforsys.com/tutorials/oracle-9i/oracle
  -cursors.html
• http//www.oracle.com/technology/documentation/ber
  keley-db/je/GettingStartedGuide/Cursors.html

22
.NET

• Microsofts .NET uses
• Datareader
• Processing row by row
• Datasets
• Declared space in memory (data structure that
  looks like a table)
• Defined using XML schema
• Loads all results of select statements into
  memory for processing
• http//quickstart.developerfusion.co.uk/quickstart
  /howto/doc/adoplus/GetDataFromDB.aspx

23
Labs

• ACCESS connecting to Oracle
• http//aspalliance.com/893_Creating_an_MS_Access_2
  003_Front_End_for_an_Oracle_10g_Express_Edition_Da
  tabase
• ADO.Net using Visual Studio (for IS and ISA
  majors)
• Need an Oracle Managed Provider
• One is includes with Visual Studio but many
  problems reported so need to get one from Oracle
• Oracle 11g is has managed provided for ADO.Net
  built in
• JDBC for CS majors

24
SOME JAVA Examples

• http//java.sun.com/developer/codesamples/jdbc.htm
  l
• http//www.cs.ubc.ca/ramesh/cpsc304/tutorial/JDBC
  /jdbc1.html
• http//java.sun.com/j2se/1.3/docs/guide/jdbc/getst
  art/GettingStartedTOC.fm.html
• http//www.jdbc-tutorial.com/
• http//www.javacoffeebreak.com/articles/jdbc/index
  .html

25
Some MySQL PHP Resources

• Connecting to a MySQL database via PHP
• http//www.php-mysql-tutorial.com/connect-to-mysql
  -using-php.php
• http//dev.mysql.com/tech-resources/articles/ddws/
  21.html
• Resources for graphical support of forms
  development PHP
• http//phpmyadmin.qarchive.org/
• MySQL with JDBC
• http//www.developer.com/java/data/article.php/341
  7381