CSE 190: Internet E-Commerce

1
CSE 190 Internet E-Commerce

• Lecture 13 Database code

2
ASP Database code (ADO)

• ADO Microsoft standard for connecting to
  databases
• ADO Active Data Objects
• API is always accessible from within ASP
• ADO also used from within application tier
  components (i.e. VB, C components)

3
ADO

• Accessing a DB with ADO
• Create ADO connection
• Open the DB connection
• Create an ADO Recordset
• Open the Recordset
• Extract the fields from the recordset
• Close the recordset
• Close the connection

4
ADO Connections

• DSN Analogous to a URL for a database
• DSN Data Source Name

5
ADO Connecting without DSN

• lt
• set connection Server.CreateObject(
  ADODB.Connection )
• connection.Provider Microsoft.Jet.
  OLEDB.4.0
• Connection.Open c/demos/northwind.mdb
• gt

6
ADO Connecting with DSN

• lt
• set connection Server.CreateObject(
  ADODB.Connection )
• connection.Open Northwind
• gt

7
ADO Creating a DSN

• In Windows 2000, Settings -gt Control Panel -gt
  Administrative Tools -gt ODBC
• Choose System DSN, and then Add
• Enter any identifying string for the DSN
• Choose Select to specify where to find the
  source DB file

8
ADO Connection Object

• Properties
• Methods

CommandTimeout Seconds to wait for command to finish
ConnectionString String used to create connection (e.g. northwind)
State Whether connection is open or closed
Execute Execute SQL query, command, or statement
Open Opens a connection
Cancel Cancels a currently executing command
Close Closes the connection
BeginTrans Begins a transaction
RollbackTrans Cancel changes in current transaction
CommitTrans Commits a transaction
9
ADO Creating Recordset

• Recordset A table of rows representing the
  results of a query or contents of an existing
  table
• Recordset via SQL query
• lt
• set connection Server.CreateObject(
  ADODB.Connection )
• connection.Open northwind
• set rs Server.CreateObject( ADODB.Recordset
  )
• rs.Open( Select from Customers ), connection
• gt

10
ADO Recordset Accessing Data

• lt
• set connection Server.CreateObject(
  "ADODB.Connection )
• connection.Open northwind
• set rsServer.CreateObject( "ADODB.recordset )
• rs.Open "Select from Customers", connection
• for each x in rs.fields
• response.write( x.name )
• response.write( " )
• response.write( x.value )
• next
• gt

11
ADO Accessing Data from all rows

• lt
• set connection Server.CreateObject(
  "ADODB.Connection )
• connection.Open northwind
• set rsServer.CreateObject( "ADODB.recordset )
• rs.Open "Select from Customers", connection
• do until rs.EOF
• for each x in rs.fields
• response.write( x.name )
• response.write( " )
• response.write( x.value )
• next
• response.write ltbrgt
• rs.MoveNext
• loop
• gt

12
ADO Clean up

• lt
• set connection Server.CreateObject(
  "ADODB.Connection )
• connection.Open northwind
• set rsServer.CreateObject( "ADODB.recordset )
• rs.Open "Select from Customers", connection
• do until rs.EOF
• for each x in rs.fields
• response.write( x.name )
• response.write( " )
• response.write( x.value )
• next
• response.write ltbrgt
• rs.MoveNext
• loop
• rs.close
• connection.close

13
JDBC

• JDBC Java version of ODBC, providing same
  functionality as ADO
• ODBC pre-ADO DB connect technology from
  Microsoft
• Using JDBC
• Load the JDBC-ODBC bridge
• Connect to data source
• Execute SQL command
• Access Recordset
• Clean up

14
JDBC Loading ODBC bridge

• Must load Java driver to connect to database
• Two methods
• Specify driver class name in code Class.forName(
  "sun.jdbc.odbc.JdbcOdbcDriver )
• Specify driver via Java propertyjdbc.drivers
  sun.jdbc.odbc.JdbcOdbcDriver

15
JDBC Connecting to DB

• import java.sql.
• public class MyTest
• public static void main( String args )
• String DSN jdbcodbcsomedsn
• Connection conn null
• conn DriverManager.getConnection( DSN,
• sa, )

16
JDBC Executing SQL

• import java.sql.
• public class MyTest
• public static void main( String args )
• String DSN jdbcodbcsomedsn
• Connection conn null
• Statement statement null
• conn DriverManager.getConnection( DSN,
• sa, )
• statement conn.createStatement()
• ResultSet result statement.executeQuery(
• SELECT programmer, cups FROM JoltData
  ORDER BY cups DESC")

17
JDBC Access Recordset

• import java.sql.
• public class MyTest
• public static void main( String args )
• String DSN jdbcodbcsomedsn
• Connection conn null
• Statement statement null
• conn DriverManager.getConnection( DSN,
• sa, )
• statement conn.createStatement()
• ResultSet result statement.executeQuery(
  SELECT programmer, cups FROM JoltData ORDER
  BY cups DESC")
• // for each row of data (note typed fields)
• while( result.next() )
• String name result.getString( programmer
  )
• int cups result.getInt( cups )

18
JDBC Clean up

• import java.sql.
• public class MyTest
• public static void main( String args )
• String DSN jdbcodbcsomedsn
• Connection conn null
• Statement statement null
• conn DriverManager.getConnection( DSN,
• sa, )
• statement conn.createStatement()
• ResultSet result statement.executeQuery(
  SELECT programmer, cups FROM JoltData ORDER
  BY cups DESC")
• // for each row of data (note typed fields)
• while( result.next() )
• String name result.getString( programmer
  )
• int cups result.getInt( cups )

19
Perl DBI Quick Overview

• use DBI
• Connect to DB
• my dbh DBI-gtconnect( 'DBIOraclepayroll ) or
  die "Couldn't connect to database " .
  DBI-gterrstr
• Prepare SQL for execution
• my sth dbh-gtprepare( 'SELECT FROM people
  WHERE lastname ?') or die "Couldn't prepare
  statement " . dbh-gterrstr
• print "Enter namegt "
• while (lastname ltgt) Read input from the
  user
• my _at_data
• chomp lastname
• sth-gtexecute( lastname ) Execute the query
• or die "Couldn't execute statement " .
  sth-gterrstr
• Fetch the record set
• while (_at_data sth-gtfetchrow_array())
• my firstname data1
• my id data2
• print "\tid firstname lastname\n" if
  (sth-gtrows 0)
• print "No names matched lastname'.\n\n"

20
References

• ASP
• http//www.w3schools.com/ado/ado_intro.asp
• JDBC
• http//developer.java.sun.com/developer/onlineTrai
  ning/Database/JDBCShortCourse/jdbc/jdbc.html
• Perl
• http//www.perl.com/pub/a/1999/10/DBI.html