Providing Access to Databases on the World Wide Web

Document Sample
Providing Access to Databases on the World Wide Web Powered By Docstoc
					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                        CRecordset
        – Open()                      – constructor
        – IsOpen()                    – Open()
        – Close()                     – 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
                                    W   Env.Var.
         http://localhost:3000/c    e
         gi-bin/DBServer.exe?       b
         SELECT=SELECT+*                  CGI
         +FROM+Table1
                                    S
                                    e    ODBC
                                    r
                                    v
                                    e   Database
Faculty of Electronic Engineering
                                    r              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

				
DOCUMENT INFO