SQL%20with%20other%20Programming%20Languages

1
SQL with other Programming Languages

• B term 2004 lecture 16

2
Why?

• SQL is not for general purpose programming.
• Eg Suppose we have a table R (a, factorialA), we
  want to populate the table as follows
• 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

3
Architecture
Host Language Embedded SQL
Preprocessor
Host Language Function Calls
Host Language compiler
Executable
4
Oracle with C Embedded SQL

• Preprocessor proc
• Available at ORACLE_HOME/bin
• SET your library path as
• setenv LD_LIBRARY_PATH LD_LIBRARY_PATHORACLE
  _HOME/lib
• We will create files with extension .pc, example
  test.pc
• We will make them as
• make -f ORACLE_HOME/precomp/demo/proc/demo_proc.
  mk build EXEtest OBJStest.o

5
To find about SQL states

• Use the special variable called SQLSTATE
• Also there are macros such as SQLERROR,
  SQLWARNING, NOT FOUND

6
Handling NULL values

• Using indicator variables
• Indicator variables need 2 bytes so typically
  you set it as short.
• It is used as
• EXEC SQL FETCH myCursor INTO pnumberisNullNumber
  , pname
• If isNullNumber has value -1, then that means
  pnumber is null

7
Scrollable cursors

• 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 myCursor SCROLL CURSOR FOR
  select sNumber, sName from student
• We fetch from scrollable cursor as
• EXEC SQL FETCH RELATIVE 2 myCursor INTO
  snumberisNullNumber, sname

8
Scrollable cursors

• We can use the following to move the cursor
  pointer around
• 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 ltnumgt where ltnumgt is any positive or
  negative integer. RELATIVE 1 NEXT, RELATIVE -1
  PRIOR
• ABSOLUTE ltnumgt, if num is positive, we count from
  first, otherwise we count from last. ABSOLUTE 1
  FIRST, ABSOLUTE -1 LAST

9
Call Level Interface (CLI)

• 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)

10
Architecture for CLI
Host Language
Host Language compiler
Executable
11
CLI with C

• include sqlcli.h
• SQLHENV myEnv
• SQLHDBC myCon
• SQLHSTMT myStmt
• SQLRETURN errorCode
• errorCode SQLAllocHandle (SQL_HANDLE_ENV,
  SQL_NULL_HANDLE, myEnv)
• errorCode SQLAllocHandle (SQL_HANDLE_DBC,
  myEnv, myCon)
• errorCode SQLAllocHandle (SQL_HANDLE_STMT,
  myCon, myStmt)

12
Executing Statements

• SQLPrepare (myStmt, SELECT from r, SQL_NTS)
• SQLExecute (myStmt)
• (or)
• SQLExecDirect (myStmt, SELECT from r,
  SQL_NTS)

13
Fetching tuples (similar to fetching tuples from
cursor)

• SQLFetch (myStmt)
• To bind values from result to programming
  language variables.
• SQLINTEGER a1, a1Info
• SQLExecDirect (myStmt, Select from r,
  SQL_NTS)
• SQLBindCol (myStmt, 1, SQL_INTEGER, a1, sizeof
  (a1), a1Info)

14
Passing Parameters to SQL queries

• SQLPrepare (myStmt, INSERT INTO Student
  (sNumber, sName) VALUES (?, ?), SQL_NTS)
• SQLBindParameter (myStmt, 1, , num, )
• SQLBindParameter (myStmt, 2, , name, )
• SQLExecute (myStmt)

15
JDBC passing parameters to queries

• PreparedStatement myStmt myCon.prepareStatement
  (INSERT INTO Student (sNumber, sName) VALUES (?,
  ?))
• myStmt.setInt (1, num)
• myStmt.setString (2, name)
• (or)
• String query INSERT INTO Student (sNumber,
  sName) VALUES ( num , name )