Chapter4 -> Application Programs and OR Capabilities

Question 1

Explain the idea behind locators as an approach to external access to structured types.

Answer 1

• > Reference to SQL data item inside DB , which can be used in statements
• > Generated by SQL engine and transferred to app (NOT an SQL data type /DB level concept)
  • Inroduced for LOBs
• > specify host variable AS LOCATOR -> impl. dependent integer pointer
• > types can be specified as locators

Question 2

How can locators be used in external routines?

Answer 2

Specify parameter or return types (which are UDT/collection) AS LOCATOR

• > invoking generate and pass inter as parameter
• > returning…

Question 3

Give an overview over Transforms

Answer 3

• > UDFs invoked automatically when UDT values are exchanged between SQL/app
• > associate UDT with transform groups (pars of functions from-SQL to-SQL)
• maps predefined value

Question 4

How can transforms be created and how do thez work together with methods?

Answer 4

CREATE TRANSFORM FOR  
group1 (FROM SQL WITH FUNCTION F(),
TO SQL WITH FUNCTION G();
------------------------
IMPLICIT TRANSFORMS FOR DISTINCT TyPES: CAST FUNCTIONS

Question 5

Give an overview of how transforms are used in SQL

Answer 5

• > Embedded program can specify transform group
• > host variable specify predefined type, which must be the same of TO/FROM SQL functions
  • > Functions are invoked automatically when reading/writting host var
  • > In external routines: host language type = predef type
  • > To SQL methods: type preserving

Question 6

Give an overview of complex value transfers.

Answer 6

• Uses transparent proprietary format, supported jointly by DB+API(JDBC e.g.)
• Generic object structures on app side (e.g. getString(1)

Question 7

How can transforms work together with methods?

Answer 7

FROM/TO SQL WITH METHOD m() FOR

** m( ) can be overriden.

Question 8

Give an overview of JDBC and its support for Complex Value Transfers.

Answer 8

JDBC: getConnection, createStatement, executeQuery, rs.next()+rs.getString() (useful for generic apps/tools)

• > Generic Objects: attribute values as array of Java objects
• > JDBC interface Struct: String getTzpeName(), object[ ] getAttributtes() (rs.getObject()->struct)

Question 9

How does JDBC support mapping of UDTs to Java?

Answer 9

Works transparently (get/set object on ResultSet or prepared statement)

 - > mapping UDT instance to Java instances (mapping table records correspondence)
- > Java class implements SQLData (read/write SQL methods)
- > SQL Input/Output stream interfaces

Question 10

Give an overview of OLB (SQLJ Part 0) and compare it to JDBC.

Answer 10

-> static embedded SQL in Java (More concise, easier than JDBC) -> Static type checking

• > Composite types/UDT -> Based on JDBC
  • > (type mapping can be supplied on properties file)

Question 11

Compare the advantages and disadvantages of each approach.

Answer 11

• > Locators:
  • Values remains in DB, avoids unnecessary transformation/transfer (+)
  • Available for collections (+)
  • Manipulation of values restricted to SQL operations (-)
• > Transforms:
  • Flexible: tailored UDT eexchange, accomodate existing formats (+)
  • additional effort: transform functions, parsing/generation (-)
• > Complex-value transfer:
  • generic representation for dynamic apps (+)
  • allows user-defined mapping; improved integration (+)
  • potential development impact (~)
  • only for java (-)