Database Programming.

Presentation on theme: "Database Programming."— Presentation transcript:

1 Database Programming

2 Contents Overview (Architecture) Problems DB와의 연결 방식
Middleware Architecture ODBC, JDBC, Perl DB Programming Examples Web & Database 연동 방식

3 Database Connectivity
질의 결과를 프로그램내부에서 처리 하나의 응용프로그램으로 다양한 데이타베이스 접근 Cf) device driver  database driver

4 Problems ? Heterogeneity Impendence Mismatch Access to Diverse DBMS
Query results (RDB)  Set Program variable Directly cannot deal with “Set” DBMS Database ?

5 -------- -------------- -------
Impedance Mismatch Program variable Row Field A Field B …. M_field_A M_field_B … DBMS Database

6 DB와의 연결 구조 (1) DBMS dependent API Client Program DBMS Database
DB Server Application DBMS Database Client Side Server Side

7 DB와의 연결 구조 (2) Client Program DBMS Database Client Side Server Side
Application DBMS Networking Module Database Client Side Server Side

8 DB와의 연결 구조 (3) Client Program MiddleWare For DB DBMS Database

9 Uniform Interface to DB
JDBC ODBC Perl DBI OLE DB DAO ADO …

10 ODBC Architecture Application Driver Manager “what” “how” Oracle
Database Informix Database Sybase Database MySQL Database

11 JDBC Architecture Java Application JDBC Manager Sybase Database MySQL
Driver JDBC-ODBC Bridge JDBC Driver JDBC Driver ODBC Driver Native Driver Sybase Database MySQL Database Informix Driver Oracle Database

12 Perl DB Architecture Perl Script DBI Oracle Database MySQL Database
DBD ODBC DBC SQLServer DBC Sybase DBD ODBC Driver Oracle Database MySQL Database SQLServer Database Sybase Database

13 Actions on Client & Server
Open a connection Sends a query statement Retrieve results Closes a connection Creates a Connection session Executes statement Sends results Close the session Client Side Server Side

14 ODBC Application DB 접속, 세션 요청 SQL 결과를 위한 저장 영역 및 데이터 포맷 정의 결과 요청 에러 처리
Query/Commit/Rollback 요청 DB 접속 종료

15 ODBC Driver Manager (ODBC.DLL) DB Driver
DB (data) source name을 특정 driver의 DLL로 mapping ODBC 호출 검증 DB Driver Data source에 연결 설정 Data source에 질의 요청 전송 Data format 변환 Query results를 application쪽으로 반환 Error code 리턴 Cursor 선언 및 조작 Transaction 처리

16 ODBC drivers

17 Example Program code NameCard DB

18 Database Programming Make a connection More rows ? Build SQL statement
Close result set Send SQL statement Close SQL statement Fetch Row Close connection

19 Example (ODBC API Code)
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); 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); 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; Example (ODBC API Code) DB접속 질의준비 /요청 변수매핑 질의결과 출력 DB종료

20 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;

21 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); ?>

22 Example (Visual C++) ODBC설정 데이타베이스 접속 Result set과 매핑되는 변수 마련
데이타베이스와 테이블 지정 DB접속을 위한 object 생성 Result set과 매핑되는 변수 마련 Result set  RECORDSET object  DOCUMENT object Result set 출력 DOCUMENT class 변수  VIEW object

23 CPubsSet(CDatabase* pDatabase = NULL);
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 제어

24 DB Field  Prog. Variable
(Visual C++)

25 Web & Database CGI 방식 Demon 방식 확장 API 방식 Sublet 방식

26 CGI 방식 HTTP Request Calls Web Server Internet HTML HTML Results HTML
CGI DB Application Web Server Internet HTML HTML Results CGI가 직접 DBMS에 접근해 데이터를 추출 구현 용이  기존의 웹서버 브라우저 그대로 사용 One Request  One Process Context switching overhead HTML Data

27 확장 API 방식 HTTP Request Ex) Apache + PHP Web Server Internet
DB Application Program Internet HTML Results DB application program을 Web Server안에서 직접 구동 DB Request  Web Server에서 처리 Context switching overhead 없음 특정 web browser에 종속 HTML Data

28 Demon 방식 DB requests Java Applet HTTP Request Demon Web Server
Remote Method Invocation Internet HTML Results Query Results Client에 있는 Java Applet이 직접 질의요청 HTML Data

29 Servelet 방식 HTTP Request Calls Web Server Internet HTML HTML Results
Process Internet HTML HTML Results data DB application program을 Web Server가 대신 호출 DB Request  Web Server에서 처리 Context switching overhead 없음 특정 web browser에 독립 HTML Data