CSE 190 Internet ECommerce Lecture 13 Database code
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 1. Create ADO connection 2. Open the DB connection 3. Create an ADO Recordset 4. Open the Recordset 5. Extract the fields from the recordset 6. Close the recordset 7. Close the connection
ADO: Connections • • DSN: Analogous to a URL for a database DSN = Data Source Name
ADO: Connecting without DSN <% set connection = Server. Create. Object( “ADODB. Connection” ) connection. Provider = “Microsoft. Jet. OLEDB. 4. 0” Connection. Open “c: /demos/northwind. mdb” %>
ADO: Connecting with DSN <% set connection = Server. Create. Object( “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: Command. Timeout Seconds to wait for command to finish Connection. String used to create connection (e. g. northwind) State Whether connection is open or closed Methods: Execute SQL query, command, or statement Opens a connection Cancels a currently executing command Closes the connection Begin. Trans Begins a transaction Rollback. Trans Cancel changes in current transaction Commit. Trans Commits a transaction
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. Create. Object( “ADODB. Connection” ) connection. Open “northwind” set rs = Server. Create. Object( “ADODB. Recordset” ) rs. Open( “Select * from Customers” ), connection %>
ADO Recordset: Accessing Data <% set connection = Server. Create. Object( "ADODB. Connection“ ) connection. Open “northwind” set rs=Server. Create. Object( "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. Create. Object( "ADODB. Connection“ ) connection. Open “northwind” set rs=Server. Create. Object( "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 “ ” rs. Move. Next loop %>
ADO: Clean up <% set connection = Server. Create. Object( "ADODB. Connection“ ) connection. Open “northwind” set rs=Server. Create. Object( "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 “ ” rs. Move. Next loop rs. close connection. close %>
JDBC • • • 1. 2. 3. 4. 5. 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: 1. Specify driver class name in code: Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver“ ); 2. Specify driver via Java property: jdbc. drivers = sun. jdbc. odbc. Jdbc. Odbc. Driver
JDBC: Connecting to DB import java. sql. *; public class My. Test { public static void main( String[] args ) { String DSN = “jdbc: odbc: somedsn”; Connection conn = null; conn = Driver. Manager. get. Connection( DSN, “sa”, “” ); } }
JDBC: Executing SQL import java. sql. *; public class My. Test { public static void main( String[] args ) { String DSN = “jdbc: odbc: somedsn”; Connection conn = null; Statement statement = null; conn = Driver. Manager. get. Connection( DSN, “sa”, “” ); statement = conn. create. Statement(); Result. Set result = statement. execute. Query( “SELECT programmer, cups FROM Jolt. Data ORDER BY cups DESC; "); } }
JDBC: Access Recordset import java. sql. *; public class My. Test { public static void main( String[] args ) { String DSN = “jdbc: odbc: somedsn”; Connection conn = null; Statement statement = null; conn = Driver. Manager. get. Connection( DSN, “sa”, “” ); statement = conn. create. Statement(); Result. Set result = statement. execute. Query( “SELECT programmer, cups FROM Jolt. Data ORDER BY cups DESC; "); // for each row of data (note typed fields) while( result. next() ) { String name = result. get. String( “programmer” ); int cups = result. get. Int( “cups” ); } } }
JDBC: Clean up import java. sql. *; public class My. Test { public static void main( String[] args ) { String DSN = “jdbc: odbc: somedsn”; Connection conn = null; Statement statement = null; conn = Driver. Manager. get. Connection( DSN, “sa”, “” ); statement = conn. create. Statement(); Result. Set result = statement. execute. Query( “SELECT programmer, cups FROM Jolt. Data ORDER BY cups DESC; "); // for each row of data (note typed fields) while( result. next() ) { String name = result. get. String( “programmer” ); int cups = result. get. Int( “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 $lastnamen"; } if ($sth->rows == 0) { print "No names matched `$lastname'. nn"; } # Clean up statement $sth->finish; print "n"; print "Enter name> "; } # Clean up connection $dbh->disconnect;
References • ASP – http: //www. w 3 schools. com/ado_intro. asp • JDBC – http: //developer. java. sun. com/developer/onlin e. Training/Database/JDBCShort. Course/jdbc/jd bc. html • Perl – http: //www. perl. com/pub/a/1999/10/DBI. html
- Slides: 20