Transcript Chapter 7

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
1
Embedded SQL Keywords
 There
are special SQL keywords that only
have meaning with Embedded SQL
–
–
–
–
–
INTO
FETCH
DECLARE CURSOR
OPEN CURSOR
CLOSE CURSOR
2
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
3
Publishers Table
 For
this presentation, we will use the
publishers table from the bookbiz.db
database
 PUBLISHERS:
pub_id
varchar(4)
not null
pub_name
varchar(40)
not null
address
varchar(40)
city
varchar(20)
state
char(2)
4
Publishers Table Data
 Pub_id
0736
0877
1389
Pub_name
New Age Books
Binnet & Hardley
Algodata Systems
Address
1 1st St.
2 2nd St.
3 3rd St.
City
Boston
Houston
Berkeley
State
MA
TX
CA
5
Interactive SQL Example
 SELECT pub_id,
pub_name, state
FROM publishers
WHERE pub_name = 'New Age Books';
 Returns:
0736 New Age Books MA
6
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
7
The INTO clause
 In
embedded SQL we use an INTO clause
in the SELECT statement to receive
information into program variables
 SELECT
select_list
INTO variable_list
FROM table_list
[ Rest of Statement ];
8
Embedded SQL Example
 SELECT
pub_id, pub_name, state
INTO :ls_pub_id, :ls_pub_name, :ls_state
FROM publishers
WHERE 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 ( : )
9
Exercise
 Write
the code to display the average price
of books in a Messagebox window.
10
Discussion
 Integer
li_price
SELECT AVG(price)
INTO :li_price
FROM titles
Messagebox('Average Price', li_price)
11
Exercise
 Display
in a Messagebox the publisher
name for the book "Life Without Fear"
(Note: Do not use any join operations or
subqueries! )
12
Discussion
 String
ls_pubname, ls_pub_id
SELECT pub_id INTO :ls_pub_id
FROM titles
WHERE title = 'Life Without Fear';
SELECT pub_name into :ls_pub_name
FROM publishers
WHERE pub_id = :ls_pub_id
Messagebox('Publisher', ls_pub_name)
13
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?
14
Interactive vs. Embedded
 Interactive:
– SELECT pub_id, pub_name, state
FROM publishers
WHERE state LIKE '_A';
– Returns two rows
 Embedded:
– SELECT pub_id, pub_name, state
INTO :ls_pub_id, :ls_pub_name, :ls_state
FROM publishers
WHERE state LIKE '_A';
– Error Condition!
15
Why?
 You've
only provided one set of variables
for two rows of information
– i.e. you can't stuff two pub_names (from the
two rows returned) into :ls_pub_name
16
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
17
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';
18
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;
19
Getting the Buffered Data
 The
OPEN cursor statement retrieves the
rows into the buffer
 How do we get this information into the
program variables?
20
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;
21
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)
22
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
23
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
24
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
25
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
26
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")
HALT
END CHOOSE
27
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
28
Close the cursor
 To
clear the buffer to a close the cursor
 Syntax: CLOSE <cursor_name>
 Example: CLOSE pub_cursor;
29
Example - Embedded INSERT
 String
ls_test_name
ls_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
30
Example - Embedded UPDATE
 string
ls_state
ls_state = 'CA'
 UPDATE
publishers
SET city = 'Oakland',
state = :ls_state
WHERE pub_name = :ls_test_name;
31
PowerBuilder Scripting
 The
following are some PowerBuilder
scripting statements that may help you
completed the assignment.
32
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
33
Displaying Information
 MessageBox(<title>,
<message>)
– example:
String ls_msg
ls_msg = 'Something is wrong'
MessageBox("Error Found", ls_msg)
34
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
35
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")
36
Stopping Execution
 RETURN
leaves the currently running
script
 HALT terminates
the entire application
37
Decision Logic
 If-Then-Else
– example:
 Choose
If li_count > 1 then
li_count = 0
else
li_count = 1
end if
Case
– example:
Choose Case li_count
Case > 1
li_count = 0
Case Else
li_count = 1
End Choose
38
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!
39