1 / 20

CSE 190: Internet E-Commerce

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.

velizabeth
Download Presentation

CSE 190: Internet E-Commerce

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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 <% set connection = Server.CreateObject( “ADODB.Connection” ) connection.Provider = “Microsoft.Jet.OLEDB.4.0” Connection.Open “c:/demos/northwind.mdb” %>

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

  7. 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

  8. ADO: Connection Object Properties: Methods:

  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: <% set connection = Server.CreateObject( “ADODB.Connection” ) connection.Open “northwind” set rs = Server.CreateObject( “ADODB.Recordset” ) rs.Open( “Select * from Customers” ), connection %>

  10. 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 %>

  11. 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 %>

  12. <% 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

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

  15. 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”, “” ); } }

  16. 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;"); } }

  17. 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” ); } } }

  18. 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(); } }

  19. 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;

  20. 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

More Related