Programmatic SQL

1
Chapter 21

• Programmatic SQL
• Transparencies

2
Chapter 21 - Objectives

• How SQL statements can be embedded in high-level
  programming languages.
• Difference between static and dynamic embedded
  SQL.
• How to write programs that use static embedded
  SQL.
• How to write programs that use dynamic embedded
  SQL.
• How to use the Open Database Connectivity (ODBC)
  de facto standard.

3
Embedded SQL

• SQL can be embedded in high-level procedural
  language.
• In many cases, language is identical although
  SELECT statement differs.
• Two types of programmatic SQL
• Embedded SQL statements.
• SQL supports Ada, C, COBOL, FORTRAN, MUMPS,
  Pascal, and PL/1.
• Application program interface (API).

4
Example 21.1 - CREATE TABLE

• EXEC SQL CREATE TABLE Viewing (
• propertyNo VARCHAR2(5) NOT NULL,
• clientNo VARCHAR2(5) NOT NULL,
• viewDate DATE NOT NULL,
• comment VARCHAR2(40))
• if (sqlca.sqlcode gt 0)
• printf(Creation successful\n)

5
Embedded SQL

• Embedded SQL starts with identifier, usually EXEC
  SQL _at_SQL( in MUMPS.
• Ends with terminator dependent on host language
• Ada, C, and PL/1 terminator is semicolon ()
• COBOL terminator is END-EXEC
• Fortran ends when no more continuation lines.
• Embedded SQL can appear anywhere an executable
  host language statement can appear.

6
SQL Communications Area (SQLCA)

• Used to report runtime errors to the application
  program.
• Most important part is SQLCODE variable
• 0 - statement executed successfully
• lt 0 - an error occurred
• gt 0 - statement executed successfully, but an
  exception occurred, such as no more rows returned
  by SELECT.

7
SQLCA for Oracle
8
WHENEVER Statement

• Every embedded SQL statement can potentially
  generate an error.
• WHENEVER is directive to precompiler to generate
  code to handle errors after every SQL statement
• EXEC SQL WHENEVER
• ltconditiongt ltactiongt

9
WHENEVER Statement

• condition can be
• SQLERROR - generate code to handle errors
  (SQLCODE lt 0).
• SQLWARNING - generate code to handle warnings.
• NOT FOUND - generate code to handle specific
  warning that a retrieval operation has found no
  more records.

10
WHENEVER Statement

• action can be
• CONTINUE - ignore condition and proceed to next
  statement.
• DO - transfer control to an error handling
  function.
• DO BREAK - place an actual break statement in
  the program.
• DO CONTINUE - place an actual continue
  statement in the program.
• GOTO label or GO TO label - transfer control to
  specified label.
• STOP - rollback all uncommitted work and
  terminate the program.

11
WHENEVER Statement

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

12
Host Language Variables

• Program variable declared in host language.
• Used in embedded SQL to transfer data from
  database into program and vice versa.
• Can be used anywhere a constant can appear.
• Cannot be used to represent database objects,
  such as table names or column names.
• To use host variable, prefix it by a colon ().

13
Host Language Variables

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

14
Indicator Variables

• Indicates presence of null
• 0 associated host variable contains valid
  value.
• lt0 associated host variable should be assumed to
  contain a null actual contents of host variable
  irrelevant.
• gt0 associated host variable contains valid value.
• Used immediately following associated host
  variable with a colon () separating two
  variables.

15
Indicator Variables - Example

• EXEC SQL BEGIN DECLARE SECTION
• char address51
• short addressInd
• EXEC SQL END DECLARE SECTION
• addressInd -1
• EXEC SQL UPDATE PrivateOwner
• SET address address addressInd
• WHERE ownerNo CO21

16
Singleton SELECT - Retrieves Single Row

• EXEC SQL SELECT fName, lName, address
• INTO firstName, lastName, address
  addressInd
• FROM PrivateOwner
• WHERE ownerNo CO21
• Must be 11 correspondence between expressions in
  SELECT list and host variables in INTO clause.
• If successful, SQLCODE set to 0 if there are no
  rows that satisfies WHERE, SQLCODE set to NOT
  FOUND.

17
Cursors

• If query can return arbitrary number of rows,
  need to use cursors.
• Cursor allows host language to access rows of
  query one at a time.
• Cursor acts as a pointer to a row of query
  result. Cursor can be advanced by one to access
  next row.
• Cursor must be declared and opened before it can
  be used and it must be closed to deactivate it
  after it is no longer required.

18
Cursors - DECLARE CURSOR

• Once opened, rows of query result can be
  retrieved one at a time using FETCH
• EXEC SQL DECLARE
• propertyCursor CURSOR FOR
• SELECT propertyNo, street, city
• FROM PropertyForRent
• WHERE staffNo SL41

19
Cursors - OPEN

• OPEN statement opens specified cursor and
  positions it before first row of query result
• EXEC SQL OPEN propertyCursor

20
Cursors - FETCH and CLOSE

• FETCH retrieves next row of query result table
• EXEC SQL FETCH propertyCursor
• INTO propertyNo, street, city
• FETCH is usually placed in a loop. When there are
  no more rows to be returned, SQLCODE is set to
  NOT FOUND.
• EXEC SQL CLOSE propertyCursor

21
ISO Standard for Embedded SQL

• Standard does not recognize SQLWARNING of
  WHENEVER statement.
• Standard does not mention an SQLCA. It does,
  however, recognize the integer variable SQLCODE,
  although this is a deprecated feature that is
  supported only for compatibility with earlier
  versions of the standard.
• Instead, defines a character string SQLSTATE
  parameter, comprising a two-character class code
  followed by a three-character subclass code.

22
ISO Standard for Embedded SQL

• Standard specifies definition and processing of
  cursors slightly differently from that presented
  above. ISO DECLARE CURSOR is
• EXEC SQL DECLARE cursorName
• INSENSITIVE SCROLL
• CURSOR FOR selectStatement
• FOR READ ONLY
• UPDATE OF columnNameList

23
ISO Standard for Embedded SQL

• FETCH statement
• EXEC SQL FETCH fetchOrientation FROM
• cursorName INTO hostVariable , . . .
• fetchOrientation can be one of
• NEXT
• PRIOR
• FIRST
• LAST
• ABSOLUTE
• RELATIVE

24
Dynamic Embedded SQL

• With static embedded SQL, cannot use host
  variables where database object names required.
• Dynamic SQL allows this.
• Idea is to place complete SQL statement in a host
  variable, which is passed to DBMS to be executed.
• If SQL statements do not involve multi-row
  queries, use EXECUTE IMMEDIATE statement
• EXEC SQL EXECUTE IMMEDIATE
• hostVariable stringLiteral

25
Dynamic Embedded SQL

• For example
• sprintf(buffer, UPDATE Staff
• SET salary salary f
• WHERE staffNo SL21 , increment)
• EXEC SQL EXECUTE IMMEDIATE buffer

26
PREPARE and EXECUTE

• DBMS must parse, validate, and optimize each
  EXECUTE IMMEDIATE statement, build execution
  plan, and execute plan.
• OK if SQL statement is only executed once in
  program otherwise inefficient.
• Dynamic SQL provides alternative PREPARE and
  EXECUTE.
• PREPARE tells DBMS to ready dynamically built
  statement for later execution.

27
PREPARE and EXECUTE

• Prepared statement assigned name. When statement
  is subsequently executed, program need only
  specify this name
• EXEC SQL PREPARE statementName
• FROM hostVariable stringLiteral
• EXEC SQL EXECUTE statementName
• USING hostVariable indicatorVariable , ...
  
• USING DESCRIPTOR descriptorName

28
Parameter Markers

• 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.

29
Placeholders

• 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.

30
Static versus Dynamic SQL
31
SQL Descriptor Area (SQLDA)

• Alternative way to pass parameters to EXECUTE
  statement is through SQLDA.
• Used when number of parameters and their data
  types unknown when statement formulated.
• SQLDA can also be used to dynamically retrieve
  data when do not know number of columns to be
  retrieved or the types of the columns.

32
SQLDA for Oracle
33
SQL Descriptor Area (SQLDA)

• Two SQL statements to set up and access SQLDA
• DESCRIBE BIND VARIABLES (host variables also
  known as bind variables) 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.

34
Fields in SQLDA

• N field maximum number of placeholders or SELECT
  list columns that can be described.
• Set by application. After DESCRIBE, N and F
  fields are set to actual number of elements.
• F field actual number of placeholders or
  SELECT list columns found by DESCRIBE.

35
Fields in SQLDA

• V field pointer to array of addresses to data
  buffers that store the input host variables or
  SELECT list columns. Set by application.
• For placeholders, data buffers must be allocated
  and array set prior to OPEN for SELECT list
  columns, data buffers must be allocated and array
  set prior to first FETCH.

36
Fields in SQLDA

• L field pointer to array of lengths of input
  host variables or SELECT list columns.
• For placeholders, lengths must be set prior to
  OPEN for SELECT list columns, DESCRIBE sets
  maximum length for each column value, which can
  be modified.
• T field pointer to array of data type codes for
  input host variables or SELECT list columns.

37
Fields in SQLDA

• I field pointer to array of addresses of buffers
  that store indicator variable values.
• For placeholders, these values must be set prior
  to OPEN for SELECT list columns, must be set
  prior to FETCH.
• S field pointer to array of addresses of data
  buffers to store placeholder names or SELECT list
  names.
• Buffers are allocated and their addresses stored
  in S by SQLSQLDAAlloc(). DESCRIBE stores the
  names in these buffers.

38
Fields in SQLDA

• M field pointer to array of maximum lengths of
  data buffers to store placeholder names or SELECT
  list names.
• Set by Oracle SQLSQLDAAlloc() function.
• C field pointer to array of current lengths of
  placeholder names or SELECT list names.
• Set by DESCRIBE.

39
Fields in SQLDA

• X field pointer to array of addresses of data
  buffers to store indicator variable names. This
  field applies only to placeholders.
• Buffers are allocated and their addresses stored
  in X by SQLSQLDAAlloc(). DESCRIBE stores the
  names in these buffers.
• Y field pointer to array of maximum lengths of
  data buffers to store indicator variable names.
  Again, this field applies only to placeholders.
• Set by SQLSQLDAAlloc().

40
Fields in SQLDA

• Z field pointer to array of current lengths of
  indicator variable names. As with X and Y, this
  field applies only to placeholders.
• Set by DESCRIBE.

41
DESCRIBE

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

42
DESCRIBE

• sprintf(query, SELECT propertyNo, rent
• FROM PropertyForRent)
• EXEC SQL PREPARE stmt FROM query
• EXEC SQL DESCRIBE SELECT LIST FOR
• stmt INTO sqlda
• sqlda will be filled in as shown overleaf.

43
DESCRIBE Completed SQLDA
44
Retrieving Data Using Dynamic SQL

• Again, use cursors to retrieve data from a query
  result table that has an arbitrary number of
  rows.
• EXEC SQL DECLARE cursorName
• CURSOR FOR selectStatement
• EXEC 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

45
Retrieving Data Using Dynamic SQL

• 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, which now uses
  descriptorName to receive rows of query result
  table (or one or more hostVariables/
  indicatorVariables).

46
Retrieving Data Using Dynamic SQL

• Before FETCH, program must provide data areas to
  receive retrieved data and indicator variables.
• Basic steps for dynamic SQL statement are
• (1) Declare host string in DECLARE SECTION to
  hold text of the query.
• (2) Declare a select SQLDA and, if required, a
  bind SQLDA.
• (3) Allocate storage space for the SQLDA(s).
• (4) Set maximum number of columns in select SQLDA
  and, if query can have placeholders, maximum
  number of placeholders in bind SQLDA.

47
Retrieving Data Using Dynamic SQL

• (5) Put the query text into the host string.
• (6) PREPARE the query from the host string.
• (7) DECLARE a cursor for the query.
• (8) If the query can have placeholders
• DESCRIBE bind variables into bind SQLDA.
• Reset number of placeholders to number actually
  found by DESCRIBE.
• Get values and allocate storage space for bind
  variables found by DESCRIBE.
• (9) OPEN cursor USING the bind SQLDA, or if no
  bind SQLDA has been used, USING the select SQLDA.

48
Retrieving Data Using Dynamic SQL

• (10) DESCRIBE the column list INTO the select
  SQLDA.
• (11) Reset number of column list items to number
  actually found by DESCRIBE.
• (12) Reset length and data type of each column
  list item.
• (13) FETCH each row from database into allocated
  data buffers pointed to by the select SQLDA and
  process it, as appropriate.
• (14) Deallocate the storage space used for the
  column list items, placeholders, indicator
  variables, and SQLDAs.
• (15) CLOSE the cursor.

49
ISO Standard for Dynamic SQL

• The SQLDA in the ISO standard is treated like a
  variable of an ADT. 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

50
ISO Standard for Dynamic SQL

• The SQLDA can be accessed using
• GET DESCRIPTOR descriptorName hostVariable
  COUNT VALUE itemNumber hostVariable1
  itemName1 ,
• SET DESCRIPTOR descriptorName COUNT
  hostVariable VALUE itemNumber itemName1
  hostVariable1 ,
• 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.

51
ISO Standard for Dynamic SQL

• For example, to get maximum number of allocated
  elements of the descriptor, count, say
• EXEC SQL GET DESCRIPTOR sqlda count COUNT
• To set up first element of descriptor
• EXEC SQL SET DESCRIPTOR sqlda
• VALUE 1 INDICATOR 1 DATA data

52
ISO Standard for Dynamic SQL

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

53
Open Database Connectivity (ODBC)

• With an API, rather than embedding raw SQL
  within program, DBMS vendor provides API.
• API consists of set of library functions for many
  common types of database accesses.
• One problem with this approach has been lack of
  interoperability.
• To standardize this approach, Microsoft produced
  ODBC standard.
• ODBC provides common interface for accessing
  heterogeneous SQL databases, based on SQL.

54
Open Database Connectivity (ODBC)

• Interface (built on C) provides high degree of
  interoperability single application can access
  different SQL DBMSs through common code.
• Enables developer to build and distribute c-s
  application without targeting specific DBMS.
• Database drivers are then added to link
  application to users choice of DBMS.
• ODBC has emerged as a de facto industry standard.

55
ODBCs Flexibility

• Applications not tied to proprietary vendor API.
• SQL statements can be explicitly included in
  source code or constructed dynamically.
• An application can ignore underlying data
  communications protocols.
• Data can be sent and received in format that is
  convenient to application.
• Design aligned with X/Open and ISO CLI.
• There are ODBC drivers available today for many
  of most popular DBMSs.

56
ODBC Interface

• Library of functions that allow application to
  connect to DBMS, execute SQL statements, and
  retrieve results.
• A standard way to connect and log on to a DBMS.
• A standard representation of data types.
• A standard set of error codes.
• SQL syntax based on the X/Open and ISO Call-Level
  Interface (CLI) specifications.

57
ODBC Architecture

• ODBC architecture has four components
• Application,
• Driver Manager,
• Driver and Database Agent,
• Data Source.

58
ODBC Architecture

• 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).

59
ODBC Architecture

• Driver and Database Agent - process ODBC
  function calls, submit SQL requests to specific
  data source, and return results to application.
• If necessary, driver modifies applications
  request so that it conforms to syntax supported
  by associated DBMS.
• With multiple drivers, these tasks performed by
  the driver no database agent exists.
• With single driver, agent designed for each
  associated DBMS and runs on database server side.

60
ODBC Architecture

• Data Source - consists of data user wants to
  access and its associated DBMS, and its host
  operating system, and network platform, if any.

61
ODBC Architecture