Computer Science 380:

Principles of Database Systems

Chapter 5

Gregory M. Kapfhammer

creative commons licensed ( BY-NC-SA ) flickr photo shared by danmachold

Explore Advanced SQL Concepts

Programming Languages

Not all queries can be expressed in SQL

Many non-declarative actions are also needed

Examples of important non-declarative work?

Basic review: dynamic and embedded SQL

Challenge: data type mismatch

Database interaction with drivers: JDBC and ODBC

Great Challenge: Data Type Mismatches

JDBC in Java

#1: Connect to the Database

#2: Ship SQL to the Database

#3: Retrieve the Results of a Query

Database interactions with PreparedStatement

Avoiding SQL injection attacks

Transferring result data using a RowSet

Meta Data

What is meta data?

How can a program learn about database structure?

#1: Use an external database program

#2: Use the ResultSetMetaData

Extract details about database columns

Or, use the DatabaseMetaData!

Benefits: database browsers and generic code

SQL Embedding

Requires: host language and "embeddable" SQL code

Wait, this sounds very limited!

But, there are some benefits as well!

Type checking of the SQL interactions

Research prototypes are changing the situation

Dynamic Versus Embedded


What do we need to specify a trigger?

#1: An event to "fire" the trigger

#2: A condition that must be true

Why do we need triggers in a DBMS?

How do I specify a database trigger?

When should we not use triggers?