CS 433 DB 2 Overview Client Access Queries

CS 433 DB 2 Overview Client Access, Queries, Stored Procedures, JDBC 9/12/01 Jeff Derstadt & Megha Batra

Administration n. Project Proposals n Due 09/14/01 by 5 PM n Email them to TAs n. Please register your group on the web ¨http: //www. cs. cornell. edu/courses/cs 433/2001 fa/

Client Configuration Must setup a client-server connection n Server name: n ¨ egret. csuglab. cornell. edu n Port: ¨ 50000 n Database name: ¨ Your group (g 7, g 23, etc. )

Client Configuration n Let’s configure our client!

Command Line Queries n Connect to database ¨ Connect to <database_name> user <user_name> using <password> n Issue queries ¨ CREATE TABLE <table> (<column_name> <datatype>, …) ¨ INSERT INTO <table> values (…) ¨ SELECT <columns> from <table>

Command Line Queries n Let’s issue some queries!

Stored Procedures n Definition: ¨A set of SQL and programming language (C++/Java) statements that are compiled into a named database object that can be invoked by a user application n Examples: ¨ For each sailor with a rating less than 5, insert her into the “Needs. Training” table, and email her requesting that she sign up for additional instruction ¨ Converting relational tables to Xml

Stored Procedures (2) n Advantages ¨ Can embed SQL in a ‘native’ programming language ¨ Executed on the database server: no network delay ¨ Compiled procedures can be invoked by any user application ¨ Provides consistent functionality (don’t need to rewrite SQL ‘inserts’, can just call a stored procedure)

Java n DB 2 allows you to write stored procedures in Java + Java is an easy language - Must use some sort of bridge between the native DB 2 code and the Java Virtual Machine to transfer data from a relational table to the stored procedure +/- JDBC provides this bridge: extra copy, but easy access

DB 2 to Java Virtual Machine Int sid = rs. get. Int(1); DB 2 String name = rs. get. String(2); (C++) Query Result 1 Copy from DB 2 to JDBC storage SID=5, NAME=Sarah 2 Result extracted from JDBC 3 Result. Set rs; JDBC (Java Database Connection)

JDBC java. sql. * n Using JDBC adds additional costs ¨ Copy from DB 2 to JDBC Result. Set object (2) ¨ Copy from Result. Set object to another Java variable (sometimes at 3) n However ¨ JDBC makes issuing SQL queries and gathering results very easy

JDBC Result. Set Provides a simple cursor object n A cursor is a set-of-records abstraction that allows you to move from one row to the next and access the columns: n rs. next() SID NAME 12 Sarah 13 Joe 15 Megha rs. get. String(2)

Stored Procedures n Let’s write a stored procedure that converts a table to Xml!

Questions ? ?