1 / 7

Dynamic SQL

Dynamic SQL. The dynamic SQL component of SQL allows programs to construct and submit SQL queries at run time. In contrast, embedded SQL statements must be completely present at compile time, they are compiled by the embedded SQL preprocessor.

yardley-maxwell
Download Presentation

Dynamic 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. Dynamic SQL The dynamic SQL component of SQL allows programs to construct and submit SQL queries at run time. In contrast, embedded SQL statements must be completely present at compile time, they are compiled by the embedded SQL preprocessor. Using dynamic SQL programs can create SQL queries as strings at runtime and can either have them executed immediately or have them prepared for sub sequent use.

  2. SQL defines standards for embedding dynamic SQL calls in host language, such as C as in the following example char *sqlprog=“update account set balance = balance *1.05 where acc_no=?” EXEC SQL prepare dynprog from : sqlprog; Char acc[10]=“101”; EXEC SQL execute dynprog using :acc; The dynamic SAL program contains acc_no=?, which is a place holder for a value that a provided when the SQL program is executed.

  3. Embedded SQL The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/1, FORTRAN, C and COBAL. A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. The basic of these languages follows that of the system R embedding of SQL into PL/1’s

  4. A simple COBAL program that retrieves the name and details of employee and displays it on the screen is given below: IDENTIFICATION DIVISION PROGRAM ID EMBDSQL ENVIRONMENT DIVISION CONFIGURATION SECTION SOURCE-COMPUTER IBM 3090 OBJECT COMPUTER IBM 3090 .

  5. DATA DIVISION WORKING STORAGE SECTION * Host variable for receiving the values retrieved using the SQL 01 WS-EMP-DETAILS 05 WS_EMPNO PIC 9 (3), 05 WS_NAME PIC X(30), 05 WS_DEPDID PIC X(2), EXECSQL INCLUDE SQLCA END_EXEC.

  6. PROCEDURE DIVISION * * initialize the working storage variables. *The Embedded SQL statements that retrieve the values. EXEC SQL SELECT EMPNO,NAME,DEPTNO INTO : WS_EMPNO, :WS_NAME, :WS_DEPTID FROM EMPLOYEE WHERE EMPNO=100 END-EXEC. DISPLAY “Employee NO. :”, WS_EMPNO. DISPLAY “Employee Name :”, WS_NAME DISPLAY “Department ID. :”,WS_DEPT. STOP RUN

  7. Cursor in embedded SQL Cursor declaration EXEC SQL Declare c cursor for select customer_name, customer_city from depositor, customer, account where depositor.customer_name = customenr_name and account.account_no = depositor.accountno and account_balance >:amount; Open cursor statement EXEC SQL open c END-EXEC Fetch statement EXEC SQL fetch c into :cn,:cc END_EXEC Close statement EXEC SQL Close c END_EXEC

More Related