1 / 19

SQL with other Programming Languages

Learn how to integrate SQL with general-purpose programming languages like COBOL, Fortran, C, and Java. Explore different approaches such as embedded SQL, call level interface (CLI), and more.

jlange
Download Presentation

SQL with other Programming Languages

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL with other Programming Languages Murali Mani

  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 with general purpose Programming Language such as COBOL, Fortran, C, Java … Murali Mani

  3. Solutions • Embedded SQL (eg: Pro*C, SQLJ …) • SQL Embedded in host language • Preprocessor converts SQL statement to API calls • A host language compiler compiles the resulting code. • Call Level Interface (CLI) (eg: JDBC, ODBC, PHP …) • Libraries of functions provided explicitly for SQL statements • No preprocessor, in stead host language compiler compiles the code. Murali Mani

  4. Architecture Host Language + Embedded SQL Host Language Preprocessor Host Language compiler Host Language + Function Calls Executable Host Language compiler Executable CLI architecture Embedded SQL architecture Murali Mani

  5. Embedded SQL: Main Constructs • Connect to DB EXEC SQL CONNECT • Declare variables that can be used by both SQL and host language EXEC SQL BEGIN DECLARE SECTION … EXEC SQL END DECLARE SECTION • Executing SQL statements EXEC SQL … Murali Mani

  6. Embedding SQL in C: Oracle #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlca.h> EXEC SQL BEGIN DECLARE SECTION; VARCHAR userid[20]; VARCHAR passwd[20]; int value; EXEC SQL END DECLARE SECTION; void sql_error (char *msg) { printf (“%s”, msg); exit (1); } Murali Mani

  7. Embedding SQL in C: Oracle int main () { strcpy (userid.arr, “mmani”); userid.len = strlen (userid.arr); strcpy (passwd.arr, “mmani”); passwd.len = strlen (passwd.arr); EXEC SQL WHENEVER SQLERROR DO sql_error (“Oracle Error\n”); EXEC SQL CONNECT :userid IDENTIFIED BY :passwd; EXEC SQL CREATE TABLE Test (a int); EXEC SQL INSERT INTO Test VALUES (1); EXEC SQL SELECT MAX (a) INTO :value from R; printf (“Max value=%d\n”,value); } Murali Mani

  8. Cursors EXEC SQL DECLARE myCursor CURSOR FOR SELECT pNumber, pName from Professor; EXEC SQL OPEN myCursor; EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH myCursor INTO :num, :name; … } EXEC SQL CLOSE myCursor; Murali Mani

  9. Updating with Cursors EXEC SQL DECLARE myCursor CURSOR FOR SELECT pNumber, pName from Professor FOR UPDATE OF pName; EXEC SQL OPEN myCursor; EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH myCursor INTO :num, :name; … EXEC SQL UPDATE Professor SET pName=‘X’ WHERE CURRENT OF myCursor; } EXEC SQL CLOSE myCursor; EXEC SQL COMMIT; Murali Mani

  10. Checking if a SQL statement returned null short isNullNumber; … EXEC SQL FETCH myCursor into :num:isNullNumber, :name; if (isNullNumber == -1) { printf (“pNumber is null\n”); } … Murali Mani

  11. Compiling • Create files with extension .pc, such as test.pc • Preprocessor = proc • Available at $ORACLE_HOME/bin • SET your library path as • setenv LD_LIBRARY_PATH ${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib Murali Mani

  12. Compiling • We will make them as make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk build EXE=test OBJS=test.o (or) $ORACLE_HOME/bin/proc iname=test.pc code=cpp parse=none g++ -I$ORACLE_HOME/precomp/public test.c -lclntsh -lm Murali Mani

  13. To find about SQL states • Use the special variable called SQLSTATE, SQLCODE • Also there are macros such as SQLERROR, SQLWARNING, NOT FOUND Murali Mani

  14. 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 :snumber:isNullNumber, :sname; Murali Mani

  15. 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 <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 Murali Mani

  16. Other Embedded SQL • Embed SQL in Java for Oracle: SQLJ • SQLJ is part of SQL standard !! • Similar to Embedding SQL in C • Check Sample code Murali Mani

  17. Call Level Interface (CLI) • CLI • In Embedded SQL, the code depended on the DBMS vendor. • CLI Examples • ODBC (Open Database Connectivity) • JDBC (Java Database Connectivity) Murali Mani

  18. 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 + “’)”; Murali Mani

  19. Tips while using JDBC • DML modifications are usually committed (unlike ProC) • Look up interface java.sql.Connection • commit () • rollback () • setAutoCommit () Murali Mani

More Related