CS代写 CHAPTER 10 - PowCoder代写

CHAPTER 10

Introduction to SQL Programming
Techniques
Slide 9- *

Introduction to SQL
Programming Techniques
Database applications
Host language
Java, C/C++/C#, COBOL, or some other programming language
Data sublanguage
SQL standards
Continually evolving
Each DBMS vendor may have some variations from standard

Slide 10- 3

Database Programming: Techniques and Issues
Interactive interface
SQL commands typed directly into a monitor
Execute file of commands
@
Application programs or database applications
Used as canned transactions by the end users access a database
May have Web interface

Slide 10- 4

Approaches to Database Programming
Embedding database commands in a general-purpose programming language
Database statements identified by a special prefix
Precompiler or preprocessor scans the source program code
Identify database statements and extract them for processing by the DBMS
Called embedded SQL

Slide 10- 5

Approaches to Database Programming (cont’d.)
Using a library of database functions
Library of functions available to the host programming language
Application programming interface (API)
Designing a brand-new language
Database programming language designed from scratch
First two approaches are more common

Slide 10- 6

Impedance Mismatch
Differences between database model and programming language model
Binding for each host programming language
Specifies for each attribute type the compatible programming language types
Cursor or iterator variable
Loop over the tuples in a query result

Slide 10- 7

Typical Sequence of Interaction in Database Programming
Open a connection to database server
Interact with database by submitting queries, updates, and other database commands
Terminate or close connection to database

Slide 10- 8

Retrieving Single Tuples with Embedded SQL
Preprocessor separates embedded SQL statements from host language code
Terminated by a matching END-EXEC
Or by a semicolon (;)
Shared variables
Used in both the C program and the embedded SQL statements
Prefixed by a colon (:) in SQL statement

Slide 10- 10

Figure 10.1 C program variables used in the embedded SQL examples E1 and E2.
Slide 10- 11

Retrieving Single Tuples with Embedded SQL (cont’d.)
Connecting to the database

CONNECT TO AS
AUTHORIZATION ;
Change connection

SET CONNECTION ;
Terminate connection

DISCONNECT ;
Slide 10- 12

Retrieving Single Tuples with Embedded SQL (cont’d.)
SQLCODE and SQLSTATE communication variables
Used by DBMS to communicate exception or error conditions
SQLCODE variable
0 = statement executed successfully
100 = no more data available in query result
< 0 = indicates some error has occurred Slide 10- 13 Copyright © 2016 and Shamkant B. Navathe Retrieving Multiple Tuples with Embedded SQL Using Cursors Points to a single tuple (row) from result of query OPEN CURSOR command Fetches query result and sets cursor to a position before first row in result Becomes current row for cursor FETCH commands Moves cursor to next row in result of query Slide 10- 16 Copyright © 2016 and Shamkant B. Navathe SQLJ: Embedding SQL Commands in Java Standard adopted by several vendors for embedding SQL in Java Import several class libraries Default context Uses exceptions for error handling SQLException is used to return errors or exception conditions Slide 10- 21 Copyright © 2016 and Shamkant B. Navathe Figure 10.5 Importing classes needed for including SQLJ in Java programs in Oracle, and establishing a connection and default context. Slide 10- 22 Copyright © 2016 and Shamkant B. Navathe Figure 10.6 Java program variables used in SQLJ examples J1 and J2. Slide 10- 23 Copyright © 2016 and Shamkant B. Navathe Figure 10.7 Program segment J1, a Java program segment with SQLJ. Slide 10- 24 Copyright © 2016 and Shamkant B. Navathe Retrieving Multiple Tuples in SQLJ Using Iterators Object associated with a collection (set or multiset) of records in a query result Named iterator Associated with a query result by listing attribute names and types in query result Positional iterator Lists only attribute types in query result Slide 10- 25 Copyright © 2016 and Shamkant B. Navathe Figure 10.8 Program segment J2A, a Java program segment that uses a named iterator to print employee information in a particular department. Slide 10- 26 Copyright © 2016 and Shamkant B. Navathe Figure 10.9 Program segment J2B, a Java program segment that uses a positional iterator to print employee information in a particular department. Slide 10- 27 Copyright © 2016 and Shamkant B. Navathe Database Programming with Function Calls: SQL/CLI & JDBC Use of function calls Dynamic approach for database programming Library of functions Also known as application programming interface (API) Used to access database SQL Call Level Interface (SQL/CLI) Part of SQL standard Slide 10- 28 Copyright © 2016 and Shamkant B. Navathe SQL/CLI: Using C as the Host Language Environment record Track one or more database connections Set environment information Connection record Keeps track of information needed for a particular database connection Statement record Keeps track of the information needed for one SQL statement Slide 10- 29 Copyright © 2016 and Shamkant B. Navathe SQL/CLI: Using C as the Host Language (cont’d.) Description record Keeps track of information about tuples or parameters Handle to the record C pointer variable makes record accessible to program Slide 10- 30 Copyright © 2016 and Shamkant B. Navathe JDBC: SQL Function Calls for Java Programming Java function libraries Single Java program can connect to several different databases Called data sources accessed by the Java program Class.forName("oracle.jdbc.driver.OracleDriver") Load a JDBC driver explicitly Slide 10- 33 Copyright © 2016 and Shamkant B. Navathe JDBC: SQL Function Calls for Java Programming Connection object Statement object has two subclasses: PreparedStatement and CallableStatement Question mark (?) symbol Represents a statement parameter Determined at runtime ResultSet object Holds results of query Slide 10- 34 Copyright © 2016 and Shamkant B. Navathe Database Stored Procedures and SQL/PSM Stored procedures Program modules stored by the DBMS at the database server Can be functions or procedures SQL/PSM (SQL/Persistent Stored Modules) Extensions to SQL Include general-purpose programming constructs in SQL Slide 10- 37 Copyright © 2016 and Shamkant B. Navathe Figure 10.14 Declaring a function in SQL/PSM. Slide 10- 43 Copyright © 2016 and Shamkant B. Navathe Comparing the Three Approaches Embedded SQL Approach Query text checked for syntax errors and validated against database schema at compile time For complex applications where queries have to be generated at runtime Function call approach more suitable Slide 10- 44 Copyright © 2016 and Shamkant B. Navathe Techniques for database programming Embedded SQL Function call libraries SQL/CLI standard JDBC class library Stored procedures Slide 10- 46 程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts