数据库编程方法

1 .Chapter 13 &14 Intro to SQL Programming Techniques & Web DB + PhP

2 .Chapter 13 Outline Database Programming: Techniques and Issues Database Programming with Function Calls: SQL/CLI and JDBC

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

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

5 .Approaches to Database Programming 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

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

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

8 .Embedded SQL, Dynamic SQL, SQLJ Embedded SQL C language SQLJ Java language Programming language called host language

9 .Retrieving Single Tuples with Embedded SQL EXEC SQL Prefix 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

10 .C Program Segment Embedded SQL

11 .Retrieving Single Tuples with Embedded SQL Connecting to the database CONNECT TO <server name>AS <connection name> AUTHORIZATION <user account name and password> ; Change connection SET CONNECTION <connection name> ; Terminate connection DISCONNECT <connection name> ;

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

13 .Retrieving Single Tuples with Embedded SQL (cont’d.) SQLSTATE String of five characters ‘00000’ = no error or exception Other values indicate various errors or exceptions For example, ‘02000’ indicates ‘no more data’ when using SQLSTATE

14 .Retrieving Single Tuples with Embedded SQL (cont’d.) SQLSTATE String of five characters ‘00000’ = no error or exception Other values indicate various errors or exceptions For example, ‘02000’ indicates ‘no more data’ when using SQLSTATE

15 .Retrieving Multiple Tuples with Embedded SQL Using Cursors Cursor 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

16 .Retrieving Multiple Tuples with Embedded SQL Using Cursors Cursor 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

17 .JDBC: SQL Function Calls for Java Programming JDBC 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

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

19 .Figure 10.12 Program segment JDBC1, a Java program segment with JDBC. Slide 10- 35

20 .Figure 10.13 Program segment JDBC2, a Java program segment that uses JDBC for a query with a collection of tuples in its result. Slide 10- 36

21 .Chapter 14 Outline A Simple PHP Example Overview of Basic Features of PHP Overview of PHP Database Programming

22 .Web Database Programming Using PHP Techniques for programming dynamic features into Web PHP Open source scripting language Interpreters provided free of charge Available on most computer platforms

23 .A Simple PHP Example PHP Open source general-purpose scripting language Comes installed with the UNIX operating system DBMS Bottom-tier database server PHP Middle-tier Web server HTML Client tier

24 .Client Tier Middle-tier Web Server Database Server A Simple PHP Example

25 .Figure 11.1a PHP program segment for entering a greeting. continued on next slide Slide 11- 6

26 .Figure 11.1b-d (b) Initial form displayed by PHP program segment. (c) User enters name John Smith . (d) Form prints welcome message for John Smith . Slide 11- 7

27 .Introduction to PHP From center for communication sciences at RPI. http://www.ccp.rpi.edu/files/2011/04/php-I-slides.ppt

28 .Welcome This slideshow presentation is designed to introduce you to PHP. It is the first of two PHP workshops available at www.tinyurl.com/rpi123 . In addition to the two PHP workshops, there are also workshops on HMTL and CSS. These slides are based on source material found at the w3schools.com website. You are encouraged to visit the site – it is a great resource.

29 .Welcome This slideshow presentation is designed to introduce you to PHP. It is the first of two PHP workshops available at www.tinyurl.com/rpi123 . In addition to the two PHP workshops, there are also workshops on HMTL and CSS. These slides are based on source material found at the w3schools.com website. You are encouraged to visit the site – it is a great resource.