Database Programming

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

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

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

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

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

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

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

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

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

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

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

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

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

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 처리

ODBC drivers

Example Program code NameCard DB

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

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종료

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;

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

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

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 제어

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

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

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

확장 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

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

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