Introduction to Database Systems

1
Introduction to Database Systems

• CS363/607
• Lecture 20

2
SQL/CLI

• Instead of using a preprocessor, we can use a
  library of functions and call them as part of an
  ordinary C program.
• The library for C is called SQL/CLI Call-Level
  Interface.
• Embedded SQLs preprocessor will translate the
  EXEC SQL statements into CLI or similar calls,
  anyway.
• It is an adaptation of ODBC

3
Data Structures

• Header file sqlcli.h
• C connects to the database by structs of the
  following types
• Environments represent the DBMS installation.
  (SQLHENV)
• Connections logins to the database. (SQLHDBC)
• Statements records that hold SQL statements to
  be passed to a connection. (SQLHSTMT)
• Descriptions records about tuples from a query
  or parameters of a statement. (SQLHDESC)

4
Environments, Connections, and Statements

• Function SQLAllocHandle(hType,hIn,hOut) is used
  to create these structs, which are called
  environment, connection, and statement handles.
• hType is the type of handle desired, e.g.,
  SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT.
• hIn is the higher-level element in which the
  newly allocated element lives.
• hOut (address of) output handle.
• It returns a value of type SQLRETURN(an integer)
  0 (no error occurred).

5
Example

• Break executives netWorth into ranges
• include sqlcli.h
• SQLHENV myEnv
• SQLHDBC myCon
• SQLHSTMT execStat
• SQLRETURN errorCode1, errorCode2, errorCode3
• errorCode1SQLAllocHandle(SQL_HANDLE_ENV,
  SQL_NULL_HANDLE, myEnv)
• if(!errorCode1)
• errorCode2SQLAllocHandle(SQL_HANDLE_DBC,
  myEnv, myCon)
• if(!errorCode2)
• errorCode3SQLAllocHandle(SQL_HANDLE_STMT,
  myCon, execStat)

6
Processing statements

• SQLPrepare(sh, st, sl)
• A statement handle sh
• A pointer to an SQL statement st, and
• A length sl for the character string pointed to
  by st. If dont know the length, SQL_NTS can be
  used.
• SQLExecute(sh)
• Causes the statement to which handle sh refers to
  be executed.

7
Example (Cont.)

• SQLPrepare(execStat, SELECT netWorth FROM
  MovieExec, SQL_NTS)
• SQLExecute(execStat)
• Another approach If we will execute a statement
  S only once, we can combine PREPARE and EXECUTE
  with
• SQLExecDirect(execStat, SELECT netWorth FROM
  MovieExec, SQL_NTS)

8
Fetching Tuples

• When the SQL statement executed is a query, we
  need to fetch the tuples of the result.
• That is, a cursor is implied by the fact we
  executed a query, and need not be declared.
• SQLFetch(sh) gets the next tuple from the result
  of the statement with handle sh.

9
Accessing Query Results

• When we fetch a tuple, we need to put the
  components somewhere.
• Thus, each component is bound to a variable by
  the function SQLBindCol(sh, colNo, colType, pVar,
  varSize, varInfo).
• sh is the handle of the statement involved
• colNo is the number of component whose value is
  obtained
• colType is the type of variable into which the
  value of the component is to be placed.
• pVar is a pointer to the variable into which the
  value is to be placed
• varSize is the length in bytes (pVar)
• varInfo is a pointer to an integer that can be
  used by SQLBindcol to provide additional
  information

10
Example (Cont.)

• include sqlcli.h
• void worthRanges()
• SQLHENV myEnv
• SQLHDBC myCon
• SQLHSTMT execStat
• SQLINTEGER worth, worthInfo
• SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
  myEnv)
• SQLAllocHandle(SQL_HANDLE_DBC, myEnv, myCon)
• SQLAllocHandle(SQL_HANDLE_STMT, myCon,
  execStat)

11
Example (Cont.)

• SQLPrepare(execStat, SELECT netWorth FROM
  MovieExec, SQL_NTS)
• SQLExecute(execStat)
• SQLBindCol(execStat, 1, SQL_INTEGER, worth,
  size(worth), worthInfo)
• while(SQLFetch(execStat)!SQL_NO_DATA)
• digits1
• while((worth/10)gt0) digits
• if(digitslt14) countsdigits
• for(i0ilt15i)
• printf(digitsd number of execs d\n, I,
  countsi)

12
Passing parameters to queries

• Use SQLPrepare to prepare a statement in which
  some portions, called parameters are replaced by
  ?. The ith ? Represents the ith parameter.
• Use function SQLBindParameter to bind values to
  the places where the ?s are found.
• Execute the query with these bindings, by calling
  SQLExecute.

13
Example

• / get values for studioName and studioAddr /
• SQLPrepare(myStat, INSERT INTO Studio(name,
  address) VALUES(?, ?), SQL_NTS)
• SQLBindParameter(myStat, 1,, studioName, )
• SQLBindParameter(myStat, 2,, studioAddr, )
• SQLExecute(myStat)

14
JDBC

• Java Database Connectivity
• Similar to CLI.

15
Course Table
16
Student Table
17
Enrollment Table
18
Domain Constraints

• domain constraint

19
Primary Key Constraints

• Primary key constraint

20
Foreign Key Constraints

• Foreign key constraint

21
Domain Constraints

• Domain constraints specify the permissible values
  for an attribute. Domains can be specified using
  standard data types such as integers,
  floating-point numbers, fixed-length strings, and
  variant-length strings. The standard data type
  specifies a broad range of values. Additional
  constraints can be specified to narrow the
  ranges. You can also specify whether an attribute
  can be null.

22
Examples

• Create table
• Drop table
• Describe table
• Select
• Insert
• Delete
• Update

create table Course ( courseId char(5),
subjectId char(4) not null, courseNumber
integer, title varchar(50) not null,
numOfCredits integer, primary key (courseId) )
create table Student ( ssn char(9),
firstName varchar(25), mi char(1), lastName
varchar(25), birthDate date, street
varchar(25), phone char(11), zipCode
char(5), deptId char(4), primary key
(ssn) )
23
Examples, Cont.

• create table Enrollment (
• ssn char(9),
• courseId char(5),
• dateRegistered date,
• grade char(1),
• primary key (ssn, courseId),
• foreign key (ssn) references Student,
• foreign key (courseId) references Course
• )

24
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Delete
• Update

drop table Enrollment drop table Course drop
table Student
25
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Delete
• Update

describe Course
26
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Delete
• Update

select firstName, mi, lastName from Student where
deptId 'CS' select firstName, mi, lastName
from Student where deptId 'CS' and zipCode
02176' select from Student where deptId
'CS' and zipCode 02090'
27
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Delete
• Update

insert into Course (courseId, subjectId,
courseNumber, title) values ('10000', CMPSC',
131', Introduction to Programming', 3)
28
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Update
• Delete

update Course set numOfCredits 4 where title
'Database Systems'
29
Examples, Cont.

• Create table
• Drop table
• Describe table
• Select
• Insert
• Update
• Delete

delete Course where title 'Database System'
30
Why Java for Database Programming?

• First, Java is platform independent. You can
  develop platform-independent database
  applications using SQL and Java for any
  relational database systems.
• Second, the support for accessing database
  systems from Java is built into Java API, so you
  can create database applications using all Java
  code with a common interface.

31
Database Applications Using Java

• GUI
• Client/Server
• Server-Side programming

32
The Architecture of JDBC
33
The JDBC Interfaces

• Loading drivers
• Establishing connections
• Creating and executing statements
• Processing ResultSet

34
Developing JDBC Programs

• Loading drivers
• Establishing connections
• Creating and executing statements
• Processing ResultSet

Statement to load a driver Class.forName("JDBCDri
verClass") A driver is a class. For
example Database Driver Class
Source Access
sun.jdbc.odbc.JdbcOdbcDriver Already in
JDK MySQL com.mysql.jdbc.Driver
Website Oracle oracle.jdbc.driver.OracleD
river Website The JDBC-ODBC driver for
Access is bundled in JDK. MySQL driver class is
in mysqljdbc.jar Oracle driver class is in
classes12.jar To use the MySQL and Oracle
drivers, you have to add mysqljdbc.jar and
classes12.jar in the classpath using the
following DOS command on Windows classpathclass
pathf\cs363\mysqljdbc.jarf\cs363\classes12.ja
r
35
Developing JDBC Programs
Connection connection DriverManager.getConnectio
n(databaseURL)   Database URL Pattern
Access jdbcodbcdataSource
MySQL jdbcmysql//hostname/dbname
Oracle jdbcoraclethin_at_hostnameportor
acleDBSID   Examples For Access Connection
connection DriverManager.getConnection
("jdbcodbcExampleMDBDataSource") For
MySQL Connection connection DriverManager.getCo
nnection ("jdbcmysql//localhost/test") For
Oracle Connection connection
DriverManager.getConnection ("jdbcoraclerodgex
u_at_notitia.mcs.suffolk.edu1521orcl", "scott",
"tiger")

• Loading drivers
• Establishing connections
• Creating and executing statements
• Processing ResultSet

36
Developing JDBC Programs

• Loading drivers
• Establishing connections
• Creating and executing statements
• Processing ResultSet

Creating statement  Statement statement
connection.createStatement() Executing
statement (for update, delete, insert)  statement
.executeUpdate ("create table Temp (col1
char(5), col2 char(5))") Executing statement
(for select) // Select the columns from the
Student table ResultSet resultSet
statement.executeQuery ("select firstName, mi,
lastName from Student where lastName " "
'Smith'")
37
Developing JDBC Programs

• Loading drivers
• Establishing connections
• Creating and executing statements
• Processing ResultSet

Executing statement (for select) // Select the
columns from the Student table ResultSet
resultSet stmt.executeQuery ("select
firstName, mi, lastName from Student where
lastName " " 'Smith'") Processing
ResultSet (for select) // Iterate through the
result and print the student names while
(resultSet.next()) System.out.println(resultSet.
getString(1) " " resultSet.getString(2)
". " resultSet.getString(3))
38
import java.sql. public class SimpleJdbc
public static void main(String args)
throws SQLException, ClassNotFoundException
// Load the JDBC driver Class.forName("com.mys
ql.jdbc.Driver") System.out.println("Driver
loaded")   // Establish a connection
Connection connection DriverManager.getConnectio
n ("jdbcmysql//cartan.cas.suffolk.edu/rodg
exu") System.out.println("Database
connected")   // Create a statement
Statement statement connection.createStatement()
  // Execute a statement ResultSet
resultSet statement.executeQuery ("select
firstName, mi, lastName from Student where
lastName " " 'Smith'")   //
Iterate through the result and print the student
names while (resultSet.next())
System.out.println(resultSet.getString(1) "\t"
resultSet.getString(2) "\t"
resultSet.getString(3))   // Close the
connection connection.close()
39
ExampleAccessing Database from Java Applets

• This example demonstrates connecting to a
  database from a Java applet. The applet lets the
  user enter the SSN and the course ID to find a
  students grade.
• FindGrade

40
Processing Statements

• Once a connection to a particular database is
  established, it can be used to send SQL
  statements from your program to the database.
  JDBC provides the Statement, PreparedStatement,
  and CallableStatement interfaces to facilitate
  sending statements to a database for execution
  and receiving execution results from the database.

41
Processing Statements Diagram
42
The execute, executeQuery, and executeUpdate
Methods

• The methods for executing SQL statements are
  execute, executeQuery, and executeUpdate, each of
  which accepts a string containing a SQL statement
  as an argument. This string is passed to the
  database for execution. The execute method should
  be used if the execution produces multiple result
  sets, multiple update counts, or a combination of
  result sets and update counts.

43
The execute, executeQuery, and executeUpdate
Methods, cont.

• The executeQuery method should be used if the
  execution produces a single result set, such as
  the SQL select statement. The executeUpdate
  method should be used if the statement results in
  a single update count or no update count, such as
  a SQL INSERT, DELETE, UPDATE, or DDL statement.

44
PreparedStatement

• The PreparedStatement interface is designed to
  execute dynamic SQL statements and SQL-stored
  procedures with IN parameters. These SQL
  statements and stored procedures are precompiled
  for efficient use when repeatedly executed.
• Statement pstmt connection.prepareStatement
• ("insert into Student (firstName, mi, lastName)
  
• values (?, ?, ?)")

45
Example Using PreparedStatement to Execute
Dynamic SQL Statements

• This example rewrites the preceding example using
  PreparedStatement.
• FindGradeUsingPreparedStatement

46
Retrieving Database Metadata

• Database metadata is the information that
  describes database itself. JDBC provides the
  DatabaseMetaData interface for obtaining database
  wide information and the ResultSetMetaData
  interface for obtaining the information on the
  specific ResultSet.

47
DatabaseMetadata, cont.

• The DatabaseMetaData interface provides more than
  100 methods for getting database metadata
  concerning the database as a whole. These methods
  can be divided into three groups for retrieving
  general information, for finding database
  capabilities, and for getting object
  descriptions.

48
General Information

• The general information includes the URL,
  username, product name, product version, driver
  name, driver version, available functions,
  available data types and so on.

49
Obtaining Database Capabilities

• The examples of the database capabilities are
  whether the database supports the GROUP BY
  operator, the ALTER TABLE command with add column
  option, supports entry-level or full ANSI92 SQL
  grammar.

50
Obtaining Object Descriptions

• the examples of the database objects are tables,
  views, and procedures.

51
DatabaseMetaData dbMetaData connection.getMetaDa
ta() System.out.println("database URL "
dbMetaData.getURL()) System.out.println("database
username " dbMetaData.getUserName()) System
.out.println("database product name "
dbMetaData.getDatabaseProductName()) System.out.p
rintln("database product version "
dbMetaData.getDatabaseProductVersion()) System.ou
t.println("JDBC driver name "
dbMetaData.getDriverName()) System.out.println("J
DBC driver version " dbMetaData.getDriverVers
ion()) System.out.println("JDBC driver major
version " new Integer(dbMetaData.getDriverMaj
orVersion())) System.out.println("JDBC driver
minor version " new Integer(dbMetaData.getDri
verMinorVersion())) System.out.println("Max
number of connections " new
Integer(dbMetaData.getMaxConnections())) System.o
ut.println("MaxTableNameLentgh " new
Integer(dbMetaData.getMaxTableNameLength())) Syst
em.out.println("MaxColumnsInTable " new
Integer(dbMetaData.getMaxColumnsInTable())) conne
ction.close()
52
ExampleCreating an Interactive SQL Client
SQL Client.
53
Batch Updates

• To improve performance, JDBC 2 introduced the
  batch update for processing nonselect SQL
  commands. A batch update consists of a sequence
  of nonselect SQL commands. These commands are
  collected in a batch and submitted to the
  database all together.
• Statement statement conn.createStatement()
•  
• // Add SQL commands to the batch
• statement.addBatch("create table T (C1 integer,
  C2 varchar(15))")
• statement.addBatch("insert into T values (100,
  'Smith')")
• statement.addBatch("insert into T values (200,
  'Jones')")
•  
• // Execute the batch
• int count statement.executeBatch()

The executeBatch() method returns an array of
counts, each of which counts the number of the
rows affected by the SQL command. The first count
returns 0 because it is a DDL command. The rest
of the commands return 1 because only one row is
affected.
54
Example Copying Text Files to Table

• Write a program that gets data from a text file
  and copies the data to a table. The text file
  consists of the lines, each of which corresponds
  to a row in the table. The fields in a row are
  separated by commas. The string values in a row
  are enclosed in single quotes. You can view the
  text file by clicking the View File button and
  copy the text to the table by clicking the Copy
  button. The table must already be defined in the
  database.
• CopyFileToTable

55
Scrollable and Updateable Result Set

• The result sets used in the preceding examples
  are read sequentially. A result set maintains a
  cursor pointing to its current row of data.
  Initially the cursor is positioned before the
  first row. The next() method moves the cursor
  forward to the next row. This is known as
  sequential forward reading. It is the only way of
  processing the rows in a result set that is
  supported by JDBC 1.
•  
• With JDBC 2, you can scroll the rows both forward
  and backward and move the cursor to a desired
  location using the first, last, next, previous,
  absolute, or relative method. Additionally, you
  can insert, delete, or update a row in the result
  set and have the changes automatically reflected
  in the database.

56
Creating Scrollable Statements

• To obtain a scrollable or updateable result set,
  you must first create a statement with an
  appropriate type and concurrency mode. For a
  static statement, use
• Statement statement connection.createStatement
• (int resultSetType, int resultSetConcurrency)
• For a prepared statement, use
• PreparedStatement statement connection.prepareSt
  atement
• (String sql, int resultSetType, int
  resultSetConcurrency)
• The resulting set is scrollable
• ResultSet resultSet statement.executeQuery(query
  )

57
Example Scrolling and Updating Table

• Develop a useful utility that displays all the
  rows of a database table in a JTable and uses a
  scrollable and updateable result set to navigate
  the table and modify its contents. defined in the
  database.
• TestTableEditor TableEditor NewRecordDialog

58
(No Transcript)
59
SQL BLOB and CLOB Types

• Database can store not only numbers and strings,
  but also images. SQL3 introduced a new data type
  BLOB (Binary Large OBject) for storing binary
  data, which can be used to store images.
• Another new SQL3 type is CLOB (Character Large
  OBject) for storing a large text in the character
  format. JDBC 2 introduced the interfaces
  java.sql.Blob and java.sql.Clob to support
  mapping for these new SQL types. JBDC 2 also
  added new methods, such as getBlob,
  setBinaryStream, getClob, setBlob, and setClob,
  in the interfaces ResultSet and PreparedStatement
  to access SQL BLOB, and CLOB values.
•  
• To store an image into a cell in a table, the
  corresponding column for the cell must be of the
  BLOB type. For example, the following SQL
  statement creates a table whose type for the flag
  column is BLOB.
• create table Country(name varchar(30), flag blob,
  
• description varchar(255))

BLOB
CLOB
60
PHP Overview

• Easy learning curve
• Syntax Perl- and C-like syntax. Relatively easy
  to learn.
• Large function library
• Embedded directly into HTML
• Interpreted, no need to compile
• Open Source server-side scripting language
  designed specifically for the web.

61
PHP Overview, Cont.

• Conceived in 1994, now used on 10 million web
  sites.
• Outputs not only HTML but can output XML, images
  (JPG PNG), PDF files and even Flash movies all
  generated on the fly. Can write these files to
  the filesystem.
• Supports a wide-range of databases (20 ODBC).
• PHP also has support for talking to other
  services using protocols such as LDAP, IMAP,
  SNMP, NNTP, POP3, HTTP.
• Refer to php manual
• http//www.php.net/manual/en/

62
PHP Tags

• PHP code must be surrounded with special tags
• lt? PHP Code In Here?gtlt?php PHP Code
  In Herephp?gtltscript language"php"gt PHP
  Code In Herelt/scriptgt
• Write text to the browser with the echo command
• To write Hello, World! to the browser, include
  the following in hello.php
• lt?php echo lth2gtHello, Worldlt/h2gt ?gt

63
PHP Variables

• Variables Are the symbols we use to represent
  data.
• Variable names can be of any length can include
  letters, numbers and underscores cannot start
  with a digit case-sensitive and can have the
  same name as a function.
• To assign values to variables
• foo bar Data Type String
• foo 1 Data Type integer
• foo 5.34 Data Type Double
• foo array(bar,united) Data Type Array
• Data Types are automatically assigned though you
  can force a data type by type casting. For
  example
• foo Hello
• bar (int)foo
• bar now equals 0
• Almost all variables are local. Globals include
  _POST

Array names0 'Helen' names1
'Susan' names2 'Marc'
64
PHP Operators

• Operators Operators are symbols that you can use
  to manipulate values and variables by performing
  an operation on them.
• Includes
• Assignment (e.g. , , )
• Arithmetic (e.g. , -, )
• Comparison (e.g. lt, gt, gt, )
• Logical (e.g. !, , )

65
PHP Comments

• // This is a one-line comment
• // another line of comment
• print "Hello name" // Welcome to the user
• / This is a multi-line comment. Anything between
  the asterisks and the slashes will be ignored. It
  is important that you always close this type of
  comment as not doing so could make your script
  not work /
• Good code will use indents and comments.

66
PHP Control Structures

• Control Structures Are the structures within a
  language that allow us to control the flow of
  execution through a program or script.
• Grouped into conditional (branching) structures
  (e.g. if/else) and repetition structures (e.g.
  while loops).
• Example if/else if/else statement
• if (foo 0)
• echo The variable foo is equal to 0
• else if ((foo gt 0) (foo lt 5))
• echo The variable foo is between 1 and 5
• else
• echo The variable foo is equal to .foo

67
PHP - Forms

• Access to the HTTP POST and GET data is simple in
  PHP
• The global variables _POST and _GET contain
  the request data lt?php
• if (_POST"submit")
• echo "lth2gtYou clicked Submit!lt/h2gt"
• else if (_POST"cancel")
• echo "lth2gtYou clicked Cancel!lt/h2gt"
• ?gt
• ltform action"form.php" method"post"gt
• ltinput type"submit" name"submit"
  value"Submit"gt
• ltinput type"submit" name"cancel"
  value"Cancel"gt
• lt/formgt

68
Example

• Table contacts(
• first char(10),
• last char(10),
• phone char(12),
• mobile char(12),
• fax char(12),
• email varchar(20),
• website varchar(30)
• )

69
Example, Cont.

• query.php
• insert.php