Transcript [강의 download]

Database
Programming
Contents




Overview (Architecture)
Problems
DB와의 연결 방식
Middleware Architecture
– ODBC, JDBC, Perl DB


Programming Examples
Web & Database
– 연동 방식
2
Database Connectivity



일반 프로그램에서 Database에 접근
질의 결과를 프로그램내부에서 처리
하나의 응용프로그램으로 다양한
데이타베이스 접근
– Cf) device driver  database driver
3
Problems

Heterogeneity
– Access to Diverse DBMS

Impendence Mismatch
– Query results (RDB)  Set
– Program variable

Directly cannot deal with “Set”
?
DBMS
Database
4
Impedance Mismatch
Program variable
M_field_A
M_field_B
…
Field A Field B
-------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------
….
-------------------------------------------
Row
DBMS
Database
5
DB와의 연결 구조 (1)
Client Program
DBMS dependent
API
DB Server
Application
DBMS
Database
Client Side
Server Side
6
DB와의 연결 구조 (2)
Client Program
DB Client
Application
DBMS
Networking
Module
Database
Client Side
Server Side
7
DB와의 연결 구조 (3)
Client Program
MiddleWare
For DB
DBMS
Database
8
Uniform Interface to DB
JDBC
ODBC
Perl DBI
OLE DB
DAO
ADO
…
9
ODBC Architecture
Application
“what”
Driver Manager
ODBC
Driver
ODBC
Driver
Oracle
Database
Informix
Database
ODBC
Driver
Sybase
Database
ODBC
Driver
“how”
MySQL
Database
10
JDBC Architecture
Java Application
JDBC Manager
JDBC
Driver
JDBC-ODBC
Bridge
JDBC
Driver
JDBC
Driver
ODBC
Driver
Native
Driver
Sybase
Database
MySQL
Database
Informix
Driver
Oracle
Database
11
Perl DB Architecture
Perl Script
DBI
Oracle
DBD
ODBC DBC
SQLServer
DBC
Sybase
DBD
ODBC
Driver
Oracle
Database
MySQL
Database
SQLServer Sybase
Database Database
12
Actions on Client & Server
1. Open a connection
1. Creates a Connection session
2. Sends a query statement
2. Executes statement
3. Retrieve results
3. Sends results
4. Closes a connection
4. Close the session
Client Side
Server Side
13
ODBC

Application
–
–
–
–
–
–
DB 접속, 세션 요청
SQL 결과를 위한 저장 영역 및 데이터 포맷 정의
결과 요청
에러 처리
Query/Commit/Rollback 요청
DB 접속 종료
14
ODBC

Driver Manager (ODBC.DLL)
– DB (data) source name을 특정 driver의 DLL로
mapping
– ODBC 호출 검증

DB Driver
–
–
–
–
–
–
–
Data source에 연결 설정
Data source에 질의 요청 전송
Data format 변환
Query results를 application쪽으로 반환
Error code 리턴
Cursor 선언 및 조작
Transaction 처리
15
ODBC drivers
16
Example
Program
code
NameCard
DB
17
Database Programming
Make a connection
More rows ?
Build SQL statement
Close result set
Send SQL statement
Close SQL statement
Fetch Row
Close connection
18
Int example (UCHAR *server, UCHAR *uid, UCHAR *pwd)
{
HENV henv;
HDBC hdbc;
HSTMT hstmt;
UCHAR id[10], name[100], select[200];
SDWORD namelen, idlen;
RETCODE rc;
SQLAllocEnv(&henv);
SQLAllocConnect(henv, &hdbc);
DB접속 rc=SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return printf(“can’t connect”);
SQLAllocStmt(hdbc, &hstmt);
lstrcpy((LPTSTR) select, “SELECT InstructId, Name FROM Instructor”);
질의준비 if (SQLExecDirect(hstmt, select, SQL_NTS) != SQL_SUCCESS)
/요청
return printf(“can’t exec direct”);
printf(“InstructorID
Name\n”);
변수매핑 SQLBindCol(hstmt, 1, SQL_C_CHAR, id, (SDWORD)sizeof(id), &idlen);
SQLBindCol(hstmt, 2, SQL_C_CHAR, name, (SDWORD)sizeof(name), &namelen);
Example
(ODBC API Code)
질의결과
출력
DB종료
while(TRUE){
rc = SQLFetch(hstmt);
if (rc != SQL_NO_DATA_FOUND) printf(“%-12s %-9s\n”, id, name);
else break;
}
SQLTransact(henv, hdbc, SQL_COMMIT);
SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv);
return TRUE;
}
19
Example (Perl Code)
# 데이타베이스 접속--------------------------------------------------$dsn = "dbi:Oracle:host=swan;sid=ora8";
$ENV{ORACLE_HOME} = "/usr4/ora8/app/oracle/product/8.0.4";
$ENV{ORA_NLS32} = "$ORACLE_HOME/ocommon/nls/admin/data";
$user = "catalog";
$password = "stop";
$dbh = DBI->connect($dsn, $user, $password) || die "$DBI::errstr";
$sql_doc
= "SELECT did, file_path, input_date FROM docs"; # 모든 문서에 대해서
#print "<font color=black size=2>$sql_doc</font>\n";
$sth = $dbh->prepare($sql_doc) || die "DBI::errstr";
$sth->execute() || die "DBI::errstr";
while (($did, $file_path, $input_date) = $sth->fetchrow_array()){
#
print "[문서번호] $did [화일명] $file_path [입력날짜] $input_date\n","<br>\n";;
}
$dbh->do (“insert into $유_name values (“perl”, “database”, ‘2000/10/17’, …);
$dbh->do (“drop table students”);
$sth->finish;
$dbh->disconnect;
20
Example (PHP Code)
$host = "localhost";
$user = "php";
$password = "12345";
$connect = mysql_connect($host, $user, $password);
mysql_select_db('php_db',$connect);
$bookname = $_POST["bookname"]; $bookauthor = $_POST["bookauthor"]; $pubyear = $_POST["pubyear"];
$position1 = $_POST["position1"]; $position2 = $_POST["position2"];
$sql = "insert into bookmanage values('$bookname', '$bookauthor' ,'$pubyear‘ ,'$position1','$position2')";
mysql_query($sql,$connect);
$connect = mysql_connect($host,$user,$password);
mysql_select_db('php_db', $connect);
$sql = "select * from bookmanage";
$result = mysql_query($sql,$connect);
$rows = mysql_num_rows($result);
echo"<table border=1>";
echo"<tr><th>책이름<th>책저자<th>출판년도<th>위치1<th>위치2";
for($i=0; $i<$rows; $i++)
{
$record = mysql_fetch_array($result);
echo "<tr>";
echo "<td>$record[bookname]";
echo "<td>$record[bookauthor]";
echo "<td>$record[pubyear]";
echo "<td>$record[position1]";
echo "<td>$record[position2]";
}
echo"</table>";
mysql_close($connect);
?>
21
Example (Visual C++)


ODBC설정
데이타베이스 접속
– 데이타베이스와 테이블 지정
– DB접속을 위한 object 생성

Result set과 매핑되는 변수 마련
– Result set  RECORDSET object 
DOCUMENT object

Result set 출력
– DOCUMENT class 변수  VIEW object
22
Class CPubsDoc : public CDocument
CPubsSet m_pubsSet
Class CPubsSet : public CRecordSet
CPubsSet(CDatabase* pDatabase = NULL);
Class CPubsView : public CRecordView
Database
CPubsSet* m_pSet;
m_pSet->Open();
m_pSet->MoveNext();
m_pSet->Update();
...
m_pSet->Close();
• 질의 요청
• Result set 제어
23
DB Field  Prog. Variable
(Visual C++)
24
Web & Database




CGI 방식
Demon 방식
확장 API 방식
Sublet 방식
25
CGI 방식
HTTP Request
Internet
Calls
CGI DB
Application
Web
Server
HTML Results
• CGI가 직접 DBMS에 접근해 데이터를 추출
• 구현 용이  기존의 웹서버 브라우저 그대로 사용
• One Request  One Process
• Context switching overhead
HTML
HTML
Data
26
확장 API 방식
HTTP Request
Internet
Ex) Apache + PHP
DB
Web
Application
Server Program
HTML Results
• DB application program을 Web Server안에서 직접 구동
• DB Request  Web Server에서 처리
• Context switching overhead 없음
• 특정 web browser에 종속
HTML
Data
27
Demon 방식
DB requests
Java
Applet
Demon
HTTP Request
Web
Server
Internet
Remote
Method
Invocation
HTML Results
Query Results
• Client에 있는 Java Applet이 직접 질의요청
HTML
Data
28
Servelet 방식
HTTP Request
Internet
HTML Results
Calls
Web
Server
Servelet
Process
HTML
data
• DB application program을 Web Server가 대신 호출
• DB Request  Web Server에서 처리
• Context switching overhead 없음
•특정 web browser에 독립
HTML
Data
29