1 / 14

Embedded SQL

Embedded SQL. Objectives. Understand how SQL can be embedded into a program Understand how to retrieve single rows & update tables using embedded SQL Understand the use of cursors to retrieve multiple rows & update the database Understand the concept of error handling. Embedded SQL.

sissy
Download Presentation

Embedded SQL

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. Embedded SQL

  2. Objectives • Understand how SQL can be embedded into a program • Understand how to retrieve single rows & update tables using embedded SQL • Understand the use of cursors to retrieve multiple rows & update the database • Understand the concept of error handling

  3. Embedded SQL • SQL commands may be embedded in a number of programming languages such as COBOL, C, & Fortran • Embedded SQL may be used to create a more flexible, accessible user interface • Performance may be improved with embedded SQL because the programmer can control the database access • Embedded SQL may be used to improve database security & enforce complex business rules

  4. Components of Embedded SQL • The host program is written in a 3GL • Sections of code are written in SQL throughout the program as needed. Each section of code begins with the keywords EXEC SQL & ends with END-EXEC • A separate precompiler is needed for each host language that will be used to access the database • The precompiler translates the SQL commands into code that can be understood by the compiler

  5. Using COBOL • Any tables to be processed must be declared in working-storage with the SQL command Declare tablename table • The table format must also be contained in an 01 record layout that describes each field • The SQLCA (SQL communications area) is used by the system to provide feedback to the program & must be included by coding the SQL command - Include SQLCA

  6. Using COBOL - continued • COBOL variable names may be used within SQL statements be preceding the variable name with a colon • The results of SQL queries must be placed in host variables through the use of the INTO clause Select custname into :cname from customer where custid = :custno-in • After every SQL statement is executed, the SQLCODE contains a code indicating if execution was normal (SQLCODE = 0) Programs should check the value of SQLCODE after every Select statement

  7. Retrieving Multiple Rows • COBOL processes a single record at a time. An SQL select statement returns a set of rows at once. In order for COBOL to process the set of rows returned by SQL a Cursor must be used. • A cursor allows the processing of a set of rows as though they were individual records in a sequential file • The cursor is a pointer that points to one row at the time

  8. Using A Cursor • The cursor is declared in the working storage section with the Declare cursor-name cursor for select statement • The select statement in the cursor definition is the statement that will be used for execution in the procedure division. • A different cursor must be declared for every SQL statement that will be executed that could return more than 1 row.

  9. The Cursor in the Procedure Division • Within the procedure division the cursor must be opened, fetched, and closed • Opening the cursor causes the query to be executed & makes the results available to the program • Executing a fetch, advances the pointer to the next row & places the row contents in the indicated host variables • Closing a cursor deactivates it

  10. A Cursor Example In working-storage: Exec SQL Declare cust-cur cursor for select cname, cphone from customer where carea = ‘912’ End-exec. In the procedure division: Exec SQL Open cust-cur End-exec.

  11. Example - continued The fetch SQL is put inside a perform loop that executes until the SQLCODE = 100 (100 is the code for no more rows) Exec SQL Fetch cust-cur into :custname, :custphone End-exec. Exec SQL Close cust-cur End-exec.

  12. Error Handling • SQLCODE contains 100 for no more records, positive numbers for various unusual but normal conditions (no records matched query), and negative numbers for fatal errors • Rather than checking for each code individually after every SQL statement, the Whenever statement may be used

  13. Whenever Exec SQL Whenever SQLError goto error-para End-exec. Exec SQL Whenever SQLwarning continue End-exec. Exec SQL Whenever not found continue End-exec.

  14. Embedded SQL Summary • SQL commands must be contained within Exec SQL, end-exec blocks • The SQL table definitions & the SQLCA must be included in the data division • Host language variables may be used within SQL statements as long as they are preceded with a colon • A cursor must be declared, opened, fetched (within a perform loop), & closed if the query retrieves more than 1 row • The SQLCODE should be checked after every SQL statement is executed

More Related