Understanding ADO in ASP: Connecting and Accessing Databases via ADO and JDBC

CSE 190: Internet E-Commerce Lecture 13: Database code

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)

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

ADO: Connections • DSN: Analogous to a URL for a database • DSN = Data Source Name

ADO: Connecting without DSN <% set connection = Server.CreateObject( “ADODB.Connection” ) connection.Provider = “Microsoft.Jet.OLEDB.4.0” Connection.Open “c:/demos/northwind.mdb” %>

ADO: Connecting with DSN <% set connection = Server.CreateObject( “ADODB.Connection” ) connection.Open “Northwind” %>

ADO: Creating a DSN • In Windows 2000, Settings -> Control Panel -> Administrative Tools -> 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

ADO: Connection Object Properties: Methods:

ADO: Creating Recordset • Recordset: A table of rows representing the results of a query or contents of an existing table • Recordset via SQL query: <% set connection = Server.CreateObject( “ADODB.Connection” ) connection.Open “northwind” set rs = Server.CreateObject( “ADODB.Recordset” ) rs.Open( “Select * from Customers” ), connection %>

ADO Recordset: Accessing Data <% set connection = Server.CreateObject( "ADODB.Connection“ ) connection.Open “northwind” set rs=Server.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 %>

ADO: Accessing Data from all rows <% set connection = Server.CreateObject( "ADODB.Connection“ ) connection.Open “northwind” set rs=Server.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 “<br>” rs.MoveNext loop %>

<% set connection = Server.CreateObject( "ADODB.Connection“ ) connection.Open “northwind” set rs=Server.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 “<br>” rs.MoveNext loop rs.close connection.close %> ADO: Clean up

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

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 property:jdbc.drivers = sun.jdbc.odbc.JdbcOdbcDriver

JDBC: Connecting to DB import java.sql.*; public class MyTest { public static void main( String[] args ) { String DSN = “jdbc:odbc:somedsn”; Connection conn = null; conn = DriverManager.getConnection( DSN, “sa”, “” ); } }

JDBC: Executing SQL import java.sql.*; public class MyTest { public static void main( String[] args ) { String DSN = “jdbc:odbc:somedsn”; 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;"); } }

JDBC: Access Recordset import java.sql.*; public class MyTest { public static void main( String[] args ) { String DSN = “jdbc:odbc:somedsn”; 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” ); } } }

JDBC: Clean up import java.sql.*; public class MyTest { public static void main( String[] args ) { String DSN = “jdbc:odbc:somedsn”; 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” ); } // Clean up conn.close(); } }

Perl DBI: Quick Overview use DBI; # Connect to DB my $dbh = DBI->connect( 'DBI:Oracle:payroll‘ ) or die "Couldn't connect to database: " . DBI->errstr; # Prepare SQL for execution my $sth = $dbh->prepare( 'SELECT * FROM people WHERE lastname = ?') or die "Couldn't prepare statement: " . $dbh->errstr; print "Enter name> "; while ($lastname = <>) { # Read input from the user my @data; chomp $lastname; $sth->execute( $lastname ) # Execute the query or die "Couldn't execute statement: " . $sth->errstr; # Fetch the record set while (@data = $sth->fetchrow_array()) { my $firstname = $data[1]; my $id = $data[2]; print "\t$id: $firstname $lastname\n"; } if ($sth->rows == 0) { print "No names matched `$lastname'.\n\n"; } # Clean up statement $sth->finish; print "\n"; print "Enter name> "; } # Clean up connection $dbh->disconnect;

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

Understanding ADO in ASP: Connecting and Accessing Databases via ADO and JDBC