Section 10 - Embedded SQL

1. Section 10 - Embedded SQL • Many computer languages allow you to embed SQL statements within the code (e.g. COBOL, PowerBuilder, C++, PL/SQL, etc.) • This allows you to both… • pass program variables to SQL statements • receive SQL statement results into program variables

2. Embedded SQL Keywords • There are special SQL keywords that only have meaning with Embedded SQL • INTO • FETCH • DECLARE CURSOR • OPEN CURSOR • CLOSE CURSOR

3. SQL Status Checking • A major difference between Interactive SQL and Embedded SQL is that you must explicitly check for the success or failure of each SQL statement. • Program logic may change based on the return values from SQL statements • Transaction management may change base on the return values from SQL statements

4. Publishers Table • For this presentation, we will use the publishers table from the bookbiz.db database • PUBLISHERS:pub_id varchar(4) not nullpub_name varchar(40) not nulladdress varchar(40)city varchar(20)state char(2)

5. Publishers Table Data • Pub_id Pub_name Address City State0736 New Age Books 1 1st St. Boston MA0877 Binnet & Hardley 2 2nd St. Houston TX1389 Algodata Systems 3 3rd St. Berkeley CA

6. Interactive SQL Example • SELECT pub_id, pub_name, stateFROM publishersWHERE pub_name = 'New Age Books'; • Returns:0736 New Age Books MA

7. Using PowerBuilder • For the rest of this presentation we will be using PowerBuilder scripting to illustrate the embedded SQL examples • Other languages may vary in how you specify variable name within a SQL statement • I will be teaching you a few Powerbuilder scripting statements in this presentation

8. The INTO clause • In embedded SQL we use an INTO clause in the SELECT statement to receive information into program variables • SELECT select_listINTO variable_listFROM table_list[ Rest of Statement ];

9. Embedded SQL Example • SELECT pub_id, pub_name, stateINTO :ls_pub_id, :ls_pub_name, :ls_stateFROM publishersWHERE pub_name = 'New Age Books';Messagebox("Pub_id for New Age Books", ls_pub_id) • In PowerBuilder, variable names that appear within SQL statements are preceded with a colon ( : )

10. Exercise • Write the code to display the average price of books in a Messagebox window.

11. Discussion • Integer li_priceSELECT AVG(price)INTO :li_priceFROM titles Messagebox('Average Price', li_price)

12. Exercise • Display in a Messagebox the publisher name for the book "Life Without Fear"(Note: Do not use any join operations or subqueries! )

13. Discussion • String ls_pubname, ls_pub_idSELECT pub_id INTO :ls_pub_idFROM titlesWHERE title = 'Life Without Fear';SELECT pub_name into :ls_pub_nameFROM publishersWHERE pub_id = :ls_pub_idMessagebox('Publisher', ls_pub_name)

14. Multiple Return Rows • Preceding SELECT statement examples returned only one row • What do you think will happen if an embedded SELECT returns more than one row?

15. Interactive vs. Embedded • Interactive: • SELECT pub_id, pub_name, stateFROM publishersWHERE state LIKE '_A'; • Returns two rows • Embedded: • SELECT pub_id, pub_name, stateINTO :ls_pub_id, :ls_pub_name, :ls_stateFROM publishersWHERE state LIKE '_A'; • Error Condition!

16. Why? • You've only provided one set of variables for two rows of information • i.e. you can't stuff two pub_names (from thetwo rows returned) into :ls_pub_name

17. Buffering Returned Data • If there is any possibility that a SELECT statement will return more than one row you must provide a buffer to hold the values • DECLARE CURSOR creates this buffer • This buffer will be read later (one row at a time) using the FETCH statement

18. DECLARE CURSOR Syntax • DECLARE <cursor_name> CURSOR FOR<Select statement (do not use INTO clause) >; • Example: • DECLARE pub_cursor CURSOR FOR SELECT pub_id, pub_name, state FROM publishers WHERE state LIKE '_A';

19. Open the Cursor • The DECLARE CURSOR stores the associated SELECT statement, but does not execute it. • The OPEN <cursor_name> statement actually executes the stored SELECT statement • Example: • OPEN pub_cursor;

20. Getting the Buffered Data • The OPEN cursor statement retrieves the rows into the buffer • How do we get this information into the program variables?

21. FETCH INTO Statement • To read the buffer one row at a time and get the data into program variables, use the FETCH INTO statement • Syntax: FETCH <cursor_name> INTO <variable_list> • Example: FETCH pub_cursor INTO :ls_pub_id, :ls_pub_name, :ls_state;

22. Matching Variables & Columns • The variables declared in the FETCH INTO statement... • must be equal to the number of columns in the SELECT statement saved with the cursor • must be of the same datatype (or convert to the variable datatype)

23. Program Loops • In order to get all the rows in the buffer you must execute the FETCH statement each time you want to get the next row • Place the FETCH in a program loop and exit the loop when there are now more rows to read in the buffer

24. Example • DO FETCH pub_cursor INTO :ls_pub_id, :ls_pub_name, ls_state; If sqlca.sqlcode = 100 then EXIT end if <some processing with the variables>LOOP WHILE TRUE

25. SQLcode • The sqlca.sqlcode variable you saw in the preceding statement is a PowerBuilder specific variable name that stores the return value from an executed SQL statement • Notice that variables within the programming language, (i.e. not in the SQL statement proper), are not preceded with the colon

26. Status Checking • All embedded SQL statements should have there status checked by interrogated the return code (e.g. sqlca.sqlcode) • Return codes and their meaning... • 0 means success for all statements including the INSERT, UPDATE & DELETE when no rows are affected • 100 means no rows found for the SELECT and means at end of buffer for the FETCH • -1 (minus one) means database error

27. Example • FETCH pub_cursor INTO :ls_pub_id, :ls_pub_name, ls_state;CHOOSE CASE sqlca.sqlcode CASE 0 COMMIT: CASE 100 COMMIT; EXIT CASE -1 ROLLBACK; Messagebox("Database Error", "Process Halted") HALTEND CHOOSE

28. COMMIT and ROLLBACK • Remember… • COMMIT saves the updates to the database since the last COMMIT • ROLLBACK un-does the updates to the database since the last COMMIT

29. Close the cursor • To clear the buffer to a close the cursor • Syntax: CLOSE <cursor_name> • Example: CLOSE pub_cursor;

30. Example - Embedded INSERT • String ls_test_namels_test_name = 'Perry Publishing' • INSERT INTO publishers (pub_id, pub_name)VALUES ('9988', :ls_test_name); • When using a constant do not use a colon

31. Example - Embedded UPDATE • string ls_statels_state = 'CA' • UPDATE publishersSET city = 'Oakland', state = :ls_stateWHERE pub_name = :ls_test_name;

32. PowerBuilder Scripting • The following are some PowerBuilder scripting statements that may help you completed the assignment.

33. Declaring a Variable • String <variable name> • For character based data • example: String ls_last_name • Int < variable name> • for whole numbers less than 32674 • example: Int li_count

34. Displaying Information • MessageBox(<title>, <message>) • example: String ls_msg ls_msg = 'Something is wrong' MessageBox("Error Found", ls_msg)

35. DO Loop • Executes the script inside the Loop repetitively • Example: Do li_count = li_count + 1 Loop While True • This will add 1 to li_count continually

36. Leaving a Loop • EXIT • Leaves the current loop (e.g DO-LOOP) and continues with the next line of code after the loop • example: DO li_count = li_count + 1 if li_count > 4 then EXIT end if LOOP WHILE TRUE Messagebox("Info","Completed")

37. Stopping Execution • RETURN leaves the currently running script • HALT terminates the entire application

38. Decision Logic • If-Then-Else • example: If li_count > 1 then li_count = 0 else li_count = 1 end if • Choose Case • example: Choose Case li_count Case > 1 li_count = 0 Case Else li_count = 1 End Choose

39. End Section 10 - Embedded SQL • Embedded SQL Assignment is Optional. • Due: Whenever (e-mail me the code) • There are no points added to your grade for this assignment • Use any programming language that allow embedded SQL… That means Not Visual Basic!