# SQL - Other Procedural Paradigms - There are many other paradigms that have been developed over the years, some are quite archaic but may be part of legacy systems you may encounter. I provide them here as a reference. ## Call-Level Interface Examples: OCCI - OCCI is a C-library specific to Oracle, but it is designed to very closely resemble JDBC for Java which is a standard. ``` #include 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 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. ## Call-Level Interface Examples: 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 } } } ``` ## libpq: Postgresql C-language interface - The C-language interface for Postgresql uses a number of function calls to commmunicate with the database. - Example: ``` #include #include #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; } ``` ## SQL - Embedded SQL Programming - In this section, we will look at a different paradigm for writing programs that incorporate SQL queries. - The main distinction between this model and others is that the program is written in a host language like C, but contains constructs that are foreign to the host language. - One thing you will notice that it requires programmers to work on very low level details of communication with the database. - To be able to compile these programs, we must first precompile using a special program, which will rewrite the program code by replacing pieces of it. - Once precompilation is finished, we compile the remaining code. - Embedded SQL, ESQL is an industry standard language. ### Overview - Embedded SQL is an SQL standard for writing a program in a host language (like C) with SQL statements starting with the string: ``` EXEC SQL ``` and ending with semicolon (;). - In addition, all variables to be used by the program as input/output to a query must be declared within a declare section. - Often type conversion for preliminary data types between the programming language and SQL is done by hand. - Proc in Oracle, ECPG in Postgresql implements the C embeddings for SQL. - Note that the following notes are based on Oracle embedded SQL language (slight differences are possible for postgresql) - Example program: ``` #include exec sql include sqlca; char user_prompt[] = "Please enter username and password: "; char cid_prompt[] = "Please enter customer ID: "; int main() { exec sql begin declare section; /* declare SQL host variables */ char cust_id[5]; char cust_name[14]; float cust_discnt; /* host var for discnt value */ char user_name[20]; exec sql end declare section; exec sql whenever sqlerror goto report_error; /* error trap condition */ exec sql whenever not found goto notfound; /* not found condition */ exec sql unix:postgresql://csc4380.cs.rpi.edu/sibel AS myconnection USER :user_name; /* ORACLE format: connect */ while (prompt(cid_prompt, 1, cust_id, 4) >= 0) { exec sql select cname, discnt into :cust_name, :cust_discnt /* retrieve cname, discnt */ from customers where cid = :cust_id; exec sql commit work; /* release read lock on row */ printf("CUSTOMER'S NAME IS %s AND DISCNT IS %5.1f\n", cust_name, cust_discnt); /* NOTE, (:) not used here */ continue; } } ``` ### ESQL - Each ESQL statement starts with EXEC SQL keyword and ends with a semicolon ; - A pre-compiler will scan a program file and only read the statements enclosed within EXEC SQL statements and disregard everything else. - SQLCA is a specific data structure for storing status codes of all SQL operations ``` /* always have this for error handling*/ exec sql include sqlca ; ``` ## Connections - To be able to perform any operations, we must open a connection to the database. ``` EXEC SQL CONNECT TO target [AS connection-name] [USER user-name]; ``` - Many connection can be opened in a program, but generally one connection per database is sufficient. - Different databases can be used in a single program. - Close all connections before the program exists: ``` EXEC SQL DISCONNECT [connection]; ``` - Change between multiple open connections with: ``` EXEC SQL SET CONNECTION connection-name; ``` ## Variables in ESQL - All variables that will be used in an SQL statement must be declared using an ESQL declaration and data type ``` EXEC SQL BEGIN DECLARE SECTION ; VARCHAR e_name[30], username[30] ; INTEGER e_ssn, e_dept_id ; EXEC SQL END DECLARE SECTION ; ``` - You can use almost any SQL command in ESQL as long as proper input to these commands are provided in the form of program variables. ### Executing SQL commands - Suppose we want to find the name of an employee given his/her SSN (input by the user of the program): ``` EXEC SQL select name, dept_id into :e_name, :e_dept_id from employee where ssn = :e_ssn ; ``` - Program variables are preceded by ":", i.e. :e_ssn. - Read the value of the variable “e_ssn” and execute the SQL statement : using this value, store the returned values of columns "name" and "dept_id" in the program variables "e_name" and "e_dept_id". - Compare the above query with the expression below. What is the difference? ``` EXEC SQL select name, dept_id from employee where ssn = e_ssn ; ``` ### Executing SQL commands - We are able to write: ``` EXEC SQL select name, dept_id into :e_name, :e_dept_id from employee where ssn = :e_ssn ; ``` - Since this query returns a single tuple. For this tuple, we read the returned values. - We will see how to handle queries that return multiple tuples in a minute. ### Dealing with Strings - There is a mismatch between the definition of a string in Oracle and in C/C++. - In C, the end of a string is identified by the null character '0'. Hence, "Sibel" would be stored as characters 'S','i','b','e','l','0'. - In Oracle, the length of a string is stored together with the string and there is no special end of string character. - If you convert a data string from Oracle to C, you must pad it with '0' manually! - The data type VARCHAR e_name[30] is translated by the pre-compiler to the following structure: ``` struct { unsigned short len unsigned char arr[30] } e_name ; ``` - Putting the pieces together: ``` strcpy(username.arr, “Sibel Adali") ; username.len = strlen(“Sibel Adali") ; strcpy(passwd.arr, “tweety-bird") ; passwd.len = strlen(“tweety-bird") ; exec sql connect :username identified by :passwd ; scanf(“%d", &e_ssn) ; exec sql select name, dept_id into :e_name, :e_dept_id from employee where ssn = :e_ssn ; e_name.arr[e_name.len] = '\0' ; /* so can use string in C*/ printf(“%s", e_name.arr) ; exec sql commit work ; /* make any changes permanent */ exec sql disconnect ; /* disconnect from the database */ ``` ### Status Processing - SQL Communications area is a data structure that contains information about - Error codes (might be more detailed than SQLSTATE) - Warning flags - Event information - Rows-processed count - Diagnostics for all processed SQL statements. - Included in the program using ``` EXEC SQL INCLUDE SQLCA; or #include ``` - It is possible to get the full text of error messages and other detailed status information. - Whenever an SQL statement is executed, its status is returned in a variable named `"SQLSTATE"` - This variable must be defined in the variable section, but it is populated with values automatically ``` EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6] ; EXEC SQL END DECLARE SECTION; ``` - Different errors and conditions have values that might be vendor specific. ### Status processing - `sqlca` covers both warnings and errors. If multiple warnings or errors occur during the execution of a statement, then sqlca will only contain information about the last one. - If no error occurred in the last SQL statement, sqlca.sqlcode will be 0 and sqlca.sqlstate will be "00000". - If a warning or error occurred, then sqlca.sqlcode will be negative and sqlca.sqlstate will be different from "00000". - If the last SQL statement was successful, then sqlca.sqlerrd[1] contains the OID of the processed row, if applicable, and sqlca.sqlerrd[2] contains the number of processed or returned rows, if applicable to the command. - The code can be checked after each statement and error handling code can be written - Commit, rollback - Exit program, etc. ``` if (strcmp(SQLSTATE, "000000") != 0) rollback ; ``` - It is also possible to use trap conditions that remain active throughout the program. ``` EXEC SQL WHENEVER ; ``` - Conditions: `SQLERROR`, `SQLWARNING`, `NOT FOUND` - Actions: `DO function`, `DO break`, `GOTO label`, `CONTINUE`, `STOP` - Because WHENEVER is a declarative statement, its scope is positional, not logical. That is, it tests all executable SQL statements that physically follow it in the source file, not in the flow of program logic. - A WHENEVER directive stays in effect until superseded by another WHENEVER directive checking for the same condition. ### Transactions - Transactions start with the logically start with the first SQL statement and end with either a COMMIT or ROLLBACK statement - It is possible to set boundaries of transactions with the SQL statement: ``` BEGIN ; SET TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED DIAGNOSTICS SIZE 6 ; ``` - Diagnostics size is the number of exception conditions that can be described at one time in the status. - READ ONLY, READ/WRITE transactions allow the programmer to define the type of the transaction - Isolation level allows the programmer to define the desired level of consistency ### ESQL - Cursor Operations - Declare a cursor using a regular SQL query (no "into"). ``` EXEC SQL DECLARE emps_dept CURSOR FOR select ssn, name from employee where dept_id = :e_dept_id ; ``` - Open a cursor: means the corresponding SQL query is executed, the results are written to a file (or a data structure) and the cursor is pointing to the first row. ``` EXEC SQL OPEN emps_dept ; ``` - Read the current row pointed to by the cursor using "fetch". At the end of fetch, the cursor is moved to point to the next tuple. ``` EXEC SQL FETCH emps_dept INTO :e_ssn, :e_name ; ``` - How do we know when we reach the end of a cursor? - Check the "sqlcode" to see if the end of a cursor is reached (its expected value depends on the system). ``` if (sqlca.sqlcode == -1) { … } ``` - Error handling statements ``` EXEC SQL WHENEVER NOT FOUND {} ``` ### Cursors and snapshots - If a cursor is declared as "INSENSITIVE", the contents of the cursor is computed once when the cursor is opened, and remains the same until the cursor is closed, even if the underlying data tables change during the this time. ``` DECLARE cursor-name CURSOR INSENSITIVE CURSOR FOR table-expression ``` - This type of cursor is a snapshot of the database, a view of it at a specific time. ``` DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR FOR table_expression [ORDER BY order-item-comma-list] [ FOR [READ ONLY | UPDATE | OF column-commalist] ] ``` ### Cursors for update - If a cursor is declared for update, then updates can be performed on the current tuple. ``` DECLARE CURSOR cursor-name CURSOR FOR table-expression FOR UPDATE OF column-list UPDATE table-name SET assignment-list WHERE CURRENT OF cursor-name DELETE FROM table-name WHERE CURRENT OF cursor-name ``` - For these updates to have an effect, the cursor must not be INSENSITIVE. ### Constraints - When constraints are violated, they cause an exception (or sqlerror) to be thrown. - When are constraints violated? - If constraint checking for a constraint is immediate, as soon as an SQL statement causes the constraint to become false, it is rolled back. - If a constraint is defined to be deferrable, then the constraint is not checked until a transaction tries to commit. Then, if it is violated, the whole transaction is rolled back. ``` CONSTRAINT name CHECK … DEFERRABLE ``` ### Dynamic SQL - In Dynamic SQL, embedded SQL statements are created on the fly using strings! - these strings are fed to an exec sql statement ``` exec sql execute immediate :sql_string ``` - Since dynamic SQL statements are not known to the pre-compiler at compile/initiation time, they must be optimized at run time! - Create a query once using a prepare statement and run it multiple times using the execute statement. ``` strcopy(sqltext.arr, "delete from employee where ssn = ?") ; sqltext.len=str.len(sqltext.arr) ; exec sql prepare del_emp from :sqltext ; exec sql execute del_emp using :cust_id ; ``` ### SQLDA - When we execute a dynamic SQL statement, we do not know which columns will be returned and how many columns will be returned. - The SQLDA descriptor definition allows us to find the number of columns and the value for each column. ``` exec sql include sqlda ; exec sql declare sel_curs cursor for sel_emps ; exec sql prepare sel_emps from :sqltext ; exec sql describe sel_emps into sqlda ; exec sql open sel_curs ; exec sql fetch sel_curs using descriptor sqlda ; ```