Chapter 4. The Web Data Design and Development
The Open Data Base Connectivity (ODBC) standard allows users and developers to access information in various databases without having to know the interface details for each database. In other words, ODBC represents an abstraction layer if the application can interface to an ODBC database
4.1 The Process of ODBC Drivers
In order to log to a database, that database must be set up as an ODBC source on the server system. This identifies to the ODBC driver what type of database with which it will interface, as well as where that database is (local or network).
4.1.1 Connecting Access 97 with ODBC
To set up an ODBC DSN, do the following:
Figure 4. 1 ODBC Data Source Administrator [31]
Figure 4. 2 Add New System DSN [31]
Figure 4. 3 Select New ODBC Driver [31]
Figure 4. 4 Select Database [31]
Figure 4. 5 Select Current File from Directories [31]
Figure 4. 6 Type Data Source Name and Description [31]
Figure 4. 7 Setup up Login Name and Password for ODBC System [31]
4.1.2 Connecting Microsoft SQL 6.5 with ODBC
To set up an ODBC DSN, do the following:
Figure 4. 8 Set up ODBC Data Source Administrator [32]
Figure 4. 9 Add New System DSN [32]
Figure 4. 10 Select SQL Sever to be a New Data Source [32]
Figure 4. 11 SQL Server Setup [32]
4.2 The Process of ODBC Connectivity
In an effort to standardize an interface to DBMS's, Microsoft created Open Database Connectivity (ODBC) based on the definition of SQL CLI (Call Level Interface). ODBC is an API in which application developers can code their programs using ODBC function calls, and each DBMS vendor can provide an ODBC driver for their specific DBMS. An application written for the ODBC API can be used to access any DBMS, given the appropriate ODBC drivers.
4.2.1 Many Vendors: One ODBC Solution
ODBC alleviates the need for independent software vendors and corporate developers to learn multiple API's. Now ODBC provides a universal data access interface. Application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases [22].
ODBC is a specification to which developers write either [15]:
To use ODBC, the following three components are required [15]:
Some DBMS vendors provide a transport mechanism for client applications to access the database server over a network. SQL is built around a network paradigm, as the database engine is a protector to be accessed locally via a UNIX domain socket or remotely via a TCP socket. Oracle provides developers with SQLNet, a set of libraries to facilitate data transfers over a TCP/IP network [7].
A three-tier architecture can be used to develop ODBC clients in a TCP/IP network. The client application is written to the ODBC specifications, and compiled with the ODBC and DBMS transport libraries. The client binary is now equipped to communicate with a DBMS server remotely, and the source code is portable among other DBMS's.
Most ODBC 2.0 or 3.0 drivers should work with the JDBC-ODBC Bridge. There is a conflict between Sybase ctlib and the Solaris Java VM. This prevents the use of Sybase ctlib based ODBC drivers in this configuration. Since there are some variations in functionality between ODBC drivers, the functionality of the Bridge may be affected [8]. The bridge works with popular PC databases, such as Microsoft Access and FoxPro. This section describes troubleshooting an ODBC Data Source that is connected via a third-party ODBC driver. Three issues require attention [27].
4.3.1 A Program with JDBC-ODBC Bridge
Use of the JDBC-ODBC Bridge from an untrusted applet running in a browser, such as Netscape Navigator, is not allowed. The JDBC-ODBC Bridge does not allow untrusted applets to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser cannot circumvent Java security by calling ODBC [8]. ODBC is native code, so once ODBC is called, Java cannot guarantee that a security violation will not occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration. Therefore, it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJava browser (available from JavaSoft), since HotJava provides an option to turn off applet security [7]. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if users choosing this option, and use an all-Java JDBC driver whenever possible to avoid security problems.
4.3.2 Testing Microsoft Access 97 by using dbANYWHERE
This procedure describes how to connect to an ODBC Microsoft Access 97 Data Source via JDBC using a third-party ODBC driver [27].
For example:
To connect to a Microsoft SQL Server ODBC Data Source named "Access97":
=>jdbc:dbaw://localhost:8889/MS_ACCESS/Access97/Access97
Microsoft Access ODBC errors may be due to conflicting versions of the Microsoft Access engine and ODBC vendor drivers, as well as using a combination of 16-bit (Windows 3.x) and 32-bit (Windows 95/NT) software. Most documents recommend that users use 32-bit software for Windows 95/NT, as often as possible, as 16-bit applications require more overheads [24].
To test a Data Source's connection using the Test Data Sources command, Choose Help Test Data Sources (Figure 4.12). Select a Data Source from the list. The list consists of the Data Sources in the dbANYWHERE Data Source configuration file [26].
Figure 4. 12 Test Data Source's connection [26]
To test a Data Source's connection using the Data Source Tool (Figure 4.13):
Figure 4. 13 Data Source Tool [26]
In addition, dbANYWHERE Administrator lets user test a link performance of a dbANYWHERE Server machine.
How to start dbANYWHERE Administrator [26]:
4.4 Using JDBC accesses to Microsoft Access Database
JDBC is the new standard for data access. This standard, developed and published by Sun Microsystems, allows a Java client application to make database requests without knowing the specific database that the requests are made against or the implementation of SQL used by that database’s vend [7].
Java is used as the programming language. SQL is used as the database query language Implements, non-proprietary JDBC libraries to make data access and storage requests to the back-end database server. In the dbANYWHERE server, 100% of JDBC functionality is supported. Database drivers are included for Oracle, Watcom, Sybase, and Microsoft SQL Server [7]. The dbANYWHERE server allows users to purchase ODBC drivers for almost all other database server types. The application developer does not need access to the database server nor to the dbANYWHERE server, thereby increasing control and easing administration. Presently, dbANYWHERE runs on Microsoft Windows NT Server, Microsoft Windows NT Workstation, and Microsoft Windows 95.
dbANYWHERE’s JDBC classes implement 100% of the JDBC standard when users use dbANYWHERE. The system architecture consists of three tiers [28]:
The dbANYWHERE architecture uses advances in software technology to provide the benefits of the other approaches without the drawbacks. dbANYWHERE clients use a Java enabled web browser. These clients can then run Java applets or applications that access the dbANYWHERE middleware server using Symantec’s dbANYWHERE classes, which implement Sun Microsystem’s JDBC specification [28]. This approach allows complete platform independence on the client side, as Java enabled browsers are available for Windows, Unix, and Macintosh machines. When the client invokes a JDBC method, a request is sent to the dbANYWHERE server which dispatches it to the appropriate database servers. Data servers, which can be running on any platform, return the requested data to the dbANYWHERE server, where the data are cached for client access and, in the case or large requests, smaller increments of the data are sent on to the client on demand [26].
4.4.1 The Method about the ODBC Connection
A Connection object represents a connection with a database. A connection session includes the SQL statements that are executed and the results that are returned over that connection. A single Application can have one or more connections with a single database, or it can have connections with many different databases.
A URL (Uniform Resource Locator) gives information for locating a resource on the Internet. It can be thought of as an address. The first part of a URL specifies the protocol used to access information, and a colon always follows it. Some common protocols are "ftp," which specifies "file transfer protocol," and "http," which specifies "hypertext transfer protocol." If the protocol is "file," it indicates that the resource is in a local file system rather than on the Internet [10]. (Underlining in the examples below is used to indicate the part being described; it is not part of the URL.)
The rest of a URL, everything after the first colon, gives information about where the data source is located. If the protocol is a file, the rest of the URL is the path to a file. For the protocols ftp and http, the rest of the URL identifies the host and may optionally give a path to a more specific site. For example, below is the URL for the KSI home page. This URL identifies only the host:
By navigating from this home page, one can go to many other pages, one of which is the eric home page. The URL for the JDBC home page is more specific and looks like this:
http://www.ksi.edu/~eric
A JDBC URL provides a way of identifying a database so that the appropriate driver will recognize it and establish a connection with it. Users do not need to worry about how to form a JDBC URL; they simply use the URL supplied with the drivers they are using [10]. JDBC's role is to recommend some conventions for driver writers to follow in structuring their JDBC URLs. Since JDBC URL is used with various kinds of drivers, the conventions are of necessity very flexible.
The standard syntax for JDBC URL is shown below. It has three parts which are separated by colons:
jdbc:<subprotocol>:<subname>
The three parts of a JDBC URL are broken down as follows [10]:
jdbc:dbaw:eric
jdbc:dbaw:// localhost:8889/MS_ACCESS/Access97/Access97
The standard way to establish a connection with a database is to call the method DriverManager.getConnection. This method takes a string containing a URL. The DriverManager class, referred to as the JDBC management layer, attempts to locate a driver can connect to the database represented by that URL. The DriverManager class maintains a list of registered Driver classes, and when the method getConnection is called, it checks with each driver in the list until it finds one that can connect to the database specified in the URL [8]. The Driver methods connect by used this URL to establish the connection. Users can pass the JDBC management layer and call Driver methods directly. This could be useful in the rare case that two drivers can connect to a database and the user wants to select a particular driver. Normally, it is much easier to let the DriverManager class handle opening a connection.
The following code exemplifies opening a connection to a database located at the URL="jdbc:dbaw://localhost:8889/MS_ACCESS/Access97/Access97" with a user ID of "eric" and "2175" as the password :
serverUrl="jdbc:dbaw://localhost:8889/MS_ACCESS/Access97/Access97";
Connection server;
Properties props;
props = new Properties();
props.put("user", "eric");
props.put("password", "2175");
driver=symjava.sql.Driver)Class.forName("symantec.itools.db.jdbc.Driver").newInstance();
server = driver.connect(serverUrl, props);
Once a connection is established, it is used to pass SQL statements to its underlying database. JDBC does not put any restrictions on the kinds of SQL statements that can be sent; this provides a great deal of flexibility, allowing the use of database-specific statements or even non-SQL statements. It requires that the user be responsible for making sure that the underlying database can process the SQL statements being sent and suffer the consequences if it cannot. For example, an application that tries to send a stored procedure call to a DBMS that does not support stored procedures will be unsuccessful and generate an exception. JDBC requires that a driver that provides at least ANSI SQL-2 Entry Level capabilities in order to be designated JDBC COMPLIANTTM [10]. This means that users can count on at least this standard level of functionality. JDBC provides three classes for sending SQL statements to the database, and three methods in the Connection interface create instances of these classes. These classes and the methods that create functions listed below [10]:
On the other hand, the following list gives a quick way to determine which Connection method is appropriate for creating different types of SQL statements [10]:
A transaction consists of one or more statements that have been executed, completed, and then either committed or rolled back. When the method commit or rollback is called, the current transaction ends, and another one begins [10].
A new connection is in auto-commit mode by default, meaning that when a statement is completed, the method commit will be called on that statement automatically. In this case, since each statement is committed individually, a transaction consists of only one statement. If auto-commit mode has been disabled, a transaction will not terminate until the method commit or rollback is called explicitly, so it will include all the statements that have been executed since the last invocation of the commit or rollback method. In this second case, all the statements in the transaction are committed or rolled back as a group. The method commit makes permanent any changes an SQL statement makes to a database, and it also releases any locks held by the transaction. The method rollback will discard those changes.
Sometimes a user does not want one change to take effect unless another one does also. This can be accomplished by disabling auto-commit and grouping both updates into one transaction. If both updates are successful, then the commit method is called, making the effects of both updates permanent; if one fails or both fail, then the rollback method is called, restoring the values that existed before the updates were executed.
Most JDBC drivers will support transactions. In fact, a JDBC-compliant driver must support transactions. DatabaseMetaData supplies information describing the level of transaction support a DBMS provides [10].
The sql.zip is the complete set of JDBC classes that are the same as Sun's JDBC 1.21 package except that the base package "java" has been renamed "symjava." The package is named symjava because the Java Security Model does not let you download classes that start with "java." Future versions of dbANYWHERE which use JDK 1.1 will not provide sql.zip [27].
Two main methods in the sql.zip are [26]:
The dbaw_awt.zip contains the dbAWARE components which use the dbANYWHERE API (which is in dbaw.zip) to support data binding, simplified master/detail relationships, scrolling database cursor support, and optimistic concurrency.
Directories in dbaw_awt.zip are [27]:
The JDBC-ODBC bridge driver translates JDBC method calls into ODBC function calls. It allows JDBC to leverage the database connectivity provided by the existing array of ODBC drivers. JDBC is designed to be efficiently implementable on ODBC, so the JDBC-ODBC bridge is the best way to use ODBC from Java. It is a joint development of JavaSoft and Intersolv. If possible, use a PureJava JDBC driver instead of the Bridge and an ODBC driver. This method eliminates the client configuration required by ODBC. It also eliminates the potential that the Java VM could be corrupted by an error in the native code. The Bridge does not work with Microsoft's J++ (J++ uses a non-standard native calling convention). Users can use JavaSoft's JDK or Symantec's Cafe instead [7].
4.4.9 A Example (Library System)
For Students:
Figure 4. 14 Main Menu for the Library System
Figure 4. 16 Search Database by Title
Figure 4. 17 Check Password to Protect System
4.5 Using Internet Database Connector connects to SQL Server 6.5
Microsoft Internet Information Server is a Web server that enables users to publish information on a corporate Intranet or on the Internet. Internet Information Server transmits information by using the Hypertext Transfer Protocol (HTTP) [13]. Internet Information Server can also be configured to provide File Transfer Protocol (FTP) and gopher services. The FTP service enables users to transfer files to and from the Web site. The gopher service uses a menu-driven protocol for locating documents. The gopher protocol has been largely superseded by the HTTP protocol.
4.5.1 Using an Internet Information Server
The creative possibilities of what users are offered on an Internet Information Server Web site are endless. Some familiar uses are to [14]:
4.5.2 The Methods of Internet Information Server
The Web is fundamentally a system of requests and responses. Web browsers request information by sending a URL to a Web server. The Web server responds by returning a Hypertext Markup Language (HTML) page.
The HTML page can be a static page that has already been formatted and stored in the Web site. A page, which the server dynamically creates in response to information, provides by the user, or a page lists the available files and folders on the Web site. Every page on the Intranet or on the Internet has a unique URL that identifies it. Web browsers request a page by sending a URL to a Web server. The server uses the information in the URL to locate and display the page. URL syntax is a specific sequence of protocol, domain name, and path to the requested information. The protocol is the communication method used to gain access to information for example, Hypertext Transport Protocol (HTTP). Internet Information Server supports the HTTP, FTP, and gopher protocols. The domain name is the Domain Name System (DNS) name of the computer that contains the information. The path is the path to the requested information on the computer. The following table shows examples of different URLs [14]:
A URL can also contain information that the Web server must process before returning a page. The data are added to the end of the path. The Web server passes the data to a program or a script for processing and returns the results in a Web page. Example request types are listed in the following table [14]:
http://www.microsoft.com/backoffice/home.htm
http://www.msn.com/custom/page1.dll? CUST=onInternet Database http://www.microsoft.com/feedback/input.idc
A Web server responds to a Web browser request by returning an HTML page. The returned page can be one of three types: a static HTML page, a dynamic HTML page, or a directory-listing page.
4.5.3 The Features of Internet Information Server
Internet Information Server is built on the Windows NT security model. Windows NT security helps an administrator protect computer and resources by requiring assigned user accounts and passwords. In addition, an administrator can control access to computer resources by limiting the user rights of these accounts. An administrator can use the WindowsNT File System (NTFS) to assign permissions to folders and files on computer [33].
In addition to the WindowsNT security features, an administrator can set Read-only or Execute-only virtual directories by using Internet Service Manager. An Internet Information Server also provides a way to deny user access to computers with particular IP addresses. IIS supports the Secure Sockets Layer (SSL) protocol, which securely encrypts data transmissions between clients and servers [14].
Publishing Information and Using a Database with the WWW service and the Open Database Connectivity (ODBC) drivers provided with Internet Information Server, the user can:
4.5.4 How the Internet Database Connector Works
Web browsers (such as Internet Explorer or browsers from other companies such as Netscape) submit requests to the Internet server by using HTTP. The Internet server responds with a document formatted in HTML. Access to databases is accomplished through a component of Internet Information Server called the Internet Database Connector (IDC). The Internet Database Connector, Httpodbc.dll, is an ISAPI DLL that uses ODBC to gain access to databases [14].
The IDC uses two types of files to control how the database is accessed and how the output Web page is constructed. These files are Internet Database Connector (.idc) files and HTML extension (.htx) files. The Internet Database Connector files contain the necessary information to connect to the appropriate DBC data source and execute the SQL statement. An Internet Database Connector file also contains the name and location of the HTML extension file. The HTML extension file is the template for the actual HTML document that will be returned to the Web browser after the database information has been merged into it by the IDC.
Here are the entire contents of the file Sample.idc referenced in the preceding URL [17]:
Datasource: Web SQL
Username: sa
Template: sample.htx
SQLStatement:
+SELECT au_lname, ytd_sales
+ from pubs.dbo.titleview
+ where ytd_sales>5000
In Sample.idc, the SQLStatement returns all the author last names and year-to-date sales in units from the "pubs" sample database in SQL Server for authors whose books have year-to-date sales of more than 5,000 dollars.
To return data to the WWW client, the .idc file merges the HTML extension .htx file and the ODBC data. This combined data are attached to standard HTTP headers, passed to the WWW service, and returned to the client. The .htx file is an HTML document with some additional tags enclosed by <%%> or <!--%%-->, which the .idc file uses to add dynamic data to the document. The HTML formatting in the .htx file typically formats the data being returned. There are six keywords (begindetail, enddetail, if, else, endif, and "%") that control how the data from the database are merged with the HTML format in the .htx file[17]. Database column names specify what data are returned in the HTML document.
For example, the following line in a .htx file merges data from the Emailname column for every record processed:
<%begindetail%><%Emailname%><%enddetail%>
The Sample.htx file is an HTML document that contains Internet Database Connector tags for data returned from the database.
For Example (Sample.htx) [17]:
<HTML>
<BODY>
<HEAD><TITLE>Authors and YTD Sales</TITLE></HEAD>
<%if idc.sales eq ""%>
<H2>Authors with sales greater than <I>5000</I></H2>
<%else%>
<H2>Authors with sales greater than <I><%idc.sales%></I></H2>
<%endif%>
<P>
<%begindetail%>
<%if CurrentRecord EQ 0 %>
Query results:
<B>Author YTD Sales<BR></B>
<%endif%>
<%au_lname%><%ytd_sales%>
<%enddetail%>
<P>
<%if CurrentRecord EQ 0 %>
<I><B>Sorry, no authors had YTD sales greater than </I><%idc.sales%>.</B>
<P>
<%else%>
<HR>
<I>
<P>
</I>
<%endif%>
</BODY>
</HTML>
The <%begindetail%> and <%enddetail%> sections delimit where rows returned from the database will appear in the document. Columns returned from the query are surrounded by <%%>, such as <%au_lname%> and <%ytd_sales%> in this example [17].
4.5.5 Learning the Features of the Internet Database Connector
For example, in the last section, the query in Sample.idc returned only the authors whose year-to-date sales exceeded 5,000. By using a parameter, users could build a Web page that asks them to decide what number to use instead of 5000. The Web page must prompt the user for the year-to-date sales figure and then name the associated variable to "sales."
The HTML syntax for the input field and button in Sample2.htm is [17]:
<FORM METHOD="POST" ACTION="/scripts/samples/sample2.idc">
<P>
Enter YTD sales amount: <INPUT NAME="sales" VALUE="5000" >
<P>
<INPUT TYPE="SUBMIT" VALUE="Run Query">
</FORM>
In the Internet Database Connector file Sample2.idc, users can use the parameter shown in place of the number 5000 [17]:
SQLStatement:
+SELECT au_lname, ytd_sales
+ from pubs.dbo.titleview
+ where ytd_sales > %sales%
Here the parameter name must be "sales" that it corresponds to the <INPUT NAME= "sales" …> on the Web page. Parameters must be enclosed with percent characters (%) to distinguish them from a normal identifier in SQL. When the Internet Database Connector encounters the parameter in the .idc file, the Internet Database Connector substitutes the value sent by the Web browser and then sends the SQL statement to the ODBC driver.
The percent character (%) is also a wildcard character in SQL. Users can use wildcards in an SQL query to search for an element in a table that contains certain characters. To insert a single "%" for a SQL wildcard, use "%%." This prevents the IDC from trying to use the % as a parameter marker.
For example [17]:
SQLStatement:
+SELECT au_lname, ytd_sales, title
+ from pubs.dbo.titleview
+ where title like '%%%title%%%'
For a percent sign to be recognized as an SQL wildcard, users must double it and then add the percent characters around the parameter to distinguish the string as a parameter. In the example, the query searches for all entries in the title column with the word "title" in them. This query returns the following:
title
title and deed
main title page
author and title
To return all entries with the word "title" as the first five letters, users would format the query as follows:
SQLStatement:
+SELECT au_lname, ytd_sales, title
+ from pubs.dbo.titleview
+ where title like '%title%%%'
In this example, the following results are returned:
title
title and deed
Users can build powerful collections of Web pages by using the output of one query to provide links to other queries. For example, to show the titles for an individual author, instead of returning the author name as plain text, users can format it as a link and then use the link to do another query.
4.5.6 A Example by Using IDC (Library System)
Figure 4. 18 Main Menu for the Library System
Figure 4. 20 Show Results from Database System
Figure 4. 21 Book Sheet Information
Figure 4. 22 Main Menu for Library System
Figure 4. 23 Check User Name and Password
Figure 4. 24 Books management menu
Figure 4. 25 Update Book Database System
