20110516 obunai 1 Database Programing Techniques and Issues

2011/05/16 obunai 1

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 2

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 3

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 8

SQLとCの型の対応 SQL C INTEGER long SMALLINT short REAL float DOUBLE double CHAR [ i ]（固定長） 配列char [ i + 1 ] VARCHAR [ i ](可変長) 配列varchar [ i + 1] 11

Connecting to the Database データベースに接続を確立するためのSQLコマンド CONNECT TO <sever name> AS <connection name> AUTHORZATION <user account name and password>; コネクションの変更 SET CONNECTION <connection name> ; コネクションの終了 DISCONNECT <connection name> ; 12

Communicating between the Program and the DBMS Using SQLCODE and SQLSTATE 通信変数(communication variables) 1. SQLCODE integer変数。SQLコマンドが実行されたときにDBMSが値を返す 　 SQLCODE = 0 → 正常に実行された 　 SQLCODE > 0 → クエリ結果に有効な値がない 　 SQLCODE < 0 → エラー 2. SQLSTATE 5文字の文字列 ’ 00000’ → エラー、エクセプションなし ’ 02000’ → no more data 13

Example of Embedded SQL Programming 14

General Options for a Cursor Declaration カーソルを宣言するとき、いくつかのオプションが指定できる DECLARE <cursor name> [INSENSITIVE] [SCROLL] CURSOR [WITH HOLD] FOR <query specification> [ORDER BY <ordering specification>] [FOR READ ONLY | FOR UPDATE [ OF <attribute list>] ]; FETCHコマンドに fetch operation を追加することができる FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO <fetch target list>; Ø <fetch operation>は NEXT, PRIOR, FIRST, LAST, ABSOLUTE i, RELATIVE i のどれか 17

Specifying Queries at Runtime Using Dynamic SQL 埋め込みSQLでは簡単にクエリを変更できない 　→dynamic SQLを使う コマンドが1回しか実行されないときはPREPAREと EXCUTEをくっつけて書いた方が便利 EXEC SQL EXECUTE IMMEDIATE : sqlupdatestring 18

SQLJ: Embedding SQL Commands in Java (cont’d) 20

22

23

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 24

0) #include sqlcli. h ; 1) void print. Sal() { 2) SQLHSTMT stmt 1 ; 3) SQLHDBC con 1 ; 4) SQLHENV env 1 ; 5) SQLRETURN ret 1, ret 2, ret 3, ret 4 ; 6) ret 1 = SQLAlloc. Handle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env 1) ; 7) if ( !ret 1 ) ret 2 = SQLAlloc. Handle(SQL_HANDLE_DBC, env 1, con 1) else exit ; 8) if ( !ret 2 ) ret 3 = SQLConnect ( con 1, “dbs”, SQL_NTS, “js”, SQL_NTS, “xyz” SQL_NTS) else exit ; 9) if (! ret 3 ) ret 4 = SQLAlloc. Handle(SQL_HANDLE_STMT, con 1, &stmt 1) else exit ; 10)SQLPrepare (stmt 1, “select Lname, Salary form EMPLOYEE where Ssn = ? ”, SQL_NTS) ; 11)prompt(“Enter a Social Security Number : “, ssn) ; 12)SQLBind. Parameter(stmt 1, 1, SLQ_CHAR, &ssn, 9, &fetchlen 1) ; 13)ret 1 = SQLExcute(stmt 11) ; 14)if ( !ret 1 ) { 15) SQLBind. Col(stmt 1, 1, SQL_CHAR, &lname, 15, &fetchlen 1) ; 16) SQLBind. Col(stmt 1, 2, SQL_FLOAT, &salary, 4, &fetchlen 2) ; 17) ret 2 = SQLFetch(stmt 1) ; 18) if ( !ret 2 ) printf(ssn, lanme, salary) 19) 20) 21) } else printf( „Social Security Number does not exist: „, ssn); } 28

29

31

0) import java. io. * ; 1)import java. sql. * ; … 2) class print. Department. Emps { 3) public static void main (String args[]) throws SQLException, IOException { 4) 　　　　　try { Class. for. Name ( “pracle. jdbc. driver. Oracle. Driver”) 5) } 6) catch (Class. Not. Found. Excepton x) { System. out. println (“Driver could not be loaded”) ; 7) } 8) String dbacct, passwrd, lanme ; 9) Double salary ; 10) Integer dno ; 11) dbacct = readentry(“Enter database account : ”) ; 12) passwrd = readentry (“Enter password: “) ; 13) Connection conn = Driver. Manager. get. Connection 14) (“jdbc : oracle : oci 8 : ” + dbacct + “/” + passwrd) ; 15) dno = readentry(“Enter a Department Number : “) ; 16) String q = “select Lname, Salary from EMPLOYEE where Dno = “ +dno. tostring(); 17) Statement s = conn. create. Statement() ; 18) Result r = s. execute. Query(q) ; 19) while (r. next()) { 20) lname = r. get. String(1); 21) salary = r. get. Double(2); 22) 23) 24) } system. out. printline(lname + salary); } } 32

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 33

Database Stored Procedures and SQL/PSM ストアドプロシージャの宣言 CREATE PROCEDURE <procedure name> (<parameter>) <local declarations> <procedure body>; 関数の宣言 CREATE FUNCTION <procedure name> (<parameter>) RETURNS <return type> <local declarations> <function body>; 35

Database Stored Procedures and SQL/PSM パラメータはSQLのデータ型の一つであるparameter型をもつ また、parameter modeをもつ Ø IN, OUT, INOUTのどれか　 　　　 それぞれの値はinput only, output only, both input and output v SQL標準のCALL statementでストアドプロシージャや　　　関 数を呼び出せる CALL <procedure or function name>( <argument list>); 36

SQL/PMS: Extending SQL for Specifying Persistent Stored Modules SQL/PMS Ø persistent stored modulesの書き方を指定する　　　　　SQL 標準の一部 Ø conditional statement と looping statement conditional statement IF <condition> THEN <statement list> ELSEIF <condition> THEN <statement list> … ELSEIF <condition> THEN <statement list> ELSEIF <statement list> END IF ; 37

SQL/PMS: Extending SQL for Specifying Persistent Stored Modules looping structure WHILE <condition> DO <statement list> END WHILE ; REPEAT <statement list> UNTIL <condition> ENDREPEAT; 38

SQL/PMS: Extending SQL for Specifying Persistent Stored Modules 39

本日の予定 Database Programing : Techniques and Issues Embedded SQL, Dynamic SQL, and SQLJ Database Programing with Function Calls : SQL/CLI and JDBC Database Stored Procedures and SQL/PSM Comparing the Three Approaches 40