5 Advanced SQL

5.1 Accessing SQL from a Programming Language

Database programmer must have access to a general programming language b/c…
- NOT all queries can be expressed in SQL
- Nondeclarative actions such as printing a report, or sending the results of a query to a graphical user interface; querying data is just one of the many components of an App. \[\\[.1cm]\]
There are 2 general approaches to accessing SQL through a general programming language: Dynamic SQL or Embbedded SQL
DYNAMIC SQL - allows general purpose programs to connect and communicate w/a database server using a colleciton of functions and methods
- JDBC - appplication program interface for Java
- ODBC - for C language, C++, C#, Ruby, Go, PHP, Visual Basic
- Python Database API - for Python

\[\\[.1cm]\]

EMBEDDED SQL - allows functions calls to connect to database using an API w/dynamic SQL facilities at runtime
JDBC - defines the API that Java programs use to connect to database servers
- orginally an abbreviation for Java Database Connectivity but full form is no longer used

\[\\[.1cm]\]

Connecting to a Database in JDBC:
- Select which database to use; can be an instance of Oracle running on local machine or PostgreSQL running on another machine
- Open a connection to the database using getConnection()
- getConnection() takes 3 parameters: URL/machine name of server, user identifier, and password

Before opening a connection, the JDBC driver must be dynamically loaded first w/specified protocol like ‘jdbc:oracle:thin:’ or ‘jdbc:mysql:’

Sending SQL statements to Database System in JDBC:
- the Statement class allows the programs to send SQL statements to the database system for execution
- a Statement object is NOT the SQL statement itself but rather an object that invokes methods that take in SQL statements as arguments and sends them to the database systems
- use excuteQuery() method for query statements
- use executeUpdate() method for nonquery statements (UPDATE, CREATE TABLE, INSERT, DELETE)

\[\\[.1cm]\] g. Handling Exceptions in JDBC: - executing SQL methods can result in an exception - the try {…} catch {…} construct can catch any JDBC errors - SQLexception - SQL-specific exception - Exception - regular Java errors

\[\\[.1cm]\]

prepareStatement() - method that allows us to predefine a query statement w/“?” as placeholders for actual values to provided by user
- always use prepared statements when taking an input from the user & adding it to a query
- NEVER create a query by concetanating strings of user entered inputs b/c if user enters certain special characters such as a single quote, the resulting SQL query my be syntactically incorrect
- luckily, setString() method checks for this & inserts necessary escape characters to ensure syntactic correctness

\[\\[.1cm]\]

SQL INJECTION - a code injection technique that might destroy your database
- one of the most common web hacking techniques
- the placement of malicious code in SQL statements via web place entered inputs
- Injection based on 1=1 is always TRUE
```
Ex. 
userID: 105 or 1=1
Corresponding SQL: SELECT * FROM users WHERE userID=105 OR 1=1
```

the sql is valid and will return all rows from the User Table since 1=1 is always True

Injection based on "“=”" is always True

Ex. username: " or ""="  
    password: " or ""=" 
    SQL: SELECT * FROM users WHERE Name="" or ""="" AND pass="" or ""=""

the sql is valid and will return all rows from the User Table b/c “‘=’” is always True

injection based on Batched SQL statements (group of 2 or more SQL statements seperated by semicolon)

Ex. userId: 105; DROP TABLE suppliers
SQL: SELECT userID FROM User where userId =105; DROP TABLE suppliers

\[\\[.1cm]\]

METADATA FEATURES - Java App does not have declarations for data stored in database & must either have it hard-coded or determine the info. directly at runtime
- when an executeQuery() method is executed, it returns a result set object, which contain the method getMetadata()
- getMetadata() returns ResultSetMetaData object w/methods to find Metadata info.
- getColumnCount() returns arity of the result relation
- getColumnName() returns names of the column
- getColumnTypeName() returns data types of columns
- getColumns() returns info. about columns
- getTable() returns list of all tables in database
- getPrimaryKeys()
- getCrossReferences returns foreign keys

\[\\[.1cm]\] k. Handling Large Object Types:

getBlob() & getClob() are similiar to getString(), but returns objects of type Blob & Clob that stores locators for large objects or logical pointers to the actual object in the database
getBytes() or getSubstring() fetches data from these objects
to update large objects, link an input stream to a Blob or Clob object like this…
```
blob.set(int parameterIndex, Input stream inputStream);
```

\[\\[.1cm]\]

OPEN DATABASE CONNECTIVITY (ODBC) - defines an API that apps can use to open a connection w/a database, send queries, & update and get back results
- apps such as GUI, spreadsheets, statistic packages can use ODBC

\[\\[.1cm]\] m. EMBEDDED SQL- defines the embeddings of SQL in variety of programming languages

host language - the language to which SQL queries are embedded
embbedded SQL - the SQL structures permitted in the host lang.

EXEC SQL is used in host lang. to identify embedded SQL requests to the preprocessor

Ex. 
general form: EXEC SQL < embedded SQL >; 
COBOL: semeicolon is replaced by END-EXEC
Java: embeddings use #SQL {....};

Apps must connect to database before executing any SQL statements
```
Ex. EXEC-SQL connect to server user username using password; 
```
Variables of host lang. can be used inside embedded SQL; they are preceded by a colon (:) & must be delcared within the DECLARE section
```
Ex. :credit_amount
```

to write an embedded SQL query, use ’declare c cursor for where c identifies the query

Ex. 
EXEC SQL 
      declare c cursor for 
      SELECT ID, name
      FROM student
      WHERE tot_cred > :credit_amount
END_EXEC

To actual execute the above query and save the results within a temporary relation, we must “open” it using EXEC-SQL open c;

\[\\[.1cm]\]

use a fetch statement to save one row of query results to host lang. variable.
```
Ex. EXEC-SQL fetch c into :si, :sn END_EXEC 
```
use close statement to delete temporary relation that holds the query results
```
Ex. EXEC SQL close c; 
```

5.2 Functions and Procedures

functions & procedures allow business logic to be stored in the database & executed from SQL statements
- they can be defined either by procedural SQL or by an external programming lang.
- SQL syntax for implementation varies across databases

\[\\[.1cm]\]

FUNCTIONS -

     -Ex. Function that returns count of instructors in a department.
     CREATE Function  dept_count(dept_name varchar(20))
           returns integer 
           begin 
           declare d_count integer 
                 SELECT COUNT(*) into d_count 
                 FROM instructor
                 WHERE instructor.dept_name = dept_name
           return d_count;
     End

     -Usage:
     SELECT dept_name, budget
     FROM department
     WHERE dept_count(dept_name) >12

\[\\[.1cm]\]

TABLE FUNCTIONS - functions that return tables as results

     - Ex
     CREATE function instructor_of(dept_name char(20))
           returns table(
                 ID varchar(5),
                 name varchar(20),
                 dept_name varchar(20), 
                 salary numeric (8,2))
           return table 
                 (SELECT ID, name, dept_name, salary
                 FROM instructor
                 WHERE instructor.dept_name = instructor_of.dept_name)

     -Usage
     SELECT * 
     FROM table (instructor_of('Music'))

Returns all instructors in the music department. For a simple query like this, we probably don’t need a function. Just write a query

\[\\[.1cm]\]

PARAMETERIZED VIEWS - generalized views created by table-valued functions & their parameters

PROCEDURES - often referred to as stored procedures; is a reusable unit of SQL statements & logic, compiled & stored together to perfom a specific task

     Ex. 
     CREATE procedure dept_count_pro (IN dept_name varchar(20), OUT d_count integer)
           begin 
                 SELECT COUNT(*) into d_count 
                 FROM instructor
                 WHERE instructor.dept_name = dept_count_proc.dept_name
           end

\[\\[.1cm]\]

Procedures can be invoked either from an SQL procedure or embedded SQL via a ‘cal’ statement
```
     Ex. 
     Declare d_count integer,
     call dept_count_proc('Physic', d_count);
```

Functions & Procedures can have the same name as other functions and procedures as long as the number of arguments are different or at least one argument is different

\[\\[.1cm]\]

PERSISTENT STORAGE MODULE (PSM)- gives SQL almost all the power of a general programming lang.

5.3 Triggers

TRIGGERS - a statement that the system executes automatically as a side effect of a modification to the database
to define a trigger, we must
- specify the conditions under which a trigger is to be executed
- specify the actions to be taken when the trigger is called

\[\\[.1cm]\]

Triggers can be activated before an event (INSERT, UPDATE, or DELETE)
Statement-level triggers
- use ‘for each statement’ instead of ‘for each row’ when executing a single action for all rows affected by the transaction
- use ‘referencing old table’ or ‘referencing new table’ to refer to temporary tables w/affected rows

\[\\[.1cm]\]

Triggers can be disable or enabled; default is enabled upon creation
Triggers can be dropped, which removes it permanently
Triggers were used earlier for tasks such as
- Maintaining summary data (e.g., total salary of each department)
- Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica \[\\[.1cm]\]
There are better ways of doing these now:
- Databases today provide built in materialized view facilities to maintain summary data
- Databases provide built-in support for replication \[\\[.1cm]\]
Encapsulation facilities can be used instead of triggers in many cases
- Define methods to update fields
- Carry out actions as part of the update methods instead of through a trigger \[\\[.1cm]\]
Risk of unintended execution of triggers, for example, when
- Loading data from a backup copy
- Replicating updates at a remote site
- Trigger execution can be disabled before such actions.

\[\\[.1cm]\]

Other risks with triggers:
- Error leading to failure of critical transactions that set off the trigger
- Cascading execution

5.4 Recursive Queries

Any recursive view must be a union of 2 subqueries: a base query that is NOT recursive & a recursive query that uses a recursive view
FIXED POINT - of the recursive view is the resultant view where not more new tuples are added
Recursive views make it possible to write queries, such as transitive closure queries, that cannot be written without recursion or iteration.