[Database] Database Application Development | 데이터베이스 응용 개발 (6장)

Database Application Development

데이터베이스 응용 개발

- DB APP은 SQL과 PL이 연동되어 구현된다.
  (SQL을 통해 Data를 추출하여 PL을 통해 처리한다.)

---

SQL in Application Code

- PL에서 SQL을 호출하는 방식이다.
- SQL은 Application Code의 Host Variable을 참조할 수 있다.
- SQL을 통해 DB와 Connection할 수 있게하는 Statement가 존재해야 한다.

* Main Integration Approaches (SQL과 PL을 통합하는 두 가지 방법)
1) Embedded SQL
- Host PL에 SQL을 내장하는 방식이다.
ex) Embedded SQL, SQLJ, SQL/PSM

2) Special API
- 특수한 API를 통해 SQL을 호출하는 방식이다.
- Embedded SQL 방식의 단점을 보완하였다.
ex) JDBC, ODBC

---

Embedded SQL

- Host PL에 SQL을 내장하는 방식이다.
ex) Embedded SQL/C : C언어에 SQL을 내장한 언어이다.
- 내장된 SQL 코드는 Preprocessor가 처리한다. (Compiler가 처리하지 못한다.)

---

내장된 SQL로 인해 추가된 명령어들
- Embedded SQL 방식에서는 항상, Prefix로 \(\texttt{EXEC SQL}\) 이 붙는다.
- 이 명령어들은 Compiler가 아닌, Preprocessor가 Machine Code로 Conversion한다.

1) Connecting To DB

EXEC SQL CONNECT

2) Declaring Variables

EXEC SQL BEGIN DECLARE SECTION
...
// Host PL의 Variable을 참조하는 SQL 코드가 위치하는 부분
...
EXEC SQL END DECLARE SECTION

- Embedded SQL은 Host PL의 Variable을 참조할 수 있으며,
  참조하고자 하는 경우, 위 BEGIN, END Block 사이에 위치시켜야 한다.

※ SQL에서 사용되는 Host PL의 Variable은 Prefix로 Colon(:)이 붙는다.

3) Statements

EXEC SQL Statement

- 이 때, Statement는 SQL Code를 의미한다.
- 많은 Record를 다루는 SQL 명령을 C코드로 옮겨와 Loop를 통해 작업하면 보다 수월하게 Query를 진행할 수 있다.

Example. Embedded SQL/C

EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION

EXEC SQL
INSERT INTO Sailors VALUES( :c_sname, :c_sid, :c_rating, :c_age );

---

Exception Handling in Embedded SQL

- Error를 처리하기 위한, 두 개의 Special Variable SQLCODE와 SQLSTATE가 존재한다.

1) SQLCODE (Old Ver.)
- C의 long Type 변수이다.
- 에러 조건을 음숫값으로 표현한다.

2) SQLSTATE (SQL-92 Standard)
- C의 char[6] Type 변수이다.
  (마지막 character(char[5])는 NULL값으로 고정되므로, 사실상 5개의 Character 변수이다.)
- 빈번히 발생하는 에러들이 미리 정의되어 있다.

---

Impedance Mismatch

- SQL을 DB의 DBMS에 전달하여, DB의 데이터를 DB Application의 Data Structure에 저장한다.
- 이 때, SQL 질의 결과가 되는 Relation의 크기를 예측할 수 없어,
  DB Application의 Data Structure의 크기를 적절히 결정할 수 없는 문제가 발생한다.
  (이러한 문제를 Impedance Mismatch라 표현한다.)
- Impedance Mismatch는 SQL을 내장할 때의 가장 주요한 문제점이다.
- SQL에서는 Impedance Mismatch 문제를 해결하기 위한 메커니즘 중 하나로 Cursor가 있다.

---

Cursors

- 어떤 Relation이나 SQL 문장에 Cursor를 선언할 수 있다.
  (SQL의 결과 또한 Relation임을 상기하자.)
- SQL의 질의 결과의 크기를 예측할 수 없어, PL의 Data Structure의 크기를
  특정지을 수 없는 문제(Impedance Mismatch)를 해결하기 위해 고안된 개념이다.
  (Cursor는 SQLSTATE와 연동되어 질의결과의 끝을 판별한다.)
- Cursor가 가리키는 Record를 검색, 삽입, 수정, 삭제할 수 있다.
  (즉, SQL을 통한 조작을 Cursor를 통해서도 수행 가능하다.)

* Record Fetch Process using Cursor (SQL \(\to\) PL's Data Structure)
1) Cursor가 Open(Declare)되면, SQL 질의 결과 Relation의 첫 번째 Record를 가리키게 된다.
2) Cursor가 가리키고 있는 Record를 Fetch하여, 참조한 C코드의 변수에 저장한다.
3) Cursor는 Fetch 후 Move하여 다음 Record를 가리키게 된다.
4) 위 과정을 반복하며, Cursor가 마지막 Record까지 Fetch한 후,
  "No Data Found"(Error Code = '02000') Event가 발생하여 생긴 Error Code를 SQLSTATE에 저장한다.
5) SQL 결과 Relation의 크기를 모르므로, Loop를 사용할 수 없고,
   SQLSTATE Error Code를 통해, Fetch가 끝났음을 확인할 수 있다.
   (SQLSTATE 값이 '02000'("No Data Found"를 의미하는 Error Code)인지를 확인한다.)

Example. Cursor

EXEC SQL DECLARE sinfo CURSOR FOR
    SELECT S.sname
    FROM Sailors S, Boats B, Reserves R
    WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
    ORDER BY S.sname

- Cursor \(\texttt{sinfo}\)를 통해, 빨간 색상 배를 예약한 선원의 이름을 알파벳순으로 구하는 질의를 수행한다.

---

Select Command using Cursor

char SQLSTATE[6];

EXEC SQL BEGIN DECLARE SECTION
char c_sname[20]; short c_minrating; float c_age;   // 질의 결과를 저장할 변수들을 선언한다.
EXEC SQL END DECLARE SECTION

c_minrating = random();  // 임의의 등급으로 초기화한다.

EXEC SQL DECLARE sinfo CURSOR FOR
    SELECT S.sname, S.age
    FROM Sailors S
    WHERE S.rating > :c_minrating    // SQL에서 C변수를 사용할 때에는 앞에 콜론(:)을 붙인다.
    ORDER BY S.sname;
    
EXEC SQL OPEN sinfo; // Cursor Open = 질의 결과 테이블의 첫 번째 레코드를 Cursor가 Point한다.
do {
    EXEC SQL FETCH sinfo INTO :c_sname, :c_age; // Cursor가 질의 결과 레코드를 Fetch하고, 다음 레코드로 Move한다.
    printf("%s is %d years old\n", c_sname, c_age);
} while(SQLSTATE != '02000'); // 질의 결과의 끝에 도달할 때 까지 반복한다.

EXEC SQL CLOSE sinfo; // Cursor를 Close한다.

---

Updae Command using Cursor

UPDATE Sailors S
SET S.rating = S.rating + 1
WHERE CURRENT of sinfo;

// sinfo Cursor가 현재 가리키고 있는 레코드에 대해 Update 질의를 수행한다.

---

Delete Command using Cursor

DELETE Sailors S
FROM CURRENT of sinfo;

// sinfo Cursor가 현재 가리키고 있는 레코드에 대해 Delete 질의를 수행한다.

---

Insenstive Cursor (Protecting Against Concurrent Updates)

- 질의 결과 레코드에 대한 "복사본"을 가리키는 Cursor를 의미한다.
- 원본 DB를 Protection할 수 있다.

Example. Insenstive Cursor

EXEC SQL DECLARE sinfo INSENSTIVE CURSOR FOR
    SELECT S.sname
    FROM Sailors S, Boats B, Reserves R
    WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
    ORDER BY S.sname;

---

Scroll Cursor

- Scrolling이 가능한 Cursor를 의미한다.
- 일반 Cursor와 달리, 질의 결과 테이블의 상하좌우로 움직일 수 있다.
  (일반 Cursor는 질의 결과의 아래 방향으로만 진행된다.)

FETCH NEXT
- 현재 Cursor가 가리키는 레코드의 다음 레코드로 이동한다.

FETCH PRIOR
- 현재 Cursor가 가리키는 레코드의 이전 레코드로 이동한다.

FETCH FIRST
- Cursor가 연동되어 있는 질의 결과 테이블의 첫 번째 레코드로 이동한다.

FETCH LAST
- Cursor가 연동되어 있는 질의 결과 테이블의 마지막 레코드로 이동한다.

FETCH RELATIVE n
- 현재 Cursor의 위치에서 n만큼 다음에 있는 레코드로 이동한다. (상대위치)
ex) FETCH RELATIVE 3 : 현재 Cursor가 가리키는 레코드에서 3만큼 다음에 있는 레코드로 이동한다.
ex) FETCH RELATIVE -3 : 현재 Cursor가 가리키는 레코드에서 3만큼 "이전"에 있는 레코드로 이동한다.

FETCH ABSOLUTE n
- 질의 결과 테이블에서 n번째의 레코드로 이동한다. (절대위치, 인덱스)
ex) FETCH ABSOLUTE 1 \(\iff\) FETCH FIRST
ex) FETCH ABSOLUTE -1 \iff FETCH LAST

Example. Scroll Cursor

EXEC SQL DECLARE sinfo SCROLL CURSOR FOR
    SELECT S.sname
    FROM Sailors S, Boats B, Reserves R
    WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
    ORDER BY S.sname;

---

Read-Only Cursor

- 질의 결과를 오로지 읽기(조회)만 가능한 형태의 Cursor이다.

Example. Read-Only Cursor

EXEC SQL DECLARE sinfo CURSOR FOR
    SELECT S.sname
    FROM Sailors S, Boats B, Reserves R
    WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
    ORDER BY S.sname
    FOR READ ONLY;

---

Dynamic SQL

- 실행시간 중에 SQL Query가 결정된다.
ex) Spreadsheet, Graphical DBMS Frontend

* Static SQL
- 컴파일 시, SQL Query가 결정된다.

Example. Dynamic SQL

char c_sqlstring[] = {"DELETE FROM Sailors WHERE rating > 5"};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
// SQL System이 c_sqlstring을 질의로 인식하고, 이에 readytogo라는 변수명을 부여하여 질의를 실행할 준비를 할 것을 지시한다.
EXEC SQL EXECUTE readytogo;
// readytogo 질의를 수행한다.

---

Limitation of Embedded SQL/C

- C에 내장된 SQL은 Preprocessor에 의해 Database에 연계된 API Call로 변환되고,
  해당 API Call은 다시금, DBMS에 특정된 Native API Call로 변환된다.
- DBMS별로 API가 제각기 다르기 때문에 Preprocessor가 해당 DBMS의 종류를 알고있어야만
  SQL Code를 DBMS API로 변환할 수 있다.
- 이 DBMS API는 다시 DBMS에 특정된 Native API(Local API)로 변환되어야 한다.
- 즉, Embedded SQL 방식에서는 DBMS에 의존적이다.
  (DBMS Specific한 Conversion을 두 번 거쳐야 한다.)

※ 이에 대한 Solution으로, C Code에 SQL을 내장하는 다른 방법 중 하나인 DB API(ODBC, JDBC)를 이용하는 방법이 있다.
* ODBC : Open Database Connectivity
* JDBC : Java Database Connectivity
- 이들은 Special한 Standard DB API이다.
- JDBC와 ODBC는 서로를 지원한다.

---

JDBC (Java Database Connectivity)

- Special DB API이자 Standard DB API이다.
- JDBC는 SQL을 JDBC API(표준)로 변환해준다.
  이후, JDBC API는 DBMS에 특정한 API로 변환된다.
- 즉, SQL이 표준 API로 변환되기 때문에 DB의 종류에 의존될 필요가 없다.
  (마지막 단계에서만 DBMS에 특정된 Local API로 변환되면 된다.)
- DBMS가 관리하는 DB에 접근하기 위해, Java Class들과 Java Interface를 이용한다.

* JDBC Methods
- \(\texttt{java.sql}\) Package에는 아래와 같은 JDBC API들을 제공하고 있다.

1) Connecting to a Remote Data Source
2) Executing SQL Statements
- SQL을 실행하는 함수가 제공된다.
3) Receiving SQL Results
- SQL 질의 결과를 받는 함수가 제공된다.
4) Transaction Management
- 트랜잭션을 관리하는 함수가 제공된다.
5) Exception Handling
- 예외를 처리하는 함수가 제공된다.

---

Advantage of API Approach

- Source Code Level에서 DBMS에 독립적이고, Execution Level에서도 DBMS에 독립적이다.
  (JDBC가 Application과 DB사이에 Layer를 제공하기 때문이다. DBMS는 해당 API의 Driver만 갖추고 있으면 된다.)
- DBMS에서는 JDBC Driver를 통해 JDBC API에 접근한다.
- JDBC는 JDBC API Call을 DBMS Specific Local Call로 변환한다.
- 반면, Preprocessor는 SQL Code를 곧바로 DBMS에 Specific한 API Call로 변환한다.

 

---

JDBC Architecture

1) Application
- DB와의 연결을 시작, 종결하고, SQL 질의를 수행하는 주체이다.

2) JDBC Driver Manager
- JDBC Driver Manager는 Application으로부터의 JDBC API Call을 해당하는 DBMS의 JDBC Driver와 연결한다.
- 즉, JDBC Driver Manager를 통해 여러 종류의 DBMS를 한 번에 다룰 수 있다.

3) JDBC Driver
- JDBC Driver는 JDBC API Call을 해당 DBMS의 Local Call로 변환하여 Data Source와 연결한다.
- SQL 요청을 받아 Data source에 전달하고, Data Source로부터 결과를 받으며, Error Code를 Handling한다.

4) Data Source
- SQL 질의를 처리하는 DBMS와 DB를 의미하는 부분이다.

---

JDBC Driver Types

1) Type1: Bridge

- Bridge는 JDBC Call을 ODBC Call로 변환하거나, ODBC Call을 JDBC Call로 변환한다.
- DBMS는 ODBC Driver와 JDBC Driver를 모두 지원하는 것이 바람직하나,
  그렇지 않고, 어느 한 쪽만 지원하고 있는 경우에 해결책이 바로 Bridge이다.
- 위 그림은 JDBC-ODBC Bridge를 표현한 그림이다.
- 새로운 드라이버를 설치할 필요가 없다.
- 부차적인 Layer가 추가되는 형태이기 때문에, Performance가 저해된다.
- Client-Side에 DBMS가 지원하는 Driver가 설치되어 있어야 한다.
- Web Service에는 부적합한 형태이다.


2) Type2: Non-Java Driver

- JDBC API Call을 DBMS의 Local API(Native API)로 바로 변환한다.
- 곧바로 Local API로 변환되기 때문에, 보다 나은 Performance를 보여준다.
- Client-Side에 DBMS에 대한 Native API가 설치되어 있어야한다.
- Web Service에는 부적합한 형태이다.

3) Type3: Network Bridge

* 3-Tier : Client - Middleware Server - DB Server

- 모든 Driver들이 네트워크를 통해 Middleware 서버에 명령을 전송하여 처리하는 형태이다.
- Middleware Server는 JDBC 요청을 DB-Specific Call로 변환한다.
- Client-Side에서는 보다 가벼운 JDBC Driver만 구성하면 된다.
- 3-Tier 구조이기 때문에 Middleware Server에 대한 추가적인 관리가 필요하다.


4) Type 4: Direct Translation to the Native API via Java Driver

- 모두 Java로 구현되어 있다.
- 우수한 성능을 보인다.
- 대부분의 Internet Access에 적합하다.
- DB의 종류마다 자체적인 Driver를 필요로한다.

---

JDBC Driver Management

* DB Query Submit Process
1) Load the JDBC Driver
2) Connect to the Data Source
3) Execute SQL Statements

* Driver Manager Class
- 현재 Load된 Driver들의 List들을 관리하는 메서드를 가진 Java 클래스이다.
- 또한, DBMS의 Driver들을 추가, 삭저할 수 있는 메서드를 가졌다.

* Two Ways for JDBC Driver Loading
1) Java Code에서 로딩하는 방법

Class.forName("oracle/jdbc.driver.Oracledriver");

2) Java Application을 시작시킨 후, Command Line으로 시작하는 방법

Djdbc.drivers=oracle/jdbc.driver

---

Connections in JDBC

- Connection Object가 생성되어 시작된 Session을 통해, Data Source와 상호작용한다.
- 각각의 Connection은 Data Source와 Logical Session을 식별한다.
- Connection은 JDBC Protocol을 사용한 URL로 구분지어진다.

jdbc:<subprotocol>:otherParameters>

Example.

String url="jdbc:oracle:www.bookstore.com:3083";  // Data Source
Connection con;  // Connection Class의 Object인 con
try {
    con = DriverManager.getConnection(url, userId, password);  // url이 가리키는 Data Source와 con을 연결한다.
} catch(SQLException excpt) { ... }

---

Connection Class API

void setTransactionIsolation(int level)
// Sets isolation level for the current connection

public int getTransactionIsolation()
// Get isolation level of the current connection

void setReadOnly(boolean b)
//Specifies whether transactions are read-only

public boolean getReadOnly()
// Tests if transaction mode is read-only

void setQutoCommit(boolean b)
// If autocommit is set, then each SQL statement is considered its own transaction. 
// Otherwise, a transaction is committed using commit(), or aborted using rollback().

public boolean getAutoCommit()
// Test if autocommit is set

public boolean isClosed()
// Checks whether connection is still open.

connectionname.close()
// Close the connection connectionname

---

Executing SQL Statements

- JDBC를 통해서 3가지 방법으로 SQL Statement들을 실행할 수 있다.

1) Statement Class
- Static SQL Statement 혹은 Dynamic SQL Statement를 실행할 수 있다.

Example. Execute SQL Statement

Statement stmt = con.createStatement();
String query = "SELECT name, rating FROM Sailors";
ResultSet rs = stmt.executeQuery(query);

- query는 Static하게 생성될 수도 있고, Dynamic하게 생성될 수도 있다.

2) PreparedStatement Class
- Semi-Static SQL Statement를 실행할 수 있다.
- SQL Structure는 Compile Time에 결정되고, Parameter들의 값은 Run-Time에 결정된다.

Example. PreparedStatement

String sql = "INSERT INTO Sailors VALUES(?, ?, ?, ?)";  // ? : Placeholder
PreparedStatement pstmt = con.prepareStatement(sql);    // PreparedStatement Class의 Object인 pstmt
pstmt.clearParameters();   // Parameter 값들을 Clear 시킨다.
pstmt.setint(1, sid);      // 1번째 Placeholder는 정수형 변수 sid로 설정한다.
pstmt.setString(2, sname); // 2번째 Placeholder는 문자열형 변수 sname로 설정한다.
pstmt.setInt(3, rating);   // 3번째 Placeholder는 정수형 변수 rating로 설정한다.
pstmt.setFloat(4, age);    // 4번째 Placeholder는 실수형 변수 age로 설정한다.

int numRows = pstmt.executeUpdate();
// executeUpdate() : 관련된 레코드의 개수를 리턴한다.

ResultSet rs = pstmt.executeQuery(sql);
// executeQuery() : 쿼리의 결과를 가리키는 Cursor를 리턴한다.
// JDBC에서는 Cursor를 ResultSet Object로 표현한다.
// rs는 쿼리 결과의 첫 번째 레코드의 이전에 위치한다.

While (rs.next()) {
    // process the data
}
// 처음 next()가 이루어지면, 첫 번째 레코드를 가리키게 된다.
// next()는 더 이상 가리킬 쿼리가 없을 때(No data found) false를 리턴한다.

* \(\texttt{ResultSet}\) Methods

Methods	Description
next()	Move to next row
previous()	Moves back one row
absolute(int num)	Moves to the row with the specified number
relative(int num)	Moves forward or backward (if negative)
first()	Moves to the first row
last()	Moves to the last row
getString(string columnName)	Retrieves the value of designated column(field) in current row (pointed row by cursor)
getString(int columnIndex)	Retrieves the value of designated column(field) in current row (pointed row by cursor)
getFloat(string columnName)	Retrieves the value of designated column(field) in current row (pointed row by cursor)

* SQL Types - Java Types

SQL Type	Java Class (Java Type)	get Method of ResultSet
BIT	Boolean	getBoolean()
CHAR	String	getString()
VARCHAR	String	getString()
DOUBLE	Double	getDouble()
FLOAT	Double	getDouble()
INTEGER	Integer	getInt()
REAL	Double	getFloat()
DATE	java.sql.Date	getDate()
TIME	java.sql.Time	getTime()
TIMESTAMP	java.sql.TimeStamp	getTimestamp()

3) CallableStatement Class
- Stored Procedure*를 사용할 수 있다.

* Stored Procedure : SQL Statement가 Compile된 Obejct File

---

JDBC Exceptions

- \(\texttt{java.sql}\)에서 제공하는 대부분의 Method들에서 발생되는 Exception의 Type은 \(\texttt{SQLException}\)이다.
- \(\texttt{SQLException}\)은 \(\texttt{Throwable}\) Class*이다.

* \(\texttt{Throwable}\) Class in Java
- JDBC Code에서 Error 혹은 Exception이 발생되면, 해당 Throwable Object가 이를 Cathcing하여, Handling한다.
- Throwable Class는 Java에서의 모든 Error와 Exception들의 Superclass이다.
- getMessage() Method를 통해 Throwable Object에 전달된 Error Message를 출력시킬 수 있다.

\(\texttt{SQLException}\) Methods

Methods	Description
\(\texttt{public String getMessage()}\)	Inherited from the \(\texttt{Throwable}\) Class
\(\texttt{publoc String getSQLState()]\)	Returns an SQLState identifier according to SQL 99 * identifier : 5개의 숫자코드로 구성된 에러코드
\(\texttt{public int getErrorCode()}\)	Retrieves a vendor-specific error code
\(\texttt{public SQLException getNextException()}\)	Gets the next exception chained to this SQLException object

Catch the Exception

try {
    body-code // Java code and SQL code
} catch (exception-className variableName) {
    handler-code
}

- body-code에서 Exception이 발생되면, variableName 객체에 전달되어 handler-code가 수행된다.

Exammple. Exception Handling

Connection con = DriverManager.getConnection(url, ”login", ”pass"); 
Statement stmt = con.createStatement();       // create and execute a query
String query = "SELECT name, rating FROM Sailors";
ResultSet rs = stmt.executeQuery(query);
try { 					
    while (rs.next() { // loop through result tuples
        String s = rs.getString(“name");    // get the attribute values
        Int n = rs.getInt(“rating");
        System.out.println(s + "   " + n);	// print name and rating
    }
} catch(SQLException  ex) { // handle exceptions
    System.out.println(ex.getMessage () + ex.getSQLState () + ex.getErrorCode ());
}

---

JDBC Warnings

- SQLWarning은 SQLException의 Subclass이다.
- Warning은 Throw되지 않는다.

\(\texttt{SQLWarning}\) Methods

Methods	Description
getWarnings()	Retrieves SQL warning if they exist
getNextWarninig()	Retrieves the warning chained  to this SQLwarning object

Example. Warning and Exception

try {
   stmt=con.createStatement();  // create an empty statement object
   warning=con.getWarnings();   // retrieve warning if it exists
   while(warning != null) {     // warning이 존재하는 경우, while Loop를 수행
      // handle SQLWarnings;
      warning = warning.getNextWarning():  
      // get next warning chained to the warning object
   }
   con.clearWarnings();
   stmt.executeUpdate(queryString);
   warning = con.getWarnings();
   …
} //end try
catch( SQLException SQLe) {     // catch the SQLException object SQLe
   // handle the exception
}

---

Database Metadata

- DB System과 Catalog에 대한 데이터, Schema에 대한 데이터를 의미한다. (데이터를 위한 데이터)
- 예를 들어, 테이블명, Column명, Cardinality, Column Data Type 등이 Metadata에 해당된다.
- JDBC에서는 \(\texttt{DatabaseMetaData}\) Class가 제공된다.

Example. DatabaseMetaData Object

DatabaseMetaData md = con.getMetaData();  // con : Connection Object
// print information about the driver:
System.out.println(
    “Name:” + md.getDriverName() +
    “version: ” + md.getDriverVersion());

\(\texttt{DatabaseMetaData}\) Methods

Methods	Description
getChtalogs()	Retrieves catalog names available in this database
getIndexInfo()	Retrieves a description of the indexes and statistics for the given table
getTables()	Retrieves a description of the tables available in the given catalog
GetColumns()	Retrieves a description of table columns available in the specified catalog
getPrimaryKeys()	Retrieves a description of the given table's primary key columns

* Catalog : Metadata에 대한 정보가 있는 DB Dictionary

Example. DatabaseMetaData Object

DatabaseMetaData md=con.getMetaData();
ResultSet  trs=md.getTables(null,null,null,null);  // MetaData또한 데이터이므로, Cursor와 연계될 수 있다.
String  tableName;

while(trs.next()) {  // for each table, do …
    tableName = trs.getString(“TABLE_NAME”);  // get TABLE_NAME field
    System.out.println(“Table: “ + tableName);
    ResultSet crs = md.getColumns(null,null,tableName,null);  // get all attributes of tableName
    
    while (crs.next()) {
        System.out.println(crs.getString(“COLUMN_NAME”) + “, “);
    }
}

---

SQLJ (SQL-Java)

- JDBC Semi-Static Query Model을 보완한 모델이다.
- SQLJ는 SQL 표준으로, Portability가 우수하다.
  (Vendor Specific한 JDBC와 Embedded SQL과 대비적이다.)

Example. JDBC

sid=rs.getInt(1);           // get value of first attribute, i.e., sid
if (sid==1) {
    sname1=rs.getString(2); // name can be assigned to
}
else {
    sname2=rs.getString(2); // different variable
}

- JDBC에서는 Column명과 변수명이 다를 수 있다.

Example. SQLJ

#sql sailors = {
    SELECT name, rating INTO :name, :rating  // name is bound
    FROM Sailors WHERE sid = :sid;           // to :name
}

- SQLJ에서는 Column명과 변수명이 같아야 한다.
- 단, SQLJ에서 변수의 이름 앞에는 콜론(:)이 붙는다.

SQLJ Precompiler
- SQLJ Procompiler는 SQLJ Application을 Pro-Processed한다.
- SQLJ Translation Program은 Embedded된 SQLJ Code를 SQLJ Java Library API로 변환한다.
- SQLJ Java Library는 JDBC Call과 마찬가지로, JDBC Driver를 Call한다.
- 최종적으로, 변경된 Program Code는 Java Compiler에 의해 Compile된다.

SQLJ Syntax
- 모든 SQLJ Statement 앞에는 Prefix: \(\texttt{#sql}\) 가 붙는다.
- SQL 질의 결과를 검색할 때, Cursor의 개념으로, \(\texttt{iterator}\) Object를 사용한다.

* Cursor의 이름

Embedded SQL	\(\texttt{Cursor}\)
JDBC	\(\texttt{ResultSet}\) Obeject
SQLJ	\(\texttt{iterator}\) Object

* Usage of \(\texttt{iterator}\)
1) \(\texttt{iterator}\) Class 선언

#sql iterator Sailors (int sid, string name, int rating);

2) 새로운 \(\texttt{iterator}\) Class로부터 \(\texttt{iterator}\) Object를 Instantiate

Sailors sailors;

3) SQL Statement를 이용하여 \(\texttt{iterator}\)를 초기화

#sql sailors = {SELECT ... FROM ... WHERE ...}

4) \(\texttt{iterator}\) Obejct를 통하여 레코드를 조회

while (sailors.next()) { // process row }

Example. SQLJ

// Java Variables
Int sid;
String name;
Int rating;

// (1) declare the iterator class
#sql iterator Sailors(Int sid, String name, Int rating);

// (2) intantiate an iterator object
Sailors sailors;    

// assume that the application sets rating

// (3) initialize iterator
#sql  sailors = {
    SELECT sid, sname INTO :sid, :name
    FROM Sailors
    WHERE rating = :rating  };   

// (4) retrieve rows from iterator object
while (sailors.next()) {             
    System.out.println(sailors.sid + “ “ + sailors.sname));
}

// (5) close the iterator object
sailors.close();

Named iterator
- Variable은 Type과 Variable의 이름을 이용하는 방식의 iterator이다.
 
Positional iterator
- 오직, Variable의 Type만을 이용하는 방식의 iterator이다.
- FETCH ... INTO 구조를 이용한다.

Example. Positional iterator

#sql iterator Sailors(Int, String, Int);       // Positional iterator
Sailors  sailors;
#sql sailors = { SELECT … FROM … WHERE … };
while (true) {
    #sql { FETCH :sailors INTO :sid, :name };  // fetch next sailor
    if (sailors.endFetch()) { break; }         // exit loop if end of iterator
    // process the sailor
}

---

Stored Procedures

1) Client/Server Concept
- Client와 Server가 네트워크로 연결되어 RPC를 통해 송수신하는 구조이다.

2) Embedded SQL Concept
- Client의 SQL 질의를 DB Server가 처리하여, Cursor로 리턴하는 구조이다.

3) Stored Procedure Concept
- SQL Statement가 포함된 Procedure가 Object 형태로 System Library에 저장된다.
  (Stored Procedure는 SQL이외에도 여러 언어로 작성될 수 있다.)
- Stored Procedure는 Server-Side에 저장되어 있어,
  Server와 네트워크로 연결된 Clients는 RPC(Remote Procedure Call)를 통해
  간편하게 Procedure를 호출할 수 있다.
  (즉, Client가 Procedure를 갖고 있지 않고, 원격으로 호출하여 사용할 수 있다.)
- 자주 사용하는 SQL 질의를 Stored Procedure 형태로 Server에 저장하여 Performance 향상을 도모할 수 있다.

* Advantages of Stored Procedure
1) Application Logic(Procedure)이 Data에 가까이 위치해있어(같은 Process Space에 위치하여)
   Inter-Process Communication이 빠르게 이루어질 수 있다.
2) 질의 결과를 한 레코드씩 받는 것이 아닌, 최종 결과를 한 번에 RPC를 통해 전달하기 때문에 Network Cost가 적다.
3) Application Logic을 재사용할 수 있어 다른 User도 RPC를 통해 연결되어 사용할 수 있다.

Example. Stored Procedure

CREATE PROCEDURE ShowNumReservations
    SELECT S.sid, S.sname, COUNT(*)
    FROM Sailors S, Reserves R
    WHERE S.sid = R.sid
    GROUP BY S.sid, S.sname

- Stored Procedure Model에서는 이렇게 정의된 Procedure는 Precompile되어 Server의 System Library에 저장된다.

* Parameter for Stored Procedure
- Stored Procedure는 유효한 SQL Type의 Argument를 요구한다.

1) IN
- Stored Procedure에 건네줄 Argument를 의미한다.

2) OUT
- Stored Procedure로부터 리턴받을 값을 의미한다.

3) INOUT
- IN, OUT의 역할을 함께 하는 Parameter를 의미한다.

Example. Stored Procedure

CREATE PROCEDURE IncreaseRating ( IN sailor_sid INTEGER, IN increase INTEGER ) -- 두 가지 정수형 매개변수를 입력받는다.
    UPDATE Sailors
        SET rating = rating + increase
        WHERE sid = sailor_sid

Example. Stored Procedure using Java

CREATE PROCEDURE TopSailors( IN num INTEGER )
    LANGUAGE Java
    EXTERNAL NAME “file:///c:/storedProcs/rank.jar” -- TopSailors Procedure가 호출되면 실행할 Java 프로그램을 지정한다.

* Calling Stored Procedure
- Stored Procedure를 호출하는 방식엔 크게 세 가지가 존재한다.

1) Embedded SQL

EXEC SQL BEGIN DECLARE SECTION
	int sid;
	int rating;
EXEC SQL END DECLARE SECTION
    // set sid and rating to some values
    // now increase the rating of this sailor
EXEC CALL IncreaseRating(:sid, :rating);  // IncreaseRating Procedure에 매개변수를 넘겨주며 호출한다.

2) JDBC

CallableStatement cstmt = con.prepareCall(“{call ShowSailors}”);
ResultSet rs = cstmt.executeQuery(); // Stored Procedure를 실행한다.
while ( rs.next() ) {
    // process result set
}

3) SQLJ

#sql iterator SailorInfo(…);
SailorInfo sailorinfo;
#sql sailorinfo = {CALL ShowSailors};  // ShowSailors Procedure를 실행한다.
while (sailorinfo.next()) {
    // process SQL result
}

---

SQL/PSM

- Stored Procedure를 작성하는 방법 중 하나로, 표준으로 지정되어 있다.

* SQL/PSM Stored Procedure 선언 형식

CREATE PROCEDURE name(p1, p2, …, pn)
    local variable declarations
    procedure code;

* SQL/PSM Function 선언 형식

CREATE FUNCTION name (p1, …, pn) 
    RETURNS sqlDataType
    local variable declarations
    function code;

* SQL/PSM Local Variable 선언 형식

DECLARE variableName dataType	

* SQL/PSM Assignment 형식

SET variableName = ( SQL_Query )

* SQL/PSM Branch and Loop

// Branch
IF     (condition) THEN statements;
ELSEIF (condition) THEN statements;
ELSE statements;
END IF;


// Loop
LOOP statements;
END LOOP

Example. SQL/PSM

CREATE FUNCTION rateSailor (IN sailorId INTEGER)
    RETURNS INTEGER  // integer형 값을 리턴함을 알린다.
    
// local two variables
DECLARE rating INTEGER		
DECLARE numRes INTEGER

SET numRes =
  ( SELECT COUNT(*)
    FROM Reserves R
    WHERE R.sid = sailorId )
    
IF (numRes > 10) THEN rating = 1;
                 ELSE rating = 0;
END IF;
RETURN rating;

---

Reference: Database Management Systems 3E (Raghu Ramakrishnan, Johannes Gehrke 저, McGrawHill, 2003)