\documentstyle[epsf,psfig,fancybox,semrot]{seminar}
% \input{psfig}

\def\printlandscape{\special{landscape}}    % Works with dvips.

% \twoup

% \landscape
\slideframe{Oval}
% \input{ifthen}

\slideheight 6.5in
% \renewcommand{\slideparskip}{.5ex}
\renewcommand{\slidestretch}{1.06}
% \renewcommand{\slidepartopsep}{.5ex}
\centerslidesfalse

\newcommand{\vs}[1]{\vspace*{#1ex}}
\newcommand{\eat}[1]{}

\newcommand{\ceilfrac}[2]{\lceil \frac{#1}{#2} \rceil}
% \newcommand{\epsfig}[1]{\psfig{#1}}

%%%%%%%%%%%%%%%%%%%%%
\newcounter{slistdepth}
\newenvironment{sitemize}
{
 \begin{itemize}
 \addtocounter{slistdepth}{1}
 % \ifthenelse{\value{slistdepth} = 1}{\addtolength{\itemsep}{-5pt}}{}
 \addtolength{\itemsep}{-5pt}
}
{
 \addtocounter{slistdepth}{-1}
 \end{itemize}
}

\newenvironment{senumerate}
{
 \begin{enumerate}
 \addtocounter{slistdepth}{1}
 % \ifthenelse{\value{slistdepth} = 1}{\addtolength{\itemsep}{-5pt}}{}
 \addtolength{\itemsep}{-5pt}
}
{
 \addtocounter{slistdepth}{-1}
 \end{enumerate}
}

\newenvironment{ssenumerate}
{
 \begin{enumerate}
 \addtolength{\itemsep}{-2pt}
}
{
 \end{enumerate}
} % Try to avoid using ssenumerate as far as possible

\newcommand{\algseplinelen}{2.8in}
\newcommand{\algsepline}{\rule{\algseplinelen}{0.1mm}\\}
\newcommand{\pseudocode}[1]{\begin{sf} 
\noindent \raisebox{0mm}[.2mm][.2mm]{\algsepline} \\
#1 

\noindent \raisebox{0ex}[.1mm][.1mm]{\algsepline} 
\end{sf}}

\newcommand{\bi}{\begin{itemize}}
\newcommand{\ei}{\end{itemize}}
\newcommand{\im}{\item}
\newcommand{\be}{\begin{enumerate}}
\newcommand{\ee}{\end{enumerate}}
\newcommand{\bs}{\begin{slide}}
\newcommand{\es}{\end{slide}}
\newcommand{\code}{\small\renewcommand{\baselinestretch}{1}}
\newcommand{\slidetitle}[1]{%
  \begin{center}
    \large\bf
    \shadowbox{#1}%
  \end{center}
  \vspace{.5ex minus .5ex}
}
\newcommand{\myfig}[2]{\centerline{
\setslidelength{\epsfxsize}{#2}\epsffile{#1}}
}
% \renewcommand{\slidetitle}[1]{\section{#1}}
% \renewcommand{\myfig}[2]{\psfig{width=#2,file=#1}}


%%%%%%%%%%%%%%%%%%%%%

\begin{document}

\bs

\vs{10}

\begin{center}
{\Large \bf An Introduction to JDBC}

\vs{10}

{\large \bf S. Sudarshan}

\vs{2}

{\large
\noindent
Computer Science and Engg. Dept \\
Indian Institute of Technology, Bombay
}

\end{center}

\es
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Introduction}

\bi
\im Client Server Database Architectures:
  \bi
   \im Backend:  database server
   \im Frontend: forms/gui/web interface
  \ei
\ei
\vs{2}

\myfig{clientserver.eps}{4.5in}
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Communicating With the Server}

\bi
  \im Standard way of expressing queries: SQL
  \im Problem:  How to send queries to server and receive results 
            in a standard {\em database independent} way?
  \im API (application program interface) standards for 
         database communication:
  \bi 
     \im ODBC (Open Database Connectivity, for C)
     \im JDBC (``Java Database Connectivity'', for Java)
  \ei
  \im There are also database specific ways of communication, e.g. Oracle OCI
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Steps in Database Access Using JDBC}

\be
  \im Open connection to database (including login/password)
  \im Send SQL statements
  \im Receive results (set of tuples -- rowset)
  \im Iterate over result set, extracting fields
  \im Exceptions
\ee
All above similar to ODBC, but calls are simpler

\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Example JDBC Code}

\vs{-2}
{\code
\begin{verbatim}
  try {
    DriverManager.registerDriver(
                    new oracle.jdbc.driver.OracleDriver());
    Connection myCom =
             DriverManager.getConnection(
                    "jdbc:oracle:thin:@everest:1521:GEN",
                    "sudarsha","mypasswd");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
                             "SELECT a, b, c FROM Table1");
    while (rs.next()) {
      int i = rs.getInt("a");
      String s = rs.getString("b");
      float f = rs.getFloat("c");
      System.out.println("ROW = " + i + " " + s + " " + f);
    }
  } catch(SQLException sqle){
    System.out.println("Exception " + sqle);
  }
\end{verbatim}
}

\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{More on JDBC}

\bi
    \im More on drivers and connections later
    \im Getting result fields:
      \bi
      \im 
       {\tt rs.getString("b")} and  {\tt rs.getString(2)} equivalent since 
           "b" is second argument of select result.
      \ei
    \im Null values
         {\code
\begin{verbatim}
int a = rs.getInt("a");
if (rs.wasNull()) System.out.println("Got null value");
\end{verbatim}
        }
    \im Exceptions
    \im Three forms of execute calls
     \bi
        \im executeQuery(..) :  for queries
        \im executeUpdate(..) : for updates
        \im execute(..) : general interface, rarely used
\ei  
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{ResultSetMetaData}
\bi
\im This class provides information about all the columns of
the ResultSet.
\im Instance of this class is obtained by getMetaData() function
of ResultSet.
\im Provides Functions for getting number of columns, column name,
type, precision, scale etc.
{\code
\begin{verbatim}
    ResultSetMetaData rsmd = rs.getMetaData();
    for ( int i = 1; i <= rsmd.getColumnCount(); i++ ) {
       String name = rs.getColumnName(i);
       String typeName = rs.getColumnTypeName(i);
    }
\end{verbatim}
}
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{PreparedStatement}

Subclass of Statement, allows queries to be compiled and
executed with different arguments

{\code
\begin{verbatim}
    PreparedStatement pstmt = con.prepareStatement(
        "UPDATE table4 SET m = ? WHERE x = ?");
    pstmt.setString(1, "Hi");  
    for (int i = 0; i < 10; i++) {
      pstmt.setInt(2, i);
      int rowCount = pstmt.executeUpdate();
    }
    pstmt.setInt(2, NULL) -- sets value to null
\end{verbatim}
}
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Callable Statement}

Subclass of Statement, allows SQL stored procedures/functions to be 
invoked.

{\code
\begin{verbatim}
CallableStatement cs1 = conn.prepareCall
                       ( "{call proc (?,?)}" ) ;
CallableStatement cs2 = conn.prepareCall
                       ( "{? = call func (?,?)}" ) ;
\end{verbatim}
}
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{DatabaseMetaData}
\bi
\im This class provides information about database.
\im Functions for getting all tables, all
columns of the table, primary keys etc.
{\code
\begin{verbatim}
    DatabaseMetaData dbmd = con.getMetaData();
    ResultSet rs = dbmd.getColumns( null, "DBIS",
                           "SUPPLIER", "%" );
    ResultSetMetaData rsmd = rs.getMetaData();

    while ( rs.next() ) {
       for ( int i = 0; i < rsmd.getColumnCount(); i++ ) { 
          System.out.println( rs.getString(i) );
       }
    }
\end{verbatim}
}
\im Functions for getting various database parameters
such as maximum row size, maximum no of connections, maximum
column name lenghts etc.
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{JDBC at Client and Server Side}

\bi
  \im JDBC at client:  Java class library for communicating with server
  \im JDBC server:  Invoke database with SQL, send results back to client
  \im Inbetween:   Network protocol
      \bi
	\im Not part of JDBC standard)
	\im  $\Rightarrow$ need matching clientside/serverside libraries
        \im  $\Rightarrow$ link to appropriate library depending on server
                    (interface same, library implementation may differ)
        \im Example:  Oracle JDBC Thin driver communicates with Oracle 
		using Sockets
     \ei
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{JDBC Drivers}

\bi
    \im Types of drivers
      \be
         \im JDBC-ODBC bridge plus ODBC driver \\
              \hspace*{10pt} JDBC client code communicates with ODBC server, 
                        which communicates with database 
         \im Native-API partly-Java driver \\
             \hspace*{10pt} Part of JDBC driver in native language like C/C++.
                         Not useful for applet/servlet. 
         \im JDBC-Net pure Java driver \\
             \hspace*{10pt} Database independent protocol
         \im Native-protocol pure Java driver \\
             \hspace*{10pt} Java code communicates with database proprietary
                            protocol.
      \ee
    \im E.g., Oracle Thin driver is Type 4
    \im Drivers must be registered with system. 
        Appropriate driver chosen by system when connection is opened. 
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
\bs
\slidetitle{JDBC URLs}

\bi
      \im Three parts, separated by colons: jdbc:<subprotocol>:<subname>
      \im Eg. {\tt jdbc:odbc:fred} uses odbc bridge, and odbc style subname
      \im Eg. {\tt jdbc:oracle:thin:@everest:1521:GEN}
           \bi
              \im Subname {\tt thin} says use Oracle's ODBC Thin driver
              \im Subname {\tt @everest} specifies machine name 
                     (everest.cse.iitb.ernet.in)
              \im {\tt 1521} is the port number 
              \im {\tt GEN} is the name of Oracle database to be used.
           \ei
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


\bs
\slidetitle{Two-tier Model}

Java+JDBC at client +  backend server
\bi
     \im Benefits:
      \bi
       \im flexible, need not be restricted to predefined queries
      \ei
     \im Problems:  
      \bi
       \im Security: passwords available at client site, 
	       any database operation possible
       \im More code shipped to client 
       \im Not appropriate across organizations, or large ones like universities
      \ei
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Three Tier Model}

% \bi
%   \im Two-tier Model: Java+JDBC at client machine
%   \im Three-tier Model: Web client + Java Servlet
% \ei

% \vs{2}

\myfig{threetier.eps}{6.5in}
% \centerline{
% \setslidelength{\epsfxsize}{7in}\epsffile{threetier.eps}
% }
% \vs{1}
% \centerline{Three Tier Model}
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Three-tier Model (Cont.)}

\bi
     \im E.g. Web client + Java Servlet
     \im Client sends request over http or application-specific protocol
     \im Application or Web server receives request
      \im Request handled by cgi program or servlet
      \im Security handled by application at server 
         \bi
          \im Better security
	  \im Fine granularity security
         \ei
      \im Simple client, but only packaged transactions
\ei

\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{JDBC vs. ODBC}

\bi
      \im ODBC for Java (ODBC is based on C)
      \im Improves on ODBC: cleaner, easier to learn
      \im JDBC-ODBC bridge:
        \bi 
	 \im translates JDBC calls to ODBC calls, 
	 \im can use standard ODBC support at backend
        \ei
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\bs
\slidetitle{Other Features}

\bi 
  \im Support for large object access via  streams
      \bi
        \im streams are like files
        \im can open a column of a result row as a stream and read bytes
              from it
      \ei

  \im Dynamic Database Access
      \bi
         \im Can be used to query database schema
	 \im Look up java.sql.DatabaseMetaData and java.sql.ResultSetMetaData 
              for more information
      \ei

  \im Transaction support
    {\code
\begin{verbatim}
    connection.setAutoCommit(false);
    connection.commit();  or  connection.rollback();
\end{verbatim}
}

\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
\bs
\slidetitle{JDBC 2.0 Extensions}

\bi
    \im Advanced data types inluding SQL3 data types in database
        \bi
          \im Reference types, structured types
          \im Inheritance
        \ei
    \im Direct storage of Java objects (using automatic serialization)
    \im Scrollable and updatable result sets 
    \im Rowsets and disconnected operation
    \im Batching of inserts and updates  (reduce calls on server)
        \bi
           \im Add multiple queries to a batch in a statement, 
		then execute the statement
           \im Oracle provides its own non-standard extensions
        \ei
\ei
\es

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
	
\bs
\slidetitle{For More Information}

\bi
   \im http://java.sun.com/products/jdk/1.2/docs/guide/jdbc/ \\
      \bi
       \im JDBC Technology Guide: getting started 
       \im JDBC 1.2 and 2.0 specifications in html/ps/pdf
       \im JDBC SDK including JDBC-ODBC bridge 
      \ei
   \im Oracle online manuals, JDBC section (comes with Oracle 8)
   \im Plenty of books
       \bi
         \im JDBC(TM) API Tutorial and Reference, Second Edition,
             Seth White, et al, 1999 
         \im Java Database Programming : Servlets \& JDBC 
              Alan Williamson, Ceri Moran, 1998 
      \ei
\ei
\es

\end{document}

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
	
\bs
\slidetitle{}

\bi
\ei
\es


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%