3 - DB-Gateways Flashcards
Explain the embedded SQL coupling approach and its different types
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)
What is the Call-Level-Interface (CLI) approach?
Standard library functions used through an API -> dynamic approach. Allows portability and vendor-independence.
Explain the advantages and disadvantages of CLI and embedded SQL as coupling approaches
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)
What are the requeriments for standard CLIs?
- 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.
What are the requirements imposed by distributed systems on DB-gateways?
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.
Give an overview of the programming model of JDBC.
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
Explain the general JDBC architecture and the DataSource mechanism
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.
What are the goals of a driver manager and the types of driver in ODBC?
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.
Give an overview of the different types of JDBC drivers
(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.
Explain the programming model of SQLJ
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(?)
What are the advantages and disadvantages of SQLJ over JDBC?
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.
How do iterators work in SQLJ?
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))
Explain the concept of binary portability in SQLJ
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))
