37 - APIs

37 - APIs#

Approaches to Database Programming#

  • Embedding database commands in a a general-purpose programing language

    • Embedded SQL

  • Using a library of database functions

    • Library of functions available to host programming language

    • Application programming interface (API)

  • Designing a brand-new language

    • Database programming language designed from scratch

Impedance Mismatch#

  • Differences between database model and programming language model

  • Binding for each host programming language

    • Specifies for each attribute type the compatible programming language types

  • Cursor or iterator variable

    • Loop over the tuples in a query result

Embedded SQL, Dynamic SQL, and SQLJ#

  • Embedded SQL

    • C language

  • SQLJ

    • Java language

  • Programming language called host language

  • SQL called data sublanguage


../../_images/37-1.jpg

Embedded SQL#

Retrieving Single Tuples#

EXEC SQL

  • Prefix

  • Preprocessor separates embedded SQL statements from host language code

  • Terminated by matching END-EXEC

    • Or by a semicolon

Shared Variables

  • Used in both the C program and the embedded SQL statements

  • Prefixed by a colon in SQL statement

To connect to the database:

CONNECT TO <server name> AS <connection name>
AUTHORIZATION <user account name and password>;

To change a connection:

SET CONNECTION <connection name>;

To terminate a connection:

DISCONNECT <connection name>;

The SQLCODE and SQLSTATE communication variables are used by the DBMS to communicate exception or error conditions.

The SQLCODE variable is

  • 0 when the statement is executed successfully

  • 100 when no more data is available in the query result

  • <0 when some error has occured

The SQLSTATE variable is a string of 5 characters where ‘00000’ indicates there is no error or exception. Other values indicate certain errors or exceptions (e.g. ‘02000’ indicates ‘no more data’)

Example#

loop = 1;
while (loop) {
    prompt("Enter a Social Security Number: ", ssn);
    EXEC SQL
        SELECT Fname, Minit, Lname, Address, Salary
        INTO :fname, :minit, :lname, :address, :salary
        FROM EMPLOYEE WHERE Ssn = :ssn;
    if (SQLCODE == 0) printf(fname, minit, lname, address, salary)
        else printf("Social Security Number does not exist: " ssn);
    prompt("More Social Security Numbers (enter 1 for Yes, 0 for No): ", loop);
}

Retrieving Multiple Tuples Using Cursors#

A cursor is a pointer to a single row (tuple) of the result of a query.

The OPEN CURSOR command fetches the query result and sets the cursor to a position before the first row in the result. This then becomes the current row of the cursor.

FETCH commands move the cursor to the next row in the result of a query.

FOR UPDATE OF lists the names of any attributes that will be updated by the program.

Fetch orientation is added using the values: NEXT, PRIOR, FIRST, LAST, ABSOLUTE i, and RELATIVE i.

DECLARE <cursor name> [INSENSITIVE] [SCROLL] CURSOR
[WITH HOLD] FOR <query specification>
[ORDER BY <ordering specification>]
[FOR READ ONLY | FOR UPDATE [OF <attribute list>]];

  • WITH HOLD indicates that the cursor will not be closed

Example#

prompt("Enter the Department Name: ", dname);
EXEC SQL
    SELECT Dnumber INTO :dnumber
    FROM DEPARTMENT WHERE Dname = :dname;
EXEC SQL DECLARE EMP CURSOR FOR
    SELECT Ssn, Fname, Minit, Lname, Salary
    FROM EMPLOYEE WHERE Dno = :dnumber
    FOR UPDATE OF Salary;
EXEC SQL OPEN EMP;
EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary;
while (SQLCODE == 0) {
    printf("Employee name is:" Fname, Minit, Lname);
    prompt("Enter the raise amount: ", raise);
    EXEC SQL
        UPDATE EMPLOYEE
        SET Salary = Salary + :raise
        WHERE CURRENT OF EMP
    EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary;
}
EXEC SQL CLOSE EMP;

Dynamic SQL#

Dynamic SQL executes different SQL queries or updates dynamically at runtime.

This includes dynamic updates and dynamic queries.

EXEC SQL BEGIN DECLARE SECTION;
varchar sqlupdatestring[256];
EXEC SQL END DECLARE SECTION;

prompt("Enter the Update Command: ", sqlupdatestring);
EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring;
EXEC SQL EXECUTE sqlcommand;

SQLJ#

SQLJ is a standard adopted by several vendors for embedding SQL in Java.

It involves importing several class libraries, default context, and uses exceptions for error handling (SQLException is used to return errors or exception conditions).

This is how to import the libraries needed for SQLJ for Java programs in Oracle and establish a connection and default context:

import java.sql.*;
import java.io.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

DefaultContext cntxt = oracle.getConnection("<url name>", "<user name>", "<password>", true);
DefaultContext.setDefaultContext(cntxt);

Our coding examples will use the following variables:

String dname, ssn, fname, fn, lname, ln, bdate, address;
char sex, minit, mi;
double salary, sal;
int dno, dnumber;

Retrieving a Single Tuple with SQLJ#

ssn = readEntry("Enter a Social Security Number: ");
try {
    #sql {SELECT Fname, Minit, Lname, Address, Salary
        INTO :fname, :minit, :lname, :address, :salary
        FROM EMPLOYEE WHERE Ssn = :ssn}
} catch (SQLException se) {
    System.out.println("Social Security Number does not exist: " + ssn);
    return;
}
System.out.println(fname + " " + minit + " " + lname + " " + address + " " + salary);

Retrieving Multiple Tuples with SQLJ#

An iterator is an object associated with a collection (set or multiset) of records in a query result.

A named iterator is associated with a query result by listing attribute names and types in the query result.

A positional iterator lists only attribute types in the query result.

This example uses a named iterator to print employee information from a given department:

dname = readEntry("Enter the Department Name: ");
try {
    #sql {SELECT Dnumber INTO :dnumber
        FROM DEPARTMENT WHERE Dname = :dname};
} catch (SQLException se) {
    System.out.println("Department does not exist: " + dname);
    return;
}
System.out.println("Employee information for Department: " + dname);
#sql iterator Emp(String ssn, String fname, String minit, String lname, double salary);
Emp e = null;
#sql e = {SELECT ssn, fname, minit, lname, salary
    FROM EMPLOYEE WHERE Dno = :dnumber};
while (e.next()) {
    System.out.println(e.ssn + " " + e.fname + " " e.minit + " " + e.lname + " " + e.salary)
}
e.close();

This example uses a positional iterator to print employee information from a given department:

dname = readEntry("Enter the Department Name: ");
try {
    #sql {SELECT Dnumber INTO :dnumber
        FROM DEPARTMENT WHERE Dname = :dname};
} catch (SQLException se) {
    System.out.println("Department does not exist: " + dname);
    return;
}
System.out.println("Employee information for Department: " + dname);
#sql iterator Emppos(String, String, String, String, double);
Emppos e = null;
#sql e = {SELECT ssn, fname, minit, lname, salary
    FROM EMPLOYEE WHERE Dno = :dnumber};
#sql {FETCH :e INTO :ssn, :fn, :mi, :ln, :sal}
while (!e.endFetch()) {
    System.out.println(ssn + " " + fn + " " mi + " " + ln + " " + sal)
}
e.close();
Contents