COMP 378
Database Systems
Notes for Chapters 4 and 5 of
Database System Concepts
Advanced SQL
1
SQL Data Types and Schemas
1.1
Additional Data Types
1.1.1 User Defined Types
Idea: in some situations, data with the same internal representation is conceptually different and should not be treated interchangeably. For example, prices in both dollars and euros could be represented by type
numeric(12, 2), but a value in dollars should not be directly assigned to an attribute representing a price in euros, and prices in dollars and euros should not be compared directly. We would like the database to report an error in these situations.
In SQL,distinct types accomplish this goal. In SQL:1999, these types would be declared as:
create type Dollars as numeric(12,2) final create type Euros as numeric(12,2) final
DB2 does not use the keywordfinal, but does encourage the use of thedistinctkeyword and requires thewith comparisonsclause to disallow comparison with other numeric types:
create distinct type Dollars as numeric(12,2) with comparisons create distinct type Euros as numeric(12,2) with comparisons
After such declarations, these types can be used as domains for attributes:
create table convert(dols Dollars, eus Euros)
Because of the use of distinct types, queries such as:
select * from convert where dols < eus
and
select dols + 20 from convert
contain type errors, and these errors are detected and reported before the system attempts to execute the query (static type checking).
In SQL:1999, values of distinct types can be cast back to their source types using syntax such as
(cast dols to numeric(12,2)). In DB2, this would be written as cast(dols as numeric(12, 2)). Hence, the previous queries can be written as:
select * from convert where cast(dols as numeric(12, 2)) < cast(eus as numeric(12, 2))
and
select cast(dols as numeric(12,2)) + 20 from convert
Distinct types can be removed or modified by name using the drop type or alter type commands. Example:
drop type Dollars
SQL:92 also includes a create domain command that is used to declare types for attributes. (This command is not supported in DB2.) Thecreate domaincommand is much likecreate type, except that constraints on elements of the domain can be specified. Examples:
• A domain for manufacturers, assuming that the flyshop only stocks flyrods by particular manufacturers:
create domain manufacturer_type varchar(30) check(value in (’Orvis’, ’G. Loomis’, ’Winston’, ’Sage’))
• The keywordconstraint can be used to give a name to such a constraint. This allows the DBMS to report which constraint was violated (by name) when an error occurs.
create domain manufacturer_type varchar(30) constraint legal_manufacturers
check(value in (’Orvis’, ’G. Loomis’, ’Winston’, ’Sage’))
In either case, the domain manufacturer type would then be used as the domain of manufacturer in theflyrodrelation.
• a domain for customer names (which are not allowed to be null):
create domain custname_type varchar(30) check(value not null)
If manufacturer is of type manufacturer type in flyrod and name is of custname typein customer, a query such as:
select *
from customer, flyrod where name = manufacturer
would be permitted - unlike distinct types, values of different domains can be compared and assigned as long as the underlying types are compatible.
• ensuring that thestocknum can’t be null:
create domain fsntype int constraint fsnkey
check(value not null and value >= 0)
This kind of constraint is useful for primary key attributes.
1.1.2 Large-Object Types
Large-object types are used for attributes that can be relatively large (pictures, audio clips, large documents, ...). ANSI SQL provides two large-object types:
• clob - character large object, for large text documents
• blob - binary large object, for binary data (pictures, video clips, ...) DB2 supports these and an additional LOB type.
Example: creating a database to store movies:
create table movie(title varchar(30), year int, content blob(2G))
DB2 does not support LOB types larger than 2 GB. Values of these types are typically only useful for external applications, and so are retrieved using a “locater” (in DB2, a rowid) that an application program can use to refer to the object.
1.1.3 Schemas, Catalogs and Environments
To help in avoiding name clashes (i.e. two different users choose the same relation name when creating relations) ANSI SQL has a naming hierarchy. At the top level, the user or application program must connect to the desireddatabase. Within each database, there can be any number ofcatalogs, and within each catalog there can be any number of schemas. A relation or view can be uniquely identified using a three part name: catalog.schema.relationFor each database, each user has a default catalog and schema (as part of the SQL environmentfor the user), so these parts of the name can be omitted if the default is desired.
In DB2, the highest level in the hierarchy is an instance. Each instance is a separate installation of DB2. This is useful, for example, for maintaining a development and a production version of the database on the same server. The instance to use can be specified by setting theDB2INSTANCE environment variable or by using the ATTACH command. Within each instance there can be any number of databases, and a user must connect to the desired database. If theDB2DBDFTenvironment variable is set to a database name, an implicit connection to that database is automatically created. Within each database there can be any number ofschemas. The default schema for each user is the user name. The schema must be specified if the default is not desired (i.e. to refer to a table created by another user). TheCONNECT RESETcommand is used to close a database connection.
2
Integrity Constraints
Anintegrity constraintis a predicate that any valid instance of a database must satisfy. These constraints are meant to protect data from accidental damage or any changes that would make the data inconsistent. We’ve already seen several forms of integrity constraints:
• not null
• check clauses
• superkeys
• mapping cardinalities
• referential integrity
As testing integrity constraints can be time consuming, care must be taken when deciding what kinds of integrity constraints to enforce. Many older database systems explicitly limit the constraints that can be enforced, but SQL-92 (and later) do not.
2.1
Constraints on a Single Relation
SQL allows many constraints to be expressed in thecreate table command:
• not null
• unique
• primary key
• using acheckclause
For example, a check clause could be used to specify that eachstocknum in purchased must also occur inflyrod:
create table purchased ( custnum int not null, stocknum int not null, pdate date,
primary key (custnum, stocknum, pdate),
check (stocknum in (select stocknum from flyrod)))
2.2
Referential Integrity
In the relations purchased and flyrod, we expect that anystocknum appearing in a tuple of purchased also appears in a tuple offlyrod. In relational algebra:
Πstocknum(purchased)⊆Πstocknum(flyrod)
On the other hand, there may well be flyrod models that no one has purchased. That is, we do not require that:
Πstocknum(flyrod)⊆Πstocknum(purchased) Note that if:
Πstocknum(purchased)6= Πstocknum(flyrod)
then some tuples offlyrodorpurchased(or both) will not participate inflyrod⊲⊳purchased. A tuple that
does not participate in a natural join is called adangling tuple. Dangling tuples may indicate a consistency problem in the database.
Referential integrity constraintsspecify exactly when dangling tuples indicate a problem. Recall that a foreign key is a set of attributes in one relation that “refer to” the primary key attributes of another. Formally, let r1(R1) and r2(R2), with K ⊆R1 as the primary key for R1. A set of attributes α ⊆R2 is
a foreign key referencing r1 if for every t2 ∈r2, there must exist a tuple t1 ∈r1 such that t1[K] = t2[α].
Equivalently: Πα(r2)⊆ΠK(r1).
A constraint of this form is called a referential integrity constraint (or asubset dependency). Clearly, eitherK=αorK andαare compatible.
Example: attribute stocknum ofpurchased is a foreign key referencing thestocknum attribute offlyrod, and
Πstocknum(purchased)⊆Πstocknum(flyrod)
is a referential integrity constraint. Also, attribute custnum of purchased is a foreign key referencing the
custnumattribute ofcustomer, and
Πcustnum(purchased)⊆Πcustnum(customer)
is a referential integrity constraint.
Example: attribute stocknum offlyrod can NOT be a foreign key referencing the stocknumattribute of
purchased, because stocknumis not the primary key ofpurchased. Sources of referential integrity constraints:
• any relation derived from an n-ary relationship set (in the E-R model) containsnforeign keys
• any relation derived from a weak entity set contains a foreign key referencing the relation derived from the identifying entity set
• if a higher level entity set is explicitly represented by a relation, then the relations for each lower level entity set contain a foreign key referencing that relation
2.2.1 Testing Referential Integrity Constraints
To ensure that referential integrity constraints are preserved, the constraints must be tested each time the database is modified.
Consider a referential integrity constraint of the form: Πα(r2)⊆ΠK(r1)
• insertion: if tuple t2 is inserted intor2, the database system must test that:
t2[α]∈ΠK(r1)
• deletion: if tuple t1 is deleted fromr1, the system must test that no tuples ofr2 referencet1:
σα=t1[K](r2) ={}
If this condition is not satisfied, then either the deletion must be rejected, or else the offending tuples must be deleted fromr2. If there are more referential integrity constraints involvingr2, this may cause
more deletions.
• update:
– if a tuplet2∈r2 is updated on an attribute ofα, then the system must test that the new value
oft2satisfies:
t2[α]∈ΠK(r1)
(same as insert).
– if a tuplet1∈r1 is updated on an attribute ofK, then the system must test that no tuples ofr2
reference the old value oft1:
σα=t
1[K](r2) ={}
(same as delete).
2.2.2 Referential Integrity in SQL
When a table is created in SQL, foreign keys can be specified as part of thecreate table statement. By default, a foreign key references the primary key of the foreign relation. For example (creating thepurchased
relation):
create table purchased ( custnum int not null, stocknum int not null, pdate date not null,
primary key (custnum, stocknum, pdate), foreign key (custnum) references customer, foreign key (stocknum) references flyrod)
The implied referential integrity constraints are exactly those mentioned earlier.
If the foreign key contains only one attribute, then it can be declared as part of the attribute declaration, i.e.:
custnum int not null references customer,
If the attribute names are different in the foreign relation or if the foreign key references a candidate key (declared usingunique), the referenced attributes can be listed explicitly in theforeign key clause.
When a referential integrity constraint is violated, the default action is for the system to reject the modi-fication that caused the violation by rolling back the transaction that attempted to perform the modimodi-fication. Aforeign key clause can specify that other actions are taken to resolve the violation for a deletion or an update. These actions are modifications to one or more relations.
Example: suppose that the purchasedrelation is created as follows:
create table purchased ( ...
foreign key (stocknum) references flyrod on delete cascade, on update cascade)
• deleting a tuple in flyrodcauses all tuples in purchasedthat mention the deleted flyrod to be deleted as well
• if the stocknum of a flyrod is updated, all tuples in purchased that referred to the old stocknum are updated to refer to the new one.
In addition tocascade, the SQL-92 standard provides the following actions:
• set null, which causes the foreign key attribute values to be set to null for each tuple that violates the constraint
• set default, which causes the foreign key attribute values to be set to the default value for the domain for each tuple that violates the constraint (not supported by DB2).
Note that foreign key attributes are allowed to contain null, and any such tuple automatically satisfies the associated referential integrity constraint. However, it is usually a good idea to declare foreign key attributes asnot null.
2.3
Additional Properties of Constraints
Integrity constraints can be added to existing relations using the syntax:
alter tabletableaddconstraint
where table is the name of an existing relation and constraint is any of the constraints we have seen so far. When a constraint is added to a relation:
• the system checks that the relation satisfies the constraint
• if so, the constraint is added
• if not, the alter tablecommand is rejected
If a transaction consists of several SQL commands, some constraints may be violated in the middle of the transaction but re-established by the time that the transaction terminates. For example, a bank may have a constraint that the total balance in all accounts is greater than some minimum threshold. (This could be expressed using an assertion as described in Section 2.4.) If a transfer from one account to another is implemented by two SQL updatestatements and the first statement decreases the balance of an account, the constraint could be violated. However, the constraint would be satisfied after the money is deposited in the second account. The SQL standard allows constraints to be declared as:
• initially deferred - which means that they are only checked at the end of transactions
• deferred - which means that transactions can choose to defer checking them until the end of the transaction
So, we could avoid the problem described above by including the twoupdatestatements in the same trans-action (which they should be anyway) and declaring the assertion asinitially deferred
The default is to check constraints immediately, and many systems (including DB2) do not support deferred constraints.
2.4
Assertions
Anassertion is an arbitrary predicate that the database should satisfy at all times.
Example: we may want to ensure that the flyshop always stocks at least 5 distinct flyrod models. In SQL-92, the syntax for assertions is:
create assertionassertion-namecheckpredicate
where predicate is a condition much like the where clause of an SQL query. For example, the above assertion can be expressed as:
create assertion min_flyrods check (select count(distinct stocknum) from flyrod) >= 5
The database system must check that the assertion is satisfied at the time it is created, and must also ensure that any database modification does not cause the assertion to become false. If the assertion is initially valid (when created), then any database modification that would cause it to become false is rejected.
Because the testing necessary to ensure that assertions are satisfied can be expensive, they must be used with care. Many commercial products (including DB2) do not support assertions.
3
Triggers
A triggeris an action that is taken in response to a modification of the database. To write a trigger, we must specify:
1. what condition should cause the trigger to be executed 2. what action should be taken when the trigger executes
Triggers can be used to maintain integrity constraints, detect exceptional conditions, log changes, and so on. The action taken by a trigger can only be an SQL statement or sequence of statements.
Triggers are not included in the SQL-92 standard, but most relational database systems have supported some form of triggers for some time. Triggers were added to the SQL:1999 standard, but most systems still use proprietary syntax for them. However, the DB2 syntax for triggers is very similar to the SQL:1999 syntax.
Example: suppose we wanted to log all changes to employee salaries. We could add a relationlogsalwith schema Logsal-schema= (modified by, ssn, oldsal, newsal, modtime) that records who changed the salary, the social security number of the employee whose salary was changed, the old salary, the new salary and the date and time of the modification. Rather than inserting into this table by hand, we would prefer that this table be automatically updated whenever an employee’s salary was changed. A relation that records changes to other relations is called adeltaorchangetable, and logging changes is referred to as creating anaudit trail. Audit trails are useful for identifying the person who carried out incorrect or fraudulent updates.
In DB2, the above trigger would be defined as:
create trigger update_log
after update of salary on employee
referencing old as oldtuple new as newtuple for each row mode db2sql
insert into logsal
values (USER, oldtuple.ssn, oldtuple.salary, newtuple.salary, CURRENT TIMESTAMP)
Notes:
• USER and CURRENT TIMESTAMP are DB2 specific variables used to access the current user and the current date/time, respectively
• events/statements that can invoke triggers include: insert,delete,updateandselect(the SQL:1999 standard does not allow selectstatements to invoke triggers)
• a trigger can be invoked before (no cascade before) or after (after) the triggering event actually occurs. SQL:1999 usesbeforerather thanno cascade before
• triggers can be executed for each row affected by the modification or selection (using
for each row mode db2sql or just for each row in SQL:1999), or just once for the entire SQL statement doing the modification (using for each statement). The former are calledrow triggers, and the laterstatement triggers.
– for row triggers, the referencingclause is used to provide a way to refer to the value of a tuple being modified both before and after it is actually changed. The variables used to refer to the old and new tuple values are calledtransition variables.
– for statement triggers, thereferencing clause is used to refer totransition tables, which are temporary tables containing the affected tuples. The syntax referencing old table as and
referencing new table as is used for this. Transition tables can not be used with before
triggers. In DB2, statement triggers must also beafter triggers.
• an optional when clause can be used so that the trigger is only invoked under certain conditions. Example: we could define a trigger that was invoked only when an employee updated their own salary.
• the action to be taken is an SQL statement or a sequence of SQL statements inside: begin atomic...
end(i.e. a transaction)
• DB2 includes a signal statement that can be used within triggers. It raises an error condition and rolls back the triggering SQL statement. Thus, thesignalstatement can be used in abeforetrigger to prevent the triggering statement from occurring (for example, to prevent an employee from changing their own salary).
The same trigger in MySQL syntax:
create trigger update_log after update on employee for each row
insert into logsal
values (user(), new.ssn, old.salary, new.salary, now())
Triggers are much more commonly used than assertions, as they are usually less expensive to test. However, triggers should be used with care:
• if a trigger contains an error (i.e. violates a referential integrity constraint), then the triggering state-ment (i.e. the insertion, deletion or update) fails and is rolled back.
• the action of one trigger could cause another trigger to fire (which could cause another trigger to fire ...)
4
Security and Authorization
The data stored in a database must be secured against unauthorized access (reading), modification and deletion. Security measures must be taken at several levels:
• the database system
• the operating system
• the network
• the physical level
• the human level
4.1
Authorization
Each user (or group of users) of a database system can have different levels of authorization on different parts of the database (i.e. relations, views):
• instance authorization:
– insert authorization
– update authorization
– delete authorization
• schema authorization:
– index authorization
– resource authorization (which allows the creation of new relations)
– alteration authorization (which allows adding and deleting attributes)
– drop authorization (which allows the deletion of relations)
The user who creates a relation automatically has all authorizations on that relation. Other users can be given combinations of the possible authorizations. The database administrator automatically has all of these authorizations, as well as ability to authorize new users.
4.2
Authorization and Views
Recall that views can be used to restrict access to data. In particular, a user may be authorized to read (select) from a view, even though that user does not have read authorization on the relations used in defining the view. In particular, this allows the user to see some attributes of a relation (those included in the result of the view) but not others.
Creating a view does not require resource authorization, but a user who creates a view receives only the authorizations on the view that are implied by the user’s authorizations on the underlying relations. In particular, if the user does not have read access on all of the underlying relations, then the user can not use the view in a query. The database system will deny the view creation request if the user would have no authorization on the resulting view.
4.3
Authorization in SQL
In SQL, authorizations are calledprivileges. The standard privileges are:
• delete
• insert
• select
• update
• references
The references privilege on a relation gives the holder the right to declare foreign keys referencing that relation. If there were no references privilege, any user could prevent deletion of tuples from a relationr1by
creating another relationr2 with a foreign key referencingr1, and then inserting appropriate tuples intor2.
Privileges are granted to or revoked from individual users or groups of users (sometimes called roles). Groups are usually operating system groups, and are convenient when some set of users should all have the same privileges. A new user is then granted these privileges just by being added to the group.
The syntax of the grant statement is:
grantprivilege list onrelation or view nametouser/group list
For example:
grant select, insert, update on customer to salesperson
The update and references privileges can be granted on specific attributes of the relation (or view). For example:
grant update(name) on customer to salesperson
If no attributes are specified, then the privilege is granted on all attributes.
The privilegeall privilegesis a shorthand for all privileges, andpublicis a shorthand for all users. In DB2, the keywordsuserandgroupcan be used to disambiguate cases where a user and a group have the same name, and groups are defined by the operating system. In SQL:1999, roles are created using the
create rolestatement, and users are added to roles using the grant statement. For example:
create role salesperson grant salesperson to ted
grant select on customer to salesperson
gives user ted the select privilege oncustomer. All privileges granted to one role can be granted to another:
create role manager
grant salesperson to manager
Now, all members of themanagerrole have all privileges of thesalespersonrole, plus any additional privileges granted tomanager.
The user who creates a relation, view or role has all privileges on it, including the privilege to grant privileges to others. The keywordswith grant optionis used to confer grant privileges. For example:
grant select on customer to ted with grant option
gives the select privilege to user ted, and also allows ted to grant this privilege to others.
In DB2, the special privilegedbadmis used to grant database administrator privileges (i.e. all privileges, including the privilege to create and drop relations and to grant privileges to others) on a database. The
dbadmprivilege can only be granted by a special user called theinstance owner(created when the database instance is created), or by users to whom the instance owner has granted thesysadmprivilege on the instance.
Therevokestatement is used to revoke privileges. For example:
revoke select on customer from ted cascade
revokes the select privilege from ted, from any users or groups that ted has granted the privilege to. This is specified bycascade, but is usually the default. If both ted and some other user have granted the select privilege to user mary (for example), then revoking the privilege from ted does not revoke the privilege from mary.
The following revoke statement:
revoke select on customer from ted restrict
does not revoke the select privilege from those that ted has granted it to (because of the use ofrestrict). Shortcomings of SQL authorization:
• privileges can not be granted on individual tuples - only on relations, attributes, ... Hence, a user can not be granted only the privilege to view their own (personal) information, for example.
• privileges based on users and groups (or roles) do not work well for Web database access. Typically, one userid and password is hard coded into the application (CGI script, servlet, applet, ...) and is used for all Web access. Any more fine-grained authorization must be done by the application.
5
Application Programming with SQL
The ability to use SQL commands in general (Java, C, Cobol) programs is necessary because:
• not all queries can be expressed in SQL (without recursion)
• SQL does not permit communication with other programs Techniques: • embedded SQL • dynamic SQL • ODBC • JDBC • 4GLs
5.1
Embedded SQL
Embedded SQL refers to SQL commands that appear directly in host language programs, where the host language may be C, C++, Java, Fortran, Cobol, etc. The Java embedding of SQL is called SQLJ. Special syntax is used to mark embedded SQL statements. Before the program can be compiled, it must be fed through a preprocessor that replaces the embedded SQL statements with system commands.
Information is transferred between normal host language commands and embedded SQL commands through host language variables. Any variable used this way must be declared in a special program section. The following syntax is typical when C is the host language:
EXEC SQL begin declare section; double len, clen;
char manuf[40], n[40], addr[50]; EXEC SQL end declare section;
In the embedded SQL statements, these variables are preceded by : to distinguish them from SQL variables or constants.
If the embedded SQL is a select statement, the host program must declare a cursor that can be used to step through the result one tuple at a time. For example, if we want to find the names and addresses of everyone who has purchased a flyrod over a particular length, along with the length of that flyrod, we declare a cursor and associate it with the appropriate query:
printf("Enter the length: "); scanf("%lf", &clen);
EXEC SQL
declare c1 cursor for
select name, address, length from wahlst.customer, wahlst.purchased, wahlst.flyrod
where wahlst.customer.custnum = wahlst.purchased.custnum and wahlst.purchased.stocknum = wahlst.flyrod.stocknum and length > :clen;
printf("Printing names and addresses and\n"); printf("computing average flyrod length.\n");
The ; after clenends theEXEC SQLsection.
To use this query, we use an open statement to initialize the cursor, afetch statement to get the next tuple, and aclose statement to close the query, so that it could be reopened if the query were repeated. Thefetchstatement sets the variableSQLCODE, which can be checked for error conditions. In particular, an
#include <stdio.h> EXEC SQL INCLUDE SQLCA; main() {
int count = 0; double sum = 0.0; EXEC SQL begin declare section;
double len, clen; char n[40], addr[50]; EXEC SQL end declare section;
/* connect to the database */ EXEC SQL CONNECT TO flyshop;
printf("Enter the length: "); scanf("%lf", &clen);
EXEC SQL
declare c1 cursor for
select name, address, length from wahlst.customer, wahlst.purchased, wahlst.flyrod
where wahlst.customer.custnum = wahlst.purchased.custnum and wahlst.purchased.stocknum = wahlst.flyrod.stocknum and length > :clen;
printf("Printing names and addresses and\n"); printf("computing average flyrod length.\n"); EXEC SQL open c1;
while (1) { /* infinite loop */ EXEC SQL fetch c1 into :n, :addr, :len;
if (SQLCODE != 0) break;
printf("name: %-30.30s address: %s\n", n, addr); count++;
sum += len; }
/* close the cursor */ EXEC SQL close c1;
/* close the database connection */ EXEC SQL CONNECT RESET;
if (count > 0)
printf("The average length is: %lf\n", sum/count); else printf("No fly rods selected.\n");
}
Note that a database connection must be opened, and should be closed when the program is finished. SQL commands that do not return a result (i.e. update,insert, delete) do not require a cursor, and so can simply be executed.
Example: an insertion into flyrod:
long fsn, lw, inv; double len;
char manuf[40];
EXEC SQL end declare section;
printf("Enter the stock number: "); scanf("%d", &fsn);
printf("Enter the manufacturer: "); scanf("%s", manuf);
printf("Enter the length: "); scanf("%lf", &len);
printf("Enter the line weight: "); scanf("%d", &lw);
printf("Enter the initial inventory: "); scanf("%d", &inv);
/* connect to the database */ EXEC SQL CONNECT TO flyshop; EXEC SQL insert into wahlst.flyrod
values (:fsn, :manuf, :len, :lw, :inv); /* close the database connection */
EXEC SQL CONNECT RESET;
In embedded SQL, the entire SQL statement must be present in the host language program at the time that the program is preprocessed (statically). Dynamic SQL allows SQL statements to be constructed at runtime (in string variables). This is more flexible, but less efficient because the query evaluation plan for each query must be constructed at runtime.
5.2
JDBC
JDBC is a Java Application Programming Interface (API) that provides methods for database interaction. A JDBC program can execute on the database server, or can connect to a database on a remote server over a network.
Objects of classStatementare used to execute SQL statements. TheexecuteUpdate()method is used for insertions, deletions and updates (which don’t return a value). If an error occurs, executeUpdate()
throws anSQLException.
TheexecuteQuery()method is used to execute an SQL query. It returns an object of the classResultSet
(provided by the API), which acts as an iterator over the result of the query. Important instance methods ofResultSetinclude:
• next(), which advances the iterator to the next tuple of the result, returning false if the result is exhausted.
• various “get” methods (getString(),getInt(),getFloat()), which take either the column name or number as parameters, and return the value in that column for the current tuple as the indicated type. The string passed toexecuteUpdate()orexecuteQuery()can be built dynamically (at runtime).
/**
* basic demonstration of JDBC database connectivity * @author Tim Wahls
* @version 2/28/2014 */
import java.sql.*; import java.io.*; public class Flyshop {
public static void main(String argv[]) throws IOException {
BufferedReader in = new BufferedReader(new InputStreamReader(System.in)); try {
// load appropriate database drivers Class.forName("com.mysql.jdbc.Driver");
// connect to the database server and database
// a remote connection requires the server name, database name // and a userid and password for connecting
System.out.print("Enter your username: "); String user = in.readLine();
System.out.print("Enter your password: "); String pass = in.readLine();
Connection con = DriverManager.getConnection( "jdbc:mysql://mathcsdev.dickinson.edu/flyshop", user, pass);
Statement stmt = con.createStatement();
// the following statements can not be executed without the appropriate // privileges
// stmt.executeUpdate("delete from flyrod where stocknum = 4"); // stmt.executeUpdate("insert into flyrod values (4, ’St. Croix’," + // "8.0, 3, 5)");
System.out.print("Enter the length: "); String len = in.readLine();
ResultSet rset = stmt.executeQuery("select * from customer, " + "purchased, flyrod where customer.custnum = " +
"purchased.custnum and purchased.stocknum = " + "flyrod.stocknum and length > " + len);
System.out.println("Purchases of all flyrods over " + len + " feet long:");
while (rset.next()) {
System.out.println(rset.getString("name") + " " +
rset.getString("manufacturer") + " " +rset.getDouble("length")); }
// close the database connection con.close();
} catch (SQLException e) {
System.out.println("SQL Exception: " + e); } catch (ClassNotFoundException e) {
System.out.println("Class Not Found Exception: " + e); }
} }
constructed once), and then reused many times with different data values. For example (assuming a database connectionconhas already been established):
double len;
PreparedStatement pStmt = con.prepareStatement( "select * from customer, " +
"purchased, flyrod where customer.custnum = " + "purchased.custnum and purchased.stocknum = " + "flyrod.stocknum and length > ?");
// the ? marks the spot to be replaced by a value System.out.print("Enter the length (0 to quit): "); len = Double.parseDouble(in.readLine());
while (len != 0) {
// replace the first ? with the value of len pStmt.setDouble(1, len);
ResultSet rset = pStmt.executeQuery();
System.out.println("All flyrods over " + len + " feet long:"); while (rset.next()) {
System.out.println(rset.getString("name") + " " +
rset.getString("manufacturer") + " " + rset.getDouble("length")); }
System.out.print("Enter the length (0 to quit): "); len = Double.parseDouble(in.readLine());
}
Prepared statements are the preferred method for database insertions, as thesetStringmethod (anal-ogous tosetDoubleabove) automatically inserts appropriate escape characters. (Imagine inserting a string value containing’.)
JDBC also includes metadata features that allow programs to examine database schemas dynamically. This is useful, for example, for writing database table browsers and other administrative applications that need to work with all possible schemas. The following example shows how to query a database to get all tables in a particular schema, and how to find all columns of a particular table (again, assuming a database connectioncon).
DatabaseMetaData dbmd = con.getMetaData(); // print the names of all tables
// parameters to getTables: catalog, schema pattern,
// table name pattern, types
ResultSet rs = dbmd.getTables(null, null, "%", null); while (rs.next()) { System.out.println("catalog name"); System.out.println(rs.getString(1)); System.out.println("schema name"); System.out.println(rs.getString(2)); System.out.println("table name"); System.out.println(rs.getString(3)); System.out.println(); }
// print the names and domains of all columns in the flyrod table // parameters to getColumns: catalog, schema pattern,
// table name pattern, column pattern
rs = dbmd.getColumns(null, null, "flyrod", "%"); while (rs.next()) {
System.out.println(rs.getString("COLUMN_NAME")); System.out.println(rs.getString("TYPE_NAME")); }
The default in JDBC is for each SQL statement to execute as a separate transaction. This behavior is not appropriate for sequences of SQL statements that should execute as a single atomic action, such as a transfer from one bank account to another. To change this default behavior, use thesetAutoCommit()method of the
Connectionclass with parameterfalse(to turn off automatic committing). Then, any changes made to the database must be explicitly committed (using thecommit()method) or rolled back (using therollback()
method). For example (again, assuming a database connectioncon):
// turn autocommit off con.setAutoCommit(false);
// sequence of SQL statements forming a transaction here
// assume variable success indicates whether the transaction processed // successfully
if (success) {
// make any updates permanent in the database con.commit();
} else {
// undo any database updates con.rollback();
}
// turn autocommit back on if desired con.setAutoCommit(true);
5.3
Stored Procedures and Procedural Extensions
Astored procedure is a user-defined procedure that is installed on the database server and can be called from application programs. In DB2, a stored procedure can be written using static or dynamic embedded SQL (in any programming language that supports the embedding). Stored procedures are used primarily for reducing network traffic. For example, rather than returning the entire result of a query to an application program for further processing, a stored procedure can do the processing directly on the server.
SQL:1999 introduces procedures, loops, if statements and user defined types with methods and inheritance (i.e. classes). These extensions are not yet widely supported by commercial products.
5.4
Additional Subquery Features
A scalar subquery is a subquery that returns a single value (more precisely, a relation with one column and one row that can be treated as a single value). SQL:2003 (and DB2) allow scalar subqueries to be used wherever a value of the same type can be used. For example, to find all flyrods that are longer than the average flyrod length:
select * from flyrod
where length > (select avg(length) from flyrod)
To find the number of flyrods purchased by each customer (without using group by):
select name, (select count(*) from purchased
where purchased.custnum = customer.custnum) as numFlyRods from customer