3 - DB-Gateways

Question 1

Explain the embedded SQL coupling approach and its different types

Answer 1

SQL queries are embedded and integrated into standard programming language.

Preprocessor translates SQL to calls of specific library (early query compilation, vendor-specific)

Static versus dynamic (character sequences can be interpreted as queries)

Question 2

What is the Call-Level-Interface (CLI) approach?

Answer 2

Standard library functions used through an API -> dynamic approach. Allows portability and vendor-independence.

Question 3

Explain the advantages and disadvantages of CLI and embedded SQL as coupling approaches

Answer 3

Embedded: nice integration with prog. lang. (syntax, variables), but vendor-specific. Static emb. can perform early compilation (check, optimization).

CLI: vendor-independence and dynamic queries (also in dynamic. emb.), but late compilation (optimization not possible, cannot check errors) and not so straight-forward integration with prog. language.

• Both approaches: mismatch set-processing versus tuple-based (cursor)

Question 4

What are the requeriments for standard CLIs?

Answer 4

• Uniform database access: SQL, metadata information, API.
• Portability: no vendor-specific preprocessor, portable application binaries
• Dynamic, late binding to DB (flexibility versus performance), but queries can be prepared once and just executed fast further.

Question 5

What are the requirements imposed by distributed systems on DB-gateways?

Answer 5

Support for remote access

Access multiple DBs through separate (simultaneous) heterogeneous DB connections (driver manager).
- - Access the same or different DBs, within the same distributed transaction.

Support vendor-specific extensions.

Question 6

Give an overview of the programming model of JDBC.

Answer 6

Standard Java CLI, derived from ODBC.
- Abstraction layer between Java applications and SQL

• Typical interaction:
• • Get connection object from DriverManager
• • Retrieve statement object from connection object (prepared or normal)
• • Call executeQuery to retrieve a ResultSet (or executeUpdate)
• • Iterate over ResultSet, using getters to read results (many kinds of ResultSets)

Additional: transaction (for single connection): implicit begin metadata lookup

Question 7

Explain the general JDBC architecture and the DataSource mechanism

Answer 7

DataSource: alternative 3, abstract vendor-specific details of the connection (URL)

• Refers to the DB using a logical name registered with JNDI (Can be reassigned to several physical DBs; no change in code)
• Support for connection pooling and distributed transactions

Architecture: application -> driver manager -> (vendor) driver
- Driver processes CLI calls and redirects SQL statements to DBMS. Hides heterogeneity of data source (DB, flat file, etc…) manages connection, error handling, data type mapping.

Question 8

What are the goals of a driver manager and the types of driver in ODBC?

Answer 8

Manages indirection between application and vendor dirver (n:m mapping)

1-tier driver: complete DB functionality on driver (flat files/desktop DBs): no multi-user / transaction.

2-tier driver: client/server model

• • Direct data protocol: message / RPC-based
• • Mapping ODBC to DB-client API: DBs runtime library does the communication
• • Middleware solution: generic driver approach that handles communication on both (middleware-vendor) side (no vendor specifies of client side)

3-tier driver: middleware server: relays requests to one or more DBMSs.

Question 9

Give an overview of the different types of JDBC drivers

Answer 9

(Partial Java)

• Type 1: mapping JDBC-ODBC (JNI) 2-tier
• Type 2: native API: converts JDBC into vendor-specific calls (requires binaries on client) 2-tier

(All (pure) Java)

• Type 3: middle-tier, uses a middleware component (JDBC server) that maps to other drivers. 3-tiers. Ideal for web IS (client processing
• Type 4: native java driver implementing network protocol of DB (vendor-specific): 2-tier.

Question 10

Explain the programming model of SQLJ

Answer 10

SQL code embedded in JAva, generates JDBC with preprocessor. Translator (profile) + customizer (vendor DB; uses java introspection). Bin-portability.

Context object is referred in the SQL code (contains connection)

Input and results are placed in Java variables

• Combines advantages of embedded with CLI (portability)
• Static authorization(?)

Question 11

What are the advantages and disadvantages of SQLJ over JDBC?

Answer 11

SQLJ

• Embedded code is more concise
• Static approach allows early checks
• Static authorization is faster and safer for enterprise (auth. based on program, users are granted EXEC)
• Customizer step allows for vendor-specific optimization
• Incorporates portability of JDBC

JDBC (DB-gateway)

• vendor-independence
• high-accptance
• no big advantages over SQLJ, since it is also based on JDBC.

Question 12

How do iterators work in SQLJ?

Answer 12

Access to rows returned by query (~cursor)

Specific iterator class generated by precomp. (strongly typed)

Named iterator: gives names to iterator members (columns)

• Automatic mapping form name of columns of result table (allows any query with applicable names)
• Methods with the same name to access values on iterator (position does not matter)

Positioned iterator: name does not matter, just types and positions are used

• Requires FETCH INTO for accessing values (to appropriate variables)
• Better performance (no string comparisons (names))

Question 13

Explain the concept of binary portability in SQLJ

Answer 13

Addition to source code portability (explores portability of Java)

Uses generic precompiler: Java + JDBC

Profile is generic and accessible to any customer (enhances the program with vendor-specific stubs (invocation of access routine))