Riyadh Philanthropic Society For Science Prince Sultan College For Woman

1. Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 9: Introduction to SQL Programming Techniques)

2. Outline • Database Programming • Approaches to Database Programming Chapter 9: Introduction to SQL Programming Techniques 1

3. Database Programming • Objective: to access a database from an application program (as • opposed to interactive interfaces). • Why? • An interactive interface is convenient but not sufficient. • (e.g. SQLPLUS) • A majority of database operations are made through application • programs (nowadays through web applications). Chapter 9: Introduction to SQL Programming Techniques 2

4. Approaches to Database Programming • Embedding Database Commands: • Most SQL statements can be embedded in a general-purpose • host programming language such as C and Java. • An embedded SQL statement is distinguished from the host • language statements by EXEC SQL and a matching END-EXEC • (or semicolon). • A precompiler (preprocessor) first scans the source program code to • identify database statements and extract them for processing by the • DBMS. • They are replaced in the program by function calls to the DBMS- • generated code. Chapter 9: Introduction to SQL Programming Techniques 3

5. Approaches to Database Programming • Using a Library of Database Functions: • A library of functions is made available to the host programming • language for database calls. • The actual database query and update commands, and any other • necessary information, are included as parameters in the function • calls. • This approach provides what is known as an API for accessing a • database from application programs. Chapter 9: Introduction to SQL Programming Techniques 4

6. Approaches to Database Programming • Designing a Brand-New Language: • A database programming language s designed from scratch to be • compatible with the database model and query language. • Minimizes impedance mismatch. • E.g. ORACLE’s PL/SQL. Chapter 9: Introduction to SQL Programming Techniques 5

7. A C Program Segment with Embedded SQL • loop = 1; • while (loop) • { • prompt (”Enter a Social Security Number: ", ssn); • EXEC SQL • select FNAME, MINIT, LNAME, ADDRESS, SALARY • into :fname, :minit, :lname, :address, :salary • from EMPLOYEE where SSN=:ssn; • if (SQLCODE == 0) printf (fname, minit, lname, address, salary) • else printf("Social Security Number does not exist: ”, ssn); • prompt("More Social Security Numbers (1 = Yes, 0 = No): ”, loop); • } Chapter 9: Introduction to SQL Programming Techniques 6

8. Embedded Versus Dynamic SQL • Embedded SQL: including hard-coded SQL statements in a program • written in another language such as C or Java. • Dynamic SQL: ability for an application program to generate SQL • code on the fly, as the application is running. Chapter 9: Introduction to SQL Programming Techniques 7

9. Database Programming with Function Calls • The use of function calls is a more dynamic approach for database • programming than embedded SQL. • One dynamic database technique is the dynamic SQL. • Another technique uses a library of functions (API) to access the • database. • Two function call interfaces are: SQL/CLI & JDBC. Chapter 9: Introduction to SQL Programming Techniques 8