SQL with other Programming Languages B term 2004

SQL with other Programming Languages B term 2004: lecture 16 Dec 11, 2003 Murali Mani

Why? l l SQL is not for general purpose programming. Eg: Suppose we have a table R (a, factorial. A), we want to populate the table as follows: l l The user inputs a, the program should compute the factorial of a and insert these 2 attributes as 1 row in R. We need to integrate SQL code with general purpose PL code such as COBOL, Fortran, C Dec 11, 2003 Murali Mani

Architecture Host Language + Embedded SQL Preprocessor Host Language + Function Calls Host Language compiler Executable Dec 11, 2003 Murali Mani

Oracle with C – Embedded SQL l Preprocessor = proc l l SET your library path as l l Available at $ORACLE_HOME/bin setenv LD_LIBRARY_PATH ${LD_LIBRARY_PATH}: ${ORACLE_HOME}/lib We will create files with extension. pc, example: test. pc l We will make them as make -f $ORACLE_HOME/precomp/demo/proc/demo_pro c. mk build EXE=test OBJS=test. o Dec 11, 2003 Murali Mani

To find about SQL states l l Use the special variable called SQLSTATE Also there are macros such as SQLERROR, SQLWARNING, NOT FOUND Dec 11, 2003 Murali Mani

Handling NULL values l l l Using indicator variables Indicator variables need 2 bytes – so typically you set it as short. It is used as l EXEC SQL FETCH my. Cursor INTO : pnumber: is. Null. Number, : pname; l If is. Null. Number has value -1, then that means : pnumber is null Dec 11, 2003 Murali Mani

Scrollable cursors l l We may need cursors that can go to any position in the result set, go back, go over the result set multiple times etc. Define a scrollable cursor as EXEC SQL DECLARE my. Cursor SCROLL CURSOR FOR select s. Number, s. Name from student; l We fetch from scrollable cursor as EXEC SQL FETCH RELATIVE 2 my. Cursor INTO : snumber: is. Null. Number, : sname; Dec 11, 2003 Murali Mani

Scrollable cursors l We can use the following to move the cursor “pointer” around l l l NEXT to give next tuple PRIOR to give previous tuple FIRST to give first tuple in result set LAST to give last tuple in result set RELATIVE <num> where <num> is any positive or negative integer. RELATIVE 1 = NEXT, RELATIVE -1 = PRIOR ABSOLUTE <num>, if num is positive, we count from first, otherwise we count from last. ABSOLUTE 1 = FIRST, ABSOLUTE -1 = LAST Dec 11, 2003 Murali Mani

Call Level Interface (CLI) l l Embedded SQL – You write a code specific to your DBMS vendor. CLI – more portable. The code you write is irrespective of the DBMS vendor. Embedded SQL – SQL embedded in C, FORTRAN, PASCAL, COBOL, Java (SQLJ) etc CLI – ODBC (Open Database Connectivity), JDBC (Java Database Connectivity) Dec 11, 2003 Murali Mani

Architecture for CLI Host Language compiler Executable Dec 11, 2003 Murali Mani

CLI with C #include sqlcli. h SQLHENV my. Env; SQLHDBC my. Con; SQLHSTMT my. Stmt; SQLRETURN error. Code; error. Code = SQLAlloc. Handle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &my. Env); error. Code = SQLAlloc. Handle (SQL_HANDLE_DBC, my. Env, &my. Con); error. Code = SQLAlloc. Handle (SQL_HANDLE_STMT, my. Con, &my. Stmt); Dec 11, 2003 Murali Mani

Executing Statements SQLPrepare (my. Stmt, “SELECT * from r”, SQL_NTS); SQLExecute (my. Stmt); (or) SQLExec. Direct (my. Stmt, “SELECT * from r”, SQL_NTS); Dec 11, 2003 Murali Mani

Fetching tuples (similar to fetching tuples from cursor) SQLFetch (my. Stmt); To bind values from result to programming language variables. SQLINTEGER a 1, a 1 Info; SQLExec. Direct (my. Stmt, “Select * from r”, SQL_NTS); SQLBind. Col (my. Stmt, 1, SQL_INTEGER, &a 1, sizeof (a 1), &a 1 Info); Dec 11, 2003 Murali Mani

Passing Parameters to SQL queries SQLPrepare (my. Stmt, “INSERT INTO Student (s. Number, s. Name) VALUES (? , ? )”, SQL_NTS); SQLBind. Parameter (my. Stmt, 1, …, num, …); SQLBind. Parameter (my. Stmt, 2, …, name, …); SQLExecute (my. Stmt); Dec 11, 2003 Murali Mani

JDBC passing parameters to queries Prepared. Statement my. Stmt = my. Con. prepare. Statement (“INSERT INTO Student (s. Number, s. Name) VALUES (? , ? )”); my. Stmt. set. Int (1, num); my. Stmt. set. String (2, name); (or) String query = “INSERT INTO Student (s. Number, s. Name) VALUES (“ + num + “, ‘” + name + “’)”; Dec 11, 2003 Murali Mani