Query Processing

1
Query Processing
2
Overview of Query Processing

• Query Processing The activities involved in
  retrieving data from the database
• Query Optimization activity of choosing an
  efficient strategy for processing a query
• Many ways to perform a complex query
• One aim of query processing is to choose the most
  cost effective.
• Two approaches
• Heuristic rules order operations in a query
• Compare different strategies based on their
  costs. Disk access dominant cost.

3
Overview of Query Processing

• Many equivalent transformations of same
  high-level query, aim of QO is to choose one that
  minimizes resource usage.
• Generally, reduce total execution time of query.
• May also reduce response time of query.
• Problem computationally intractable large number
  of relations, so strategy adopted is reduced to
  finding near optimum solution.
• A problem for which no algorithm can exist which
  computes all possible solutions
• Both methods depend on database statistics.
  Accuracy and currency of stats effects efficiency
  of strategy chosen
• Keeping stats current is problematic (discussed
  further later)
• Update periodically
• Or make updating users responsibility

4
Overview of Query Processing

• Example Find all Managers who work at a London
  branch.
• SQL
• SELECT
• FROM Staff s, Branch b
• WHERE s.branchNo b.branchNo AND
• (s.position Manager AND b.city London)

• Assume
• 1000 tuples in Staff
• 50 tuples in Branch
• 50 Managers
• 5 London branches
• no indexes or sort keys
• results of any intermediate operations stored on
  disk
• cost of the final write is ignored
• tuples are accessed one at a time.

• Relational Algebra Queries
• (1) ?(position'Manager') ? (city'London') ?
• (Staff.branchNoBranch.branchNo) (Staff X
  Branch)
• (2) ?(position'Manager') ? (city'London')(
• Staff Staff.branchNoBranch.branchNo
  Branch)
• (3) (?position'Manager'(Staff))
  Staff.branchNoBranch.branchNo
• (?city'London' (Branch))

• Cost (in disk accesses) are
• (1) (1000 50) 2(1000 50) 101050
• (2) 21000 (1000 50) 3050
• (3) 1000 250 5 (50 5) 1160
• Cartesian product and join operations much more
  expensive than selection.
• Third option significantly reduces size of
  relations being joined together.

5
Overview of Query Processing

• QP has four main phases
• decomposition
• optimization
• code generation
• execution

6
Overview Dynamic vs Static optimization

• Two options for when first three phases of QP can
  be carried out
• dynamically every time query is run
• Advantages
• info is up to date.
• Disadvantages
• performance of query is affected, time may limit
  finding optimum strategy
• statically when query is first submitted.
• Advantages
• removal of runtime overhead
• more time to find optimum strategy.
• Disadvantages
• chosen execution strategy may no longer be
  optimal when query is run. - Could use a hybrid
  approach re-optimize if statistics have changed

7
Query Decomposition

• Aims - to transform high-level query RA query
• - check that query is syntactically and
  semantically correct.
• Typical stages are
• analysis
• normalization
• semantic analysis
• simplification
• query restructuring

8
1. Analysis

• In this stage
• Analyze query lexically and syntactically using
  compiler techniques.
• Verify relations and attributes are defined in
  system catalog.
• Verify operations are appropriate for object type.

Example SELECT staff_no FROM Staff WHERE
position gt 10

• This query would be rejected on two grounds
• staff_no is not defined for Staff relation
  (should be staffNo).
• Comparison gt10 is incompatible with type
  position, which is variable character string.

9
1. Analysis

• Finally, query transformed into some internal
  representation more suitable for processing.
• Query tree typically, constructed as follows
• Leaf node created for each base relation.
• Non-leaf node created for each intermediate
  relation produced by RA operation.
• Root of tree represents query result.
• Sequence is directed from leaves to root.

10
1. Analysis
SELECT FROM Staff s, Branch b WHERE
s.branchNo b.branchNo AND (s.position
Manager AND b.city London)

• Finally, query transformed into some internal
  representation more suitable for processing.
• Relational Algebra Tree typically, constructed as
  follows
• Leaf node created for each base relation.
• Non-leaf node created for each intermediate
  relation produced by RA operation.
• Root of tree represents query result.
• Sequence is directed from leaves to root.

11
2. Normalization

• Converts query into a normalized form for easier
  manipulation.
• Predicate can be converted into one of two forms
• 1. Conjunctive normal form (? AND)
• (position 'Manager' ? salary gt 20000) ?
  (branchNo 'B003')
• 2. Disjunctive normal form (? OR)
• (position 'Manager' ? branchNo 'B003' ) ?
• (salary gt 20000 ? branchNo 'B003')
• Resulting tuples formed by Union of two results

12
3. Semantic Analysis

• Aim to reject incorrectly formulated or
  contradictory normalized queries
• Query is incorrectly formulated if components do
  not contribute to generation of result.
• Query is contradictory if its predicate cannot
  be satisfied by any tuple.
• Algorithms to determine correctness exist only
  for queries that do
• not contain disjunction and negation
• A relation connection graph (RC). If not
  connected, query is incorrectly formulated.
• Normalized attribute connection graph (NAC). If
  graph has cycle for which valuation sum is
  negative, query is contradictory.

13
3. Semantic Analysis

Example RC Graph (a) SELECT p.propertyNo,
p.street FROM Client c, Viewing v,
PropertyForRent p WHERE c.clientNo v.clientNo
AND c.maxRent gt 500 AND c.prefType
Flat AND p.ownerNo CO93
Example NAC Graph (b) SELECT p.propertyNo,
p.street FROM Client c, Viewing v,
PropertyForRent p WHERE c.maxRent gt 500 AND
c.clientNo v.clientNo AND v.propertyNo
p.propertyNo AND c.prefType Flat
AND c.maxRent lt 200

• Relation connection graph not fully
  connected, so query is not correctly formulated.
• Have omitted the join condition (v.propertyNo
  p.propertyNo)

graph has cycle between nodes c.maxRent and 0
with negative valuation sum, so query is
contradictory.
14
4. Simplification

• Aim Detects redundant qualifications,
• -eliminates common sub-expressions,
• -transforms query to semantically equivalent
  but more easily and efficiently computed
  form.
• Typically, access restrictions, view
  definitions, and integrity constraints are
  considered.
• Assuming user has appropriate access privileges,
  first apply well-known idempotency rules of
  Boolean algebra.
• Example p 613

5. Query Restructuring
Aim restructure query to provide a more
efficient implementation
15
Heuristic Approach to Query Optimisation
Heuristic approach uses transformational rules
to convert one RA expression into an equivalent
form that is known to be more efficient e.g. in
earlier example, perform Selection operation
before using the relation in a join rather than
vice versa. The following rules can be used to
restructure the RA tree generated during query
decomposition
16
Transformation Rules

• R, S, T are three relations. R is defined over
  attributes A A1, A2, , An and S over B
  B1, B2, , Bn.
• p, q, r denote predicates
• L, L1, L2, M, M1, M2 and N denote sets of
  attributes

17
Transformation Rules

• 1. Conjunctive Selection operations can cascade
  into individual Selection operations (and vice
  versa).
• ?p?q?r(R) ?p(?q(?r(R)))
• Sometimes referred to as cascade of Selection.
• ?branchNo'B003' ? salarygt15000(Staff)
  ?branchNo'B003'(?salarygt15000(Staff)).

• 2. Commutativity of Selection.
• ?p(?q(R)) ?q(?p(R))
• For example ?branchNo'B003'(?salarygt15000(Staff)
  )
• ?salarygt15000(?branchNo'B003'(Staff))

18
Transformation Rules

• 3. In a sequence of Projection operations, only
  the last in the sequence is required.
• ?L?M ?N(R) ?L (R)
• For example ?lName?branchNo, lName(Staff)
  ?lName (Staff)

• 4. Commutativity of Selection and Projection.
• If predicate p involves only attributes in
  projection list, Selection and Projection
  operations commute
• ?Ai, , Am(?p(R)) ?p(?Ai, , Am(R))
• where p? A1, A2, , Am
• For example ?fName, lName(?lName'Beech'(Staff))
  
• ?lName'Beech'(?fName,lName(Staff))

19
Transformation Rules

• 5. Commutativity of Theta join (and Cartesian
  product).
• R p S S p R
• R X S S X R
• Rule also applies to Equijoin and Natural join.
  For example
• Staff staff.branchNobranch.branchNo Branch
  Branch staff.branchNobranch.branchNoStaff
• 6. Commutativity of Selection and Theta join (or
  cartesian product)
• If selection predicate involves only attributes
  of one of join relations, Selection and Join (or
  Cartesian product) operations commute
• ?p(R r S) (?p(R)) r S
• ?p(R X S) (?p(R)) X S
• where p? A1, A2, , An

20
Transformation Rules

• 6. Commutativity of Selection and Theta join (or
  cartesian product) cont
• Alternatively if selection predicate is
  conjunctive predicate having form (p ? q), where
  p only involves attributes of R, and q only
  attributes of S, Selection and Theta join
  operations commute as
• ?p ? q(R r S) (?p(R)) r (?q(S))
• ?p ? q(R X S) (?p(R)) X (?q(S))
• For example
• ?position'Manager' ? city'London'(Staff
  Staff.branchNoBranch.branchNo Branch)
• (?position'Manager'(Staff))
  Staff.branchNoBranch.branchNo (?city'London
  (Branch))

21
Transformation Rules

• 7. Commutativity of Projection and Theta join (or
  Cartesian product).
• If projection list is of form L L1 ? L2, where
  L1 only has attributes of R, and L2 only has
  attributes of S, provided join condition only
  contains attributes of L, Projection and Theta
  join commute
• ?L1?L2(R r S) (?L1(R)) r (?L2(S))
• If join condition contains additional attributes
  not in L (M M1 ? M2 where M1 only has
  attributes of R, and M2 only has attributes of
  S), a final projection operation is required
• ?L1?L2(R r S) ?L1?L2( (?L1?M1(R)) r
  (?L2?M2(S)))

22
Transformation Rules

• 8. Commutativity of Union and Intersection (but
  not set difference). R ? S S ? R
• R ? S S ? R
• 9. Commutativity of Selection and set operations
  (Union, Intersection, and Set difference). ?p(R ?
  S) ?p(S) ? ?p(R)
• ?p(R ? S) ?p(S) ? ?p(R)
• ?p(R - S) ?p(S) - ?p(R)

• 10. Commutativity of Projection and Union.
• ?L(R ? S) ?L(S) ? ?L(R)
• 11. Associativity of Union and Intersection (but
  not Set difference).
• (R ? S) ? T S ? (R ? T)
• (R ? S) ? T S ? (R ? T)

23
Transformation Rules

• 12. Associativity of Theta join (and Cartesian
  product).
• Cartesian product and Natural join are always
  associative
• (R S) T R (S T)
• (R X S) X T R X (S X T)
• If join condition q involves attributes only from
  S and T, then Theta join is associative
• (R p S) q ? r T R p ? r (S
  q T)
• For example (Staff Staff.staffNoPropertyFor
  Rent.staffNo PropertyForRent)
• ownerNoOwner.ownerNo ? staff.lNameOwner.l
  Name Owner
• Staff staff.staffNoPropertyForRent.staffNo
  ? staff.lNamelName (PropertyForRent
  ownerNo Owner)

24
Example Transformation Rules
25
Transformation Rules
Heuristic approach
Example For prospective renters of flats, find
properties that match requirements and owned by
CO93. SQL SELECT p.propertyNo, p.street FROM
Client c, Viewing v, PropertyForRent p WHERE
c.prefType Flat AND c.clientNo v.clientNo
AND v.propertyNo p.propertyNo AND
c.maxRent gt p.rent AND c.prefType p.type
AND p.ownerNo CO93
26
Transformation Rules example
Heuristic approach
Push Selection rows down using rules 1, 2,
and 6
(a) Canonical RA tree
27
Transformation Rules example
Heuristic approach
Use associativity of equijoins (rule 11) to
reorder.
(c) After changing Selection/Cartesians to
Equijoins
28
Transformation Rules example
Heuristic approach
Substitute c.prefTypep.type to p.typeFlat
(as we know c.prefType Flat)
(e) Formed by pushing projections down, rules 4
and 7
29
Heuristic Processing Strategies
Heuristic approach

• Perform Selection operations as early as
  possible.
• Keep predicates on same relation together.
• Combine Cartesian product with subsequent
  Selection whose predicate represents join
  condition into a Join operation.
• Use associativity of binary operations to
  rearrange leaf nodes so leaf nodes with most
  restrictive Selection operations executed first.
• Perform Projection as early as possible.
• Keep projection attributes on same relation
  together.
• Compute common expressions once.
• If common expression appears more than once, and
  result not too large, store result and reuse it
  when required.
• Useful when querying views, as same expression is
  used to construct view each time.

30
Cost Estimations
31
Cost estimation for RA operations

• Previously
• Many different ways of implementing RA
  operations.
• Aim of QO is to choose most efficient one.
• Success of estimation depends on amount and
  currency of statistical info DBMS holds.
• How?
• Use formula that estimate costs for a number of
  options select one with lowest cost.
• Consider only cost of disk access, which is
  usually dominant cost in QP.
• estimate required number of disk block accesses.
• Many estimates are based on cardinality of the
  relation.

32
Database Statistics

• Expect DBMS to hold following types of info in
  system catalog
• For each base relation R
• nTuples(R) no. of tuples in R.
• bFactor(R) - blocking factor of R.
• nBlocks(R) no. of blocks required to store R
  nBlocks(R)nTuples(R)/bFactor(R)
• For each Attribute A of base relation R
• nDistinctA(R) no. of distinct values that
  appear for attribute A in R.
• minA(R),maxA(R) - min and max possible values for
  attribute A in R.
• SCA(R) - selection cardinality of attribute A in
  R. Average no. of tuples that satisfy an equality
  condition on attribute A.
• For each multilevel index I on attribute set A
• nLevelsA(I) - number of levels in I.
• nLfBlocksA(I) - number of leaf blocks in I.

Pg 620
33
Selection operation

• Selection operation works on a single relation R
  and defines a relation S
• on the tuples of R that satisfy the predicate
  (simple or composite).
• No. of different implementations, depending on
• file structure
• whether attribute(s) involved are indexed/hashed.
  
• Main strategies are
• Linear Search (Unordered file, no index).
• Binary Search (Ordered file, no index).
• Equality on hash key.
• Equality condition on primary key.
• Inequality condition on primary key.
• Equality condition on clustering (secondary)
  index.
• Equality condition on a non-clustering
  (secondary) index.
• Inequality condition on a secondary B-tree
  index.

34
Selection operation

• Estimating cardinality of the Selection
  operation
• Assume attribute values are uniformly distributed
  within their domain and attributes are
  independent.
• nTuples(S) SCA(R)
• For any attribute B ? A of S
• nTuples(S) if nTuples(S) lt
  nDistinctB(R)/2
• nDistinctB(S) nDistinctB(R) if nTuples(S) gt
  2nDistinctB(R)
• (nTuples(S) nDistinctB(R))/3
  otherwise

Pg 622
35
Selection operation

• Linear search (unordered file, no index)
• For equality condition on key attribute
• - only one tuple matches, so cost estimate is
  nBlocks(R)/2
• For any other condition entire file may need
  searching,
• multiple matches, more general cost estimate
  nBlocks(R)
• Binary search (ordered file, no index)
• Equality predicate on A and file is ordered on
  key attribute A
• - binary chop, only one tuple matches, cost
  estimate log2(nBlocks(R))
• Generally
• SCA(R) tuples match, cost estimate is
• log2(nBlocks(R))SCA(R)/bFactor(R) 1

See the book for more examples!
Pg 622
36
Cost Estimation

• Example Page 627
• Problem 20.18 a

37
Pipelining

• Materialization - output of one operation stored
  in temp relation for processing by next.
• Alternative
• Pipeline results of 1 operation to another
  without creating temp relation. Pipelining or
  on-the-fly processing.
• can save on cost of
• creating temp relations
• reading results back in again.
• Generally, pipeline is implemented as separate
  process or thread.

38
Programmatic SQL
39
Embedded SQL

• SQL standard lacks computational completeness
• (no flow of control commands, IF,..THEN..ELSE, )
• Overcome by
• SQL allows statements to be embedded in
  high-level procedural languages.
• 2 Programmatic SQL types
• Embedded SQL statements SQL supports Ada, C,
  COBOL, FORTRAN, MUMPS, Pascal, and PL/1.
• Application Programming Interface (API) best
  known-ODBC

40
Embedded SQL

• There are 2 types of embedded SQL
• Static entire SQL statement known when program
  written
• Dynamic all or part of statement may be
  specified at runtime

41
Simple embedded SQL statements

• Simplest return no query results non-SELECT.
• I.e. INSERT, UPDATE, DELETE
• Basic concepts
• Embedded SQL statements start with identifier
  (EXEC SQL)
• Ends with terminator dependent on host language
• Ada, C, and PL/1 terminator is ()
• COBOL terminator is END-EXEC
• Fortran ends when no more continuation lines.
• Embedded SQL can appear anywhere an executable
  host language statement can appear

42
SQL Communications Area(SQLCA)

• SQLCA reports runtime errors to application
  program
• Data structure, contains
• Error variables
• Status indicators
• To use include EXEC SQL INCLUDE sqlca
• Most important part is SQLCODE variable
• 0 - statement executed successfully
• lt 0 - an error occurred
• gt 0 - statement executed successfully, but
  exception occurred,e.g. no more rows returned by
  SELECT.

43
(SQLCA)Whenever statement

• EXEC SQL WHENEVER ltconditiongtltactiongt
• Automatically generates code to handle errors
  after every SQL statement.

Example in code segment EXEC SQL WHENEVER
SQLERROR GOTO error1 EXEC SQL INSERT INTO
Viewing VALUES (CR76, PA14, 12-May-2001,
Not enough space) would be converted by
precompiler to EXEC SQL INSERT INTO Viewing
VALUES (CR76, PA14, 12-May-2001, Not
enough space) if (sqlca.sqlcode lt 0) goto
error1

• action can be
• CONTINUE
• DO
• DO BREAK
• DO CONTINUE
• GOTO label or GO TO label
• STOP

• condition can be
• SQLERROR
• SQLWARNING
• NOT FOUND

44
Host Language Variable

• Host Language variable variable declared in host
  language.
• Can be used in embedded SQL to transfer data from
  database to program vice versa
• Can be used anywhere a constant would appear
• Cannot be used to represent database objects
  (tables, )
• To use variable name prefixed by colon

• Example
• EXEC SQL UPDATE Staff
• SET salary salary increment
• WHERE staffNo SL21
• Need to declare host language variables to SQL
  and host language
• EXEC SQL BEGIN DECLARE SECTION
• float increment
• EXEC SQL END DECLARE SECTION

45
Host Language Variable

• Indicator variables resolve problem that
  relational model contains missing or unknown
  values (NULL).
• Each host variable has an indicator variable that
  can be examined.
• Meanings
• ?0 - assoc host variable contains valid value.
• lt0 - assoc host variable should be assumed to
  contain a null actual contents of host
  variable irrelevant.
• Used immediately following assoc host variable
  with () separating two variables.

Example set adress col from CO21 to NULL EXEC
SQL BEGIN DECLARE SECTION char address51 s
hort addressInd EXEC SQL END DECLARE
SECTION addressInd -1 EXEC SQL UPDATE
PrivateOwner SET address address
addressInd WHERE ownerNo CO21
46
Retrieving data using embedded SQL and cursors

• Instead of retrieving using SELECT
  complicated, impedance mismatch
• SQL divides queries into 2 groups
• Single-row queries result contains at most 1 row
• Multi-row queries result contains at least 0
  rows

47
Retrieving data using embedded SQL and cursors
7.2 Embedded SQL

• Single-row queries result contains at most 1 row
• Handled by singleton select statement
• same format as SELECT
• Extra INTO clause host variable names
• Must be 11 correspondence between expressions in
  SELECT list and host variables in INTO

• If successful SQLCODE set to 0.
• If there are no rows that satisfies WHERE
  SQLCODE set to NOT FOUND (100)

Example retrieve details of owner from CO21 EXEC
SQL SELECT fName, lName, address INTO firstName,
lastName, address addressInd FROM
PrivateOwner WHERE ownerNo CO21
48
Retrieving data using embedded SQL and cursors

• 2. Multi-row queries result contains at least 0
  rows
• Embedded SQL uses Cursors when query returns
  arbitrary no. of rows
• Cursor
• allows host language access of rows one at a
  time.
• acts as a pointer to a row of query result.
• must be declared and opened before it can be used
  and it must be closed to deactivate it after it
  is no longer required.

49
Retrieving data using embedded SQL and cursors

• EXEC SQL DECLARE cursorName CURSOR FOR
  selectStatement
• Defines specific SELECT to be performed and assoc
  cursor name with query

• EXEC SQL OPEN cursorName
• Executes query, identifies all rows satisfying
  query conditions
• Positions cursor before first row of results
  table
• EXEC SQL FETCH cursorName INTO hostVarindicatorV
  ar,
• Retrieves the next row of active set (Oracle name
  for results rows active set of the cursor)
• EXEC SQL CLOSE cursorName
• Closes cursor.

Example EXEC SQL DECLARE propertyCursor CURSOR
FOR SELECT propertyNo, street, city FROM
PropertyForRent WHERE staffNo SL41
50
Using cursors to modify data

• Cursor is either
• Readonly table/view identified by cursor not
  updatable
• Updatable otherwise. Can use POSITIONED DELETE
  CURRENT

• EXEC SQL DECLARE cursorName CURSOR FOR
  selectStatement FOR UPDATE OF columnName ,
• Used to update through a cursor in Oracle.
• EXEC SQL UPDATE TableName SET columnName
  dataValue ,WHERE CURRENT OF cursorName
• Format of Cursor based update.
• EXEC SQL DELETE FROM TableName WHERE CURRENT OF
  cursorName
• Format of Cursor based delete.

51
ISO standard for Embedded SQL

• Differences between Oracle embedded SQL dialect
  ISO standard
• Cursors
• ISO specifies and defines slightly differently
• ISO DECLARE CURSOR
• EXEC SQL DECLARE cursorName INSENSITIVESCROLL
  
• CURSOR FOR selectStatement FORREAD ONLY
  UPDATEOF columnNameList
• FETCH statement
• EXEC SQL FETCH fietchOrientation
• FROM cursorName INTO hostVariable,

• fetchOrientation one of
• NEXT
• PRIOR
• FIRST
• LAST
• ABSOLUTE
• RELATIVE

52
Dynamic SQL

• Many situations where the pattern of database
  access not fixed only known at runtime.
• Basic difference
• Static does not allow host variables to be used
  in place of table names or column names.
• Dynamic overcomes this idea is to place complete
  SQL statement to be executed in a host variable.
  This is then passed to the DBMS to be executed.

53
The EXECUTE IMMEDIATE statement

• EXEC SQL EXECUTE IMMEDIATE hostVar
  stringLiteral
• This command allows SQL statement stored in host
  variable
• (or literal string) to be executed.
• - Only if no SELECT in statement.

Example (buffer is host variable char
buffer100) sprintf(buffer, UPDATE Staff SET
salary salary f WHERE staffNo SL21 ,
increment) EXEC SQL EXECUTE IMMEDIATE buffer
54
Comparison of static and dynamic processing by
DBMS

• DBMS must
• parse,
• validate,
• and optimize
• each EXECUTE IMMEDIATE statement,
• build execution plan
• and execute plan.
• Inefficient if executes gt 1

Static SQL
Dynamic SQL
55
PREPARE and EXECUTE

• Alternative approach for SQL statements with
  executes gt1
• PREPARE statement
• EXEC SQL PREPARE statementName FROM
  hostVarstringLiteral
• instructs DBMS to ready dynamically built
  statement for later execution.
• Prepared statement assigned name.
• EXECUTE statement
• EXEC SQL EXECUTE statementName
• USING hostVar indicatorVar USING
  DESCRIPTOR descriptorName
• When statement is subsequently executed, program
  need only specify this name

56
PREPARE and EXECUTE

• USING allows portions of prepared statement to be
• unspecified, replaced by placeholders (parameter
  markers).
• Placeholder can appear anywhere in hostVariable
  or stringLiteral of PREPARE that constant can
  appear.
• Tells DBMS value will be supplied later, in
  EXECUTE statement.

• Example
• sprintf(buffer, UPDATE Staff SET salary sal
  WHERE staffNo sn)
• EXEC SQL PREPARE stmt FROM buffer
• EXEC SQL EXECUTE stmt USING newSalary, staffNo
• - sal and sn are placeholders.

57
The SQL Descriptor Area (SQLDA)

• Alternative to placeholders (to pass parameters
  to EXECUTE)
• Used when no. of params and their data types
  unknown at statement formulation.
• SQLDA can also be used to dynamically retrieve
  data when do not know no. or types of columns to
  be retrieved.
• Two SQL statements to set up and access SQLDA
• DESCRIBE BIND VARIABLES (host vars also known as
  bind vars) fills in an SQLDA for any bind
  variables specified in the query.
• DESCRIBE SELECT LIST fills in an SQLDA for column
  data when wish to dynamically retrieve data and
  number of columns to be retrieved or types of the
  columns are not known.

58
DESCRIBE statement

• EXEC SQL DESCRIBE BIND VARIABLES FOR
  statementName
• INTO bindDescriptorName
• EXEC SQL DESCRIBE SELECT LIST FOR statementName
• INTO selectDescriptorName
• Returns
• names,
• data types,
• lengths of columns/ placeholders,
• specified in query into an SQLDA.
• For non-select, sets F field to 0. (Field F
  actual no.of placeholders or SELECT cols found by
  DESCRIBE).
• statementName is name of prepared statement
• DescriptorNames are names of initialized SQLDAs.

59
DESCRIBE statement
8.3 Dynamic SQL

• Example
• sprintf(query,
• SELECTpropertyNo,
• rent FROM
• PropertyForRent)
• EXEC SQL PREPARE
• stmt FROM query
• EXEC SQL
• DESCRIBE SELECT
• LIST FOR stmt INTO
• sqlda

60
Retrieving Data Using Dynamic SQL

• Again, use cursors to retrieve data from a query
  result table that has an arbitrary number of
  rows.
• Format of DECLARE, OPEN, FETCH and CLOSE for
  dynamic SQL
• EXEC SQL DECLARE cursorName CURSOR FOR
  selectStatementEXEC SQL OPEN cursorName FOR
  READONLY USING hostVariable indicatorVariable
  ,... USING DESCRIPTOR descriptorName
• EXEC SQL FETCH cursorName INTO hostVariable
  indicatorVariable ,... USING DESCRIPTOR
  descriptorName
• EXEC SQL CLOSE cursorName

• OPEN allows values for placeholders to be
  substituted using one or more hostVariables in
  USING clause or passing values via descriptorName
  (SQLDA) in a USING DESCRIPTOR clause.
• Main difference is with FETCH, now uses
  descriptorName to receive rows of query result
  table (or one or more hostVariables/
  indicatorVariables).

61
ISO Standard for Dynamic SQL

• So far we have been looking at Oracle dynamic
  SQL. There are some differences with the ISO
  standard.
• SQLDA in the ISO standard is treated like a
  variable of an ADT (abstract data type).
• Programmer has access only to the SQLDA using set
  of methods.
• An SQLDA is allocated and deallocated using
  statements
• ALLOCATE DESCRIPTOR descriptorName WITH MAX
  occurrences
• DEALLOCATE DESCRIPTOR descriptorName

62
ISO Standard for Dynamic SQL
8.3 Dynamic SQL

• So far we have been looking at Oracle dynamic
  SQL. There are some differences with the ISO
  standard.
• SQLDA in the ISO standard is treated
• like a variable of an Abstract DatatType.
• Programmer has access only to the
• SQLDA using set of methods.
• An SQLDA is allocated and deallocated
• using statements
• ALLOCATE DESCRIPTOR descriptorName
• WITH MAX occurrences
• DEALLOCATE DESCRIPTOR descriptorName

• Some common ISO descriptor names are
• TYPE data type of item
• LENGTH length of data in the item
• INDICATOR associated indicator value
• DATA the data value.

63
ISO Standard for Dynamic SQL

• Standard also provides two DESCRIBE statements to
• distinguish between description of input and
  output
• parameters.
• DESCRIBE INPUT provides description of input
  params (placeholders) for a prepared statement.
• DESCRIBE OUTPUT provides description of resultant
  cols of dynamic SELECT statement.
• In both cases, format is similar to DESCRIBE
  statements

64
Open Database Connectivity Standard

• API, rather than embedding raw SQL within program
• DBMS vendor provides API.
• API set of library functions for many common
  types of database accesses.
• One problem lack of interoperability.
• To standardize, Microsoft produced ODBC standard.
  
• ODBC provides common interface for accessing
  heterogeneous SQL databases, based on SQL.
• ODBC has emerged as a de facto industry standard.
  

65
The ODBC Architecture

• ODBC architecture has four components
• Application performs processing and calls ODBC
  functions to submit SQL statements to DBMS and to
  retrieve results from DBMS.
• Driver Manager loads drivers on behalf of
  application. Driver Manager, provided by
  Microsoft, is Dynamic-Link Library (DLL).
• Driver and Database Agent process ODBC function
  calls, submit SQL requests to specific data
  source, and return results to application.
• Data Source consists of data user wants to
  access and its associated DBMS, and its host
  operating system, and network platform, if any.

66
The ODBC Architecture
67
(No Transcript)