PPT Presentation

Download Report

Transcript PPT Presentation

Providing Access to Databases
on the World Wide Web
the new lab exercise
Prof.dr Slobodanka Đorđević-Kajan
mr Aleksandar Dimitrijević
1
Faculty of Electronic Engineering
Introduction


Structures and Databases is a two-semester
course on undergraduated studies
Databases exercises currently includes
–
–
–
–
–
–

Lab1: ER modeling and ER-to-Relational mapping
Lab2: SQL (Create and loading tables)
Lab3: SQL (Queries)
Lab4: SQL (Update and Delete tables)
Lab5: SQL (Views and Indexes)
Lab6: Pro*C
Two new lab exercises extends basic set of
databases labs with application access to
databases
– Lab7: Data Access APIs
– Lab8: Providing Access to Databases on the World Wide Web
2
Faculty of Electronic Engineering
Task




Write a Windows console application in MS
Visual C/C++ for executing a simple SQL query
(defined in previous DB exercise) on the
database using ODBC API.
Create a HTML client containing form for data
requesting.
Modify console application so that can be
executed as CGI process.
Test the application using HTML client.
3
Faculty of Electronic Engineering
Prerequisites
High level of fundamental programming
skill (including exception handling)
 Medium level of C/C++ programming skill
using MS Visual C/C++
 Medium level of understanding HTTP
protocol, and appropriate header format
 Low-Medium level of HTML syntax
knowledge
 SQL syntax knowledge

4
Faculty of Electronic Engineering
Goals

Introduction to ...
– ODBC API
– HTML form definition
– CGI interface and retrieving paramethers from
system variables using C++

All together – writting an application that
enables remote users to access appropriate
data source through the Internet
5
Faculty of Electronic Engineering
ODBC

Open Database Connectivity (ODBC) is a
call-level interface that allows applications
to access data in any database for which
there is an ODBC driver.

ODBC provides an API that allows
application to be independent of the source
database management system (DBMS).
6
Faculty of Electronic Engineering
Main MFC ODBC Classes
 CDatabase
– Open()
– IsOpen()
– Close()
 CRecordset
– constructor
– Open()
– IsEOF
– MoveNext()
– Close()
7
Faculty of Electronic Engineering
CDatabase
CDatabase represents a connection to a data source
CDatabase db;
db.Open(NULL, FALSE, FALSE, _T("ODBC;Driver={Microsoft
Access Driver *.mdb)};DefaultDir=C:\\Tmp\\;DBQ=Db1.mdb"));
if(db.IsOpen())
{
....
db.Close();
}
8
Faculty of Electronic Engineering
CRecordset
CRecordset represents a set of records selected from a data
source.
CRecordset rs(&db);
try{
if(rs.Open( CRecordset::forwardOnly, selectQuery )){
while( !rs.IsEOF( ) ){
short index = 0;
try{
CString strVal;
while(true){
rs.GetFieldValue( index, strVal );
....
index++;}
}catch(CException* e){
...
rs.MoveNext( );}
}
rs.Close( );
...
9
Faculty of Electronic Engineering
HTML Form Creation
<HTML>
<head><title>DB klient</title></head>
<body background="bg.gif">
<FONT color="FFFFFF">
<h1>Database client - form</h1>
<form ACTION="http://localhost:3000/cgi-bin/DBServer.exe"
METHOD="GET">
Upit: <input TYPE="text" NAME="SELECT" SIZE="30"
MAXLENGTH="150">
<input TYPE="submit" VALUE="Query">
<p> Rezultati upita: </p>
<TEXTAREA NAME="RESULTS" ROWS="10" COLS="40">
</TEXTAREA>
</form>
</FONT></body>
</HTML>
10
Faculty of Electronic Engineering
HTML Form – Database client
11
Faculty of Electronic Engineering
Datapath
http://localhost:3000/c
gi-bin/DBServer.exe?
SELECT=SELECT+*
+FROM+Table1
Faculty of Electronic Engineering
W
e
b
Env.Var.
CGI
S
e
r
v
e
r
ODBC
Database
12
Receiving parameters

char* reqmethod =
getenv("REQUEST_METHOD");

char* q_string =
getenv("QUERY_STRING");
13
Faculty of Electronic Engineering
Output
printf("Pragma: no-cache\r\n");
printf("Content-Type: text/html\r\n");
SYSTEMTIME st;
GetSystemTime( &st );
CTime timeHttp( st );
CString time=timeHttp.Format( "%a, %d %b %Y %H:%M:%S GMT" );
printf("Last-Modified: %s\r\n\r\n",time);
printf("\r\n");
printf("<HTML><head><title>DB klient</title></head>");
...
14
Faculty of Electronic Engineering
Development Environment
Microsoft Visual C/C++
15
Faculty of Electronic Engineering
Summary
This exercise combines elements of Web
programming and database access using
ODBC interface.
 Should be carried out at the end of the
course, after finishing all other prerequisit
exercises.
 Should create the foundation for including
database access in more serious projects.

16
Faculty of Electronic Engineering
More Information ...
Like all other matherial needed in this
course, preparation for this lab can be found
on:
http://gislab.elfak.ni.ac.yu/baze/
17
Faculty of Electronic Engineering