SQL - Procedural Programming

Overview

Queries and expressions in SQL are ideal for large set-at-a-time operations:
- Operations that need to search the whole relation to answer a complex combination of tuples.
- Using SQL allows database to use the query optimizer to find the best query execution plan for the query.
However, SQL is not efficient for some programming tasks:
- Sequence of code: A block of queries that must be executed in an order
- Conditionals: if/then/else statements (some simple ones can be handled, but cannot be combined with sequence of code
- Loops
- Store values in and use values from program variables
- Error handling

Example Program

We will see an example program that is hard to write using an SQL query, but trivial with a procedural language.

Find the top 3 most frequent y for each x in table R(x,y,z).

Not very easy!

However, suppose we have a query:

SELECT x,y, count(*) num FROM R GROUP BY x,y ORDER BY x,y, num;

Now, we can write a simple program to compute this as follows:

Algorithm Top 3 y for each x:
   Run query:
        SELECT x,y, count(*) num
        FROM R
        GROUP BY x,y
        ORDER BY x,y, num;
   In a loop read each tuple such that:
       if a new x is found:
           read the next three y values
           (error handling if less than three y values are found)
           skip remaining x values

Procedural SQL

To enable the use of SQL for costly queries, while making it possible to write code/procedures on top of it, databases support a number of options.
The options belong in two main options:
- Server side
- Client side

Server side

Languages make it possible to define procedures, functions and triggers
These programs are compiled and stored within the database server
They can also be called by queries

Client side

Languages allow programs to integrate querying of the database with a procedural language
Coding in a host language with db hooks (C, C++, Java, Python, etc.) using the data structures of these languages
Coding in frameworks with their own data models (Java, Python, etc) with similar db hooks as in above.

Programming with SQL

All programming paradigms support:
- Methods to execute queries and update statements
  
  Executing any SQL statement and catching the outcome
- Interpret errors returned if any
- Input values from variables into queries, output query results into variables
- Loop over query results (for queries returning multiple tuples)
- Raise exceptions (which results in rollback of transactions)
- Store and reuse queries in the shape of cursors
- Starting and committing transactions
Client side programs also support:
- Opening/closing connections, allocating/releasing database resources for queries
The actual syntax of the methods for doing this vary, but the principle is the same.

Server side language examples

Generally database specific
Postgresql:
- pl/pgsql: generic procedural language for postgresql
- pl/pyhton: procedural language that is an extension of python (also see pl/tcl, pl/perl)

Client side language examples

Database specific extensions of host languages, for example
- libpq: C library for postgresql, uses library calls for specific to postgresql
- OCCI: Oracle library for C
Write code in C with hooks to the database in the form of functions and libraries, and compile using C compiler
- ECPG: embedded programming in SQL, based on the embedded programming standard with a postgresql specific pre-compiler, an the standard C compiler
Standard libraries for connecting to the database
- JDBC is a Java standard library, all databases implement the same functions (some non-standard data types will be different)
  
  To compile code with JDBC functions, the specific adapter (library) for a database must be used.
- No specific standard for libraries based on C, but ODBC is a non-language specific standard, .NET tries to achieve the same
- Python DB-API is a database independent framework for Python
  
  Similar to Java, a module for each database management system is needed. The postgresql module is called psycopg2.

Frameworks

Frameworks are based on specific design principles for developing database backed applications
Examples:
- Object-relational-mapping used by Rails, Hibernate, Django, WebObjects, .NET (different frameworks have different models)
- Note that the frameworks can be built on top of other languages (such as Java + JDBC)

pl/pgsql

Note that languages may be different, but the way that communicate with SQL is very similar.
We will go into detail of one language and then show similar concepts in other paradigms.

See full documentation here:

http://www.postgresql.org/docs/11/plpgsql.html
pl/pgsql supports the same data types as the database
Programs and functions can be compiled and used directly at the db server

Main pl/pgsql block has the form:

[ <<label>> ]
[DECLARE
variable declarations ]
BEGIN
statement
END [ label ] ;

Variable types:

integer
numeric(5)
varchar
tablename%ROWTYPE
tablename.columname%TYPE
RECORD

ROWTYPE and RECORD have subfields, i.e. x.name.

Programming constructs

Conditionals:

IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
Loops:
[ <<label>> ]
LOOP
statements
END LOOP [ label ];

Returning a value:

pl/pgsql functions do not allow you to modify input variables
RETURN will return a value. As a result, you can call it like a constant in the select statement shown below:

CREATE FUNCTION sales_tax(subtotal real, state varchar) RETURNS real AS $$
DECLARE
   adjusted_subtotal real ;
BEGIN
   IF state = 'NY' THEN
       adjusted_subtotal = subtotal * 0.08 ;
   ELSIF state = 'AL' THEN
       adjusted_subtotal = subtotal ;
   ELSE
       adjusted_subtotal = subtotal * 0.06;
   END IF ;
   RETURN adjusted_subtotal ;
END ;
$$ LANGUAGE plpgsql ;

Now, test it:

select sales_tax(100, 'NY') ;

sales_tax
-----------
8
(1 row)

Note: The whole body of the function is entered within the two $$ signs.
You can also return a table of rows:
- Return each tuple with RETURN NEXT and finish with RETURN
- As these return a table, they are called in the FROM clause. See the loop section below for examples.
Starting with Version 11, Postgresql also allows procedures that modify input variables. You can read about them in the documentation.

Handling SQL:

We will consider three types of SQL expressions:

Statements that return no output but a status (successful or not, and what was wrong)
Statements that return a single tuple
Statements that return multiple tuples

CREATE FUNCTION sales_tax(subtotal real) RETURNS boolean AS $$
DECLARE
   adjusted_subtotal real ;
BEGIN
   adjusted_subtotal = subtotal * 0.06;
   BEGIN
       INSERT INTO temp VALUES (adjusted_subtotal) ;
       RETURN true ;
   EXCEPTION WHEN unique_violation THEN
       RETURN false ;
   END ;
END ;
$$ LANGUAGE plpgsql ;

Now, when you run this function, a row is inserted into table temp.

Executing queries

When the query returns a single row, then we can read it directly into a variable.
Note that when using variables as input/output, pl/pgsql does not need any special delimiters (be careful naming the variables so as not to clash with column names)

Example:

  SELECT * INTO myrec FROM emp WHERE empname = myname;
  IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', myname;
  END IF;

- input: myname, output: myrec

Executing queries

When the query returns multiple rows, then a loop is needed to go through them one by one.
- A query returns a stream of tuples, which needs to be processed.
- Equally important is closing the stream associated with a query if required by the programming language.

Example:

[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];

DECLARE
myRow  RECORD ;
lastX      INT ;
yCnt       INT ;
BEGIN
   lastX = 0 ;
   yCnt = 0 ;
   FOR myRow IN
          SELECT x,y, count(*) as num
          FROM temp GROUP BY x,y ORDER BY x, num ASC LOOP
      yCnt = yCnt + 1;
      IF yCnt < 4 AND lastX = myRow.x THEN
          INSERT INTO temp2 VALUES(myRow.x, myRow.y, myRow.num) ;
      ELSIF lastX <> myRow.x THEN
          lastX = myRow.x ;
          yCnt = 1 ;
          INSERT INTO temp2 VALUES(myRow.x, myRow.y, myRow.num) ;
      END IF ;
   END LOOP ;
RETURN 1 ;
END ;

Note that procedure proc2() computes and inserts the top 3 y values (by count) for each x. Call it as:
```
SELECT proc2() ;
```

Here is an example of function that returns a table. Note that the table structure has to be defined in advance.

CREATE TABLE names (name VARCHAR(255)) ;

CREATE FUNCTION allnames() RETURNS SETOF names AS $$
DECLARE
   row RECORD ;
BEGIN
   FOR row in SELECT DISTINCT crsname FROM courses LOOP
       RETURN NEXT row ;
   END LOOP ;
   RETURN ;
END ;
$$ LANGUAGE plpgsql ;

Since this function returns a table, we call it in the FROM clause:

university=> select * from allnames();
          name
------------------------
Spellcasting
Practical Applications
Mazes
Alternate Dimensions
Minor Mendings
Transmogrification
Advanced Spellcasting
(7 rows)

You can also use the tables returned by functions in queries:

::
university=> select * from allnames() a, courses c where a.name=c.crsname;

Cursors

A cursor is a query with a handle. Cursors may have input.
Cursors may be defined once, and used many times to read tuples.

A query in a cursor is optimized once, reducing the cost of optimizing the query many times.
Functions may return reference to a cursor, allowing a program to read tuples that are returned.
Cursors provide a more efficient implementation of queries returning many tuples.

First, declare cursors:

DECLARE curs2 CURSOR FOR SELECT * FROM tenk1;

Then, execute the associated query by opening them:
```
OPEN curs2;
```
Then, retrieve tuples in the result using fetch:
```
FETCH curs2 INTO foo, bar, baz;
```

or

FOR recordvar IN curs2 LOOP

When finished, close the cursor to release allocated memory:
```
CLOSE curs2;
```
Cursors can also be used for update/delete if it is pointing to a specific tuple (similar to the notion of an updatable view). - Update/delete the tuple the cursor is pointing to.

Exceptions

When an SQL statement is executed, if it is not successful, it raises an error. This error can be caught in the usual try/catch format:
```
BEGIN
statement
EXCEPTION WHEN condition THEN
  statement
END ;
```
The exception conditions define integrity violations, statement errors, connection errors, etc.
The pl/pgsql statements can also raise exceptions to be caught by the calling statement:
```
RAISE NOTICE  ''
RAISE EXCEPTION ''
```
Also uncaught exceptions within the function will be raised when the function fails.

Other languages and formalisms for procedural programming

Given our introduction to programming with pl/pgsql, we will now cover some alternate languages to program in databases.
Each will have constructs similar to pl/pgsql, some will be generic and some will be database specific. I invite you to compare them to each other and notice similarities.

Client-side programming

Client-side programming languages have additional constructs to connect to a database, keep a pool of open connections and close connections.

Running and Debugging

Depending on how tight the integration with the database, error checking becomes an issue.
Two sources of syntax errors:
- Host language syntax errors
- SQL syntax errors: note that often these are not checked until you send the query to the server. So, it is difficult to debug.
Some client-side programming paradigms are a hybrid of host language and additional constructs.
- Example: Embedded SQL is an SQL standard for writing programs in a host language.
- There is a precompilation phase for only the SQL code and variables with better error checking.
- SQL calls are changed to calls in the host language after precompilation.
- Though writing programs in this paradigm can be a bit awkward and it is a less popular platform as a result.

Performance

One must be especially careful about sending large data sets over the network when writing client code.
When writing client-side code, you must balance the work that must be done in the server side and client side.
- Very complex queries slow down performance.
- Very simple queries lead to very large data sets being sent over the network.

Call level interfaces for client-side programming

Supplies the constructs for opening connections, running queries, looping over them etc.
SQL is completely handled with special function calls in the host language
JDBC is an industry standard for Java, supported by all databases using drivers.
Other database specific examples: OCCI for C in Oracle, Libpq for C in Postgresql

OCCI Example

OCCI is a C-library specific to Oracle, but it is designed to very closely resemble JDBC for Java which is a standard.

#include <occi.h>
using namespace oracle::occi;

Environment* const env = Environment::createEnvironment(Environment::DEFAULT);

Connection* const con = env->createConnection(user, pass, osid);

Statement* const s =
       con->createStatement("SELECT a.stageName"
                            " FROM movies.actors a"
                            " WHERE a.stagename like 'A%'");

Initialization

Each OCCI program must initialize an environment at the start of a program.

static Environment* Environment::createEnvironment (Environment::DEFAULT)

The Environment object contains the memory allocator and thread-library configuration for OCI.
You should explicitly terminate an environment at the end of a program to release all memory.
```
Environment::terminateEnvironment(env);
```

Connection

After initialization, you must open a connection to the specific database instance you are going to query.
```
Connection* Environment::createConnection (string username, string password, string sid)
```

It will authenticate the given user and password for the given instance id.

Connection* con = env->createConnection("scott",  "tiger", "ora9i");

A single connection can be used to query the same database multiple times in parallel or sequentially.
You should terminate your connection at the end of your program to release all memory at the client and the database server.
```
env->terminateConnection(con);
```

Querying

Once you have established a connection to the database, you are ready to execute queries and updates.
To execute a query, you will need to:
- Create an SQL statement and load it into a statement type object.
- Execute your query which will return one or more tuples.
- Create a resultset object that will allow you to iterate through the tuples returned by the query.
- Close your resultset object so that the database and your program releases the necessary memory.
- Close your statement if you will no longer use it. Note that you can use a single statement object repeatedly with different SQL queries.

Statements

Create a statement for a specific connection:

Statement* sel_all_stmt
con->createStatement("SELECT attr1 FROM my_table");

..statements to execute this query here…

Change the query for this statement if necessary:

sel_all_stmt->setSQL("SELECT attr2, attr3 FROM new_table");

When finished, release the statement object.
```
con->terminateStatement(sel_all_stmt);
```

Parametrized Statements

Very often statements are executed multiple times with different values.
For example, suppose a query that finds the name of a specific employee may be executed multiple times for different employees.
```
Statement* sel_name con->createStatement("SELECT name FROM employee WHERE id = :1");
```
This means this query will need to be supplied by one value before it is executed.
```
sel_name->setInt(1, 112223333);
```
The type used in the “set” method should set the type of the value being supplied.
This type of a query is UNPREPARED if the required value is not supplied by the program.

A prepared statement is optimized once and the query plan is used multiple times for each execution of the query saving execution time.

Statement* sel_name con->createStatement("SELECT name FROM employee WHERE id = :1 AND Office = :2");
sel_name->setInt(1, 112223333);
sel_name->setString(2, “AE125”);

Or

Statement* sel_name con->createStatement("SELECT name FROM employee WHERE id = :1 AND Office = :2");
sel_name->setInt(1, ssnVar);
sel_name->setString(2, officeVar);

Where ssnVar and officeVar are program variables of types integer and string respectively containing the necessary values.

Update statements

All statements that change the database are executed using executeUpdate method.

Examples are insert, update, delete, create …, drop … statements

stmt->executeUpdate(“CREATE TABLE basket_tab (fruit VARCHAR2(30), quantity NUMBER)”);

statement* s1 =
con->createStatement("INSERT INTO my_table (a, b) VALUES (1, 'A')");
s1->executeUpdate();

Update statements return the total number of tuples effected which can be returned by getUpdateCount() method.

Select statements

A SELECT query returns one or more tuples by the execution of the executeQuery method.
To process these tuples, you need a result set object which processes tuples in a similar way to a file.

You need to open, iterate through and close a result set to access the tuples.

statement* s1 =
con->createStatement("SELECT name FROM emp WHERE id < 1000");
ResultSet r = s1->executeQuery();

Query is executed, the tuples are returned to the program and the result set is initialized to the before the first item in the results.
To find the first item, you need to execute “next” operation.

next returns false if the last tuple is already been read.

statement* s1 = con->createStatement(
         "SELECT id, name FROM emp WHERE id < 1000");
ResultSet r = s1->executeQuery();
while (r->next()) {
    varId = r->getInt(1) ;
    varName = r->getString(2) ;
}
s->closeResultSet(r);

To read the columns of the current tuple pointed to by the result set, get methods are used.
The type of these methods much match the type of the column returned by the program. - getXXX(i) means attribute i of the query should have type XXX.

Errors and Status

An SQLException is raised for many things that may go wrong for your program: - Connection cannot be established - The query cannot be executed, etc.

The exceptions can be caught and checked in the program.

try{
   ... operations which throw SQLException ...
}
catch (SQLException e){
   cerr << e.what();
}

Other useful methods: e.getMessage(), e.getErrorCode()

Errors and Status

As statements are created dynamically and executed in the program, it may be necessary to check their status while the program is running.
This is accomplished with the status methods for each class.
Examples:
- Check whether the statement is UNPREPARED, PREPARED, RESULT_SET_AVAILABLE, or UPDATE_COUNT_AVAILABLE.
- Check whether the result set is END_OF_FETCH = 0, DATA_AVAILABLE.
Other methods exist to check for other status related information. For example, check whether the result set is empty (r->isNull).

Dynamic Conditions

Suppose tableName is a program variable set to a specific constant by the user.

sqlString = "SELECT * FROM " << tableName ;
statement* s1 = con->createStatement(sqlString);
ResultSet r = s1->executeQuery();
while (r->next()) {
           // what attributes are in this result set?
}
s->closeResultSet(r);

The function:
```
vector<MetaData> getColumnListMetaData() const;
```
that returns number, types and properties of aResultSet’s columns.

Transactions

A transaction starts with the first executeQuery, executeUpdate statement and ends until the first commit, abort and rollback statement executed for the given connection.
```
con->commit();
con->rollback();
```
After a rollback/commit, the next query/update will start a new transaction.

JDBC

OCCI is an Oracle specific language for enabling communications between a C++ program and a database.
JDBC is a standard for any database product and a Java program for the same purpose.
JDBC and OCCI are very similar to each other and have almost identical set of classes and methods. In fact, OCCI is based on JDBC.
To accomplish the communication between a Java program and a database, a set of libraries called a “driver” is needed.
JDBC drivers are specific to the database server.

Example program:

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
class Employee
{
public static void main (String args []) throws SQLException
{//Set your user name and the password
String userName = "dummy" ;
String passWord = "dummy" ;

// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@acadoracle.server.rpi.edu:1521:ora9",userName,passWord);
// Create a statement which will return a cursor that
// will allow you to scroll the result set using both
// "next" and "previous" methods

try {
      Statement stmt = conn.createStatement
           (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

      ResultSet rset = stmt.executeQuery("SELECT name, oid FROM items ");

      // Iterate through the result and print the item names
      while (rset.next ()) {
          //Get item name, which is the first column
          System.out.println (rset.getString (1));

          PreparedStatement pstmt = conn.prepareStatement ("SELECT name FROM owners WHERE oid = ?") ;

          //Feed the owner id retrieved from rset into pstmt
          pstmt.setInt(1, rset.getInt (2));
          ResultSet dset = pstmt.executeQuery() ;
          if (dset.next())
              System.out.println(dset.getString (1));
      } }
 }
 catch (SQLException) { error-handling-code } }  }

Python DB-API

DB-API is a generic db interface for python (like JDBC).

psycopg2 is a python adapter that implements DB-API.

import psycopg2 as dbapi2

db = dbapi2.connect (database="python", user="python", password="python")
cur = db.cursor()
cur.execute ("INSERT INTO versions VALUES ('2007-10-18', '2.4.4', 'stable')")
conn.commit ()
cur.close()
db.close()

Example 2:

import psycopg2 as dbapi2

db = dbapi2.connect (database="python", user="python", password="python")
cur = db.cursor()

cur.execute ("SELECT * FROM versions");
rows = cur.fetchall()
for i, row in enumerate(rows):
    print "Row", i, "value = ", row

try:
    cur.execute ("""UPDATE versions SET status='stable' where version='2.6.0' """)
    cur.execute ("""UPDATE versions SET status='old' where version='2.4.4' """)
    db.commit()
except Exception, e:
    db.rollback()

libpq: Postgresql C-language interface

The C-language interface for Postgresql uses a number of function calls to commmunicate with the database.

Example:

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h”
static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;  PGresult   *res;
    int         nFields;
    int         i,  j;

    conninfo = "port=5432 dbname='sibel' host='localhost' user='sibel' ";
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK)    {
        fprintf(stderr, "Connection to database failed: %s",
        PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");

    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
         fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
         PQclear(res);
         exit_nicely(conn);
    }
    /* Should PQclear PGresult whenever it is no longer needed to avoid\
    memory leaks */
    PQclear(res);

    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");
    /* next, print out the rows */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}