39 - APIs

39 - APIs#

Database Programming with Function Calls: SQL/CLI & JDBC#

This approach uses function calls, which is a dynamic approach to database programming.

SQL Call Level Interface (SQL/CLI) is part of the SQL standard and is a standardization of ODBC.

SQL/CLI: Using C as the Host Language#

  • Environment Record

    • Keeps environment information

    • Track one or more database connections

  • Connection Record

    • Keeps track of information needed for a particular database connection

  • Statement Record

    • Keeps track of the information needed for one SQL statement

  • Description Record

    • Keeps track of information about tuples or parameters

Here is query for single tuple as a result:

#include sqlchi.h;
void printSal() {

    SQLHSTMT stmt1;
    SQLHDBC con1;
    SQLHENV env1;
    SQLRETURN ret1, ret2, ret3, ret4;
    ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1);
    if (!ret1) ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1) else exit;
    if (!ret2) ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz", SQL_NTS) else exit;
    if (!ret3) ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1) else exit;

    SQLPrepare(stmt1, "select Lname, Salary from EMPLOYEE where Ssn = ?", SQL_NTS);

    prompt("Enter a Social Security Number: ", ssn);
    SQLBindParameter(stmt1, 1, SQL_CHAR, &ssn, 9, &fetchlen1);
    ret1 = SQLExecute(stmt1);
    if (!ret1) {
        SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1);
        SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2);
        ret2 = SQLFetch(stmt1);
        if (!ret2) {
            printf(ssn, lname, salary);
        } else {
            printf("Social Security Number does not exist: ", ssn);
        }
    }
}

Here is a query for a collection of tuples as a result:

#include sqlchi.h;
void printDepartmentEmps() {

    // Unchanged from previous example
    SQLHSTMT stmt1;
    SQLHDBC con1;
    SQLHENV env1;
    SQLRETURN ret1, ret2, ret3, ret4;
    ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1);
    if (!ret1) ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1) else exit;
    if (!ret2) ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz", SQL_NTS) else exit;
    if (!ret3) ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1) else exit;

    SQLPrepare(stmt1, "select Lname, Salary from EMPLOYEE where Dno = ?", SQL_NTS);

    prompt("Enter the Department Number: ", ssn);
    SQLBindParameter(stmt1, 1, SQL_INTEGER, &dno, 4, &fetchlen1);
    ret1 = SQLExecute(stmt1);
    if (!ret1) {
        SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1);
        SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2);
        ret2 = SQLFetch(stmt1);
        while (!ret2) {
            printf(lname, salary);
            ret2 = SQLFETCH(stmt1);
        }
    }
}

JDBC: SQL Function Calls for Java Programming#

JDBC uses function libraries for Java. A single Java program can connect to several databases, which are called data sources by the Java program.

A JDBC Driver must be loaded explicitly:

Class.forName("oracle.jdbc.driver.OracleDriver");

JDBC utilizes a Connection object, Statement object (with PreparedStatement and CallableStatement subclasses), the question mark (?) symbol (represents a statement parameter, determined at runtime), and a ResultSet object (holds results of the query).

Here is a query for a single tuple as a result:

import java.io.*;
import java.sql.*;

class getEmpInfo {
    public static void main(String args[]) throws SQLException, IOException {

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException x) {
            System.out.println("Driver could not be loaded");
        }

        String dbacct, passwrd, ssn, lname;
        Double salary;
        dbacct = readEntry("Enter database account:");
        passwrd = readEntry("Enter password:");
        Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:" + dbacct + "/" + passwrd);
        String stmt1 = "select Lname, Salary from EMPLOYEE where Ssn = ?";
        PreparedStatement p = conn.prepareStatement(stmt1);
        ssn = readentry("Enter a Social Security Number:");
        p.clearParameters();
        p.setString(1, ssn);
        ResultSet r = p.executeQuery();
        while (r.next()) {
            lname = r.getString(1);
            salary = r.getDouble(2);
            System.out.println(lname + salary);
        }
    }
}

Here is a query for multiple tuples:

import java.io.*;
import java.sql.*;

class getDepartmentEmps {
    public static void main(String args[]) throws SQLException, IOException {

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException x) {
            System.out.println("Driver could not be loaded");
        }

        String dbacct, passwrd, ssn, lname;
        Double salary;
        Integer dno;
        dbacct = readEntry("Enter database account:");
        passwrd = readEntry("Enter password:");
        Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:" + dbacct + "/" + passwrd);

        dno = readEntry("Enter a Department Number:");
        String q = "select Lname, Salary from EMPLOYEE where Dno = " + dno.tostring();
        Statement s = conn.createStatement();
        ResultSet r = s.executeQuery(q);
        while (r.next()) {
            lname = r.getString(1);
            salary = r.getDouble(2);
            System.out.println(lname + salary);
        }
    }
}

Database Stored Procedures and SQL/PSM#

Stored procedures are program modules stored by the DBMS at the database server. These can be functions or procedures.

CREATE PROCEDURE <procedure name> (<parameters>)
<local declarations>
<procedure body>;

SQL/Persistent stored modules (SQL/PSM) are extensions to SQL that include general-purpose programming constructs in SQL.

Persistent stored modules are stored persistently by the DBMS. They are useful when a database program is needed by several applications, for reducing data transfer and communication cost between client and server in certain situations, and for enhancing modeling power provided by views.

CREATE FUNCTION <function name> (<parameters>)
RETURNS <return type>
<local declarations>
<function body>;

Each parameter has a parameter type, which is one of the SQL data types, and a parameter mode, which is IN, OUT, or INOUT.

To call a stored procedure:

CALL <procedure or function name> (<argument list>);

Here is a conditional branching statement:

IF <condition> THEN <statement list>
ELSEIF <condition> THEN <statement list>
ELSEIF <condition> THEN <statement list>
ELSE <statement list>
END IF;

Here is a while loop:

WHILE <condition> DO
    <statement list>
END WHILE;

REPEAT
    <statement list>
UNTIL <condition>
END REPEAT;

And here is a for loop:

FOR <loop name> AS <cursor name> CURSOR FOR <query> DO
    <statement list>
END FOR;

And here is how to declare a function:

CREATE FUNCTION Dept_size(IN deptno INTEGER)
RETURNS VARCHAR [7]
DECLARE No_of_emps INTEGER;
SELECT COUNT(*) INTO No_of_emps
FROM EMPLOYEE WHERE Dno = deptno;
IF No_of_emps > 100 THEN RETURN "HUGE"
ELSEIF No_of_emps > 25 THEN RETURN "LARGE"
ELSEIF No_of_emps > 10 THEN RETURN "MEDIUM"
ELSE RETURN "SMALL"
END IF;
Contents