INTERNET DATABASE CONNECTOR

INTERNET DATABASE CONNECTOR Colorado Technical University IT420 Tim Peterson

IDC Uses • Leverages ODBC to create Web pages. • IDC is a component of ISAPI (Internet Server API) and is part of IIS. • IIS also supports CGI. • IDC is easier to develop than CGI • IDC is integrated with Microsoft’s PWS • IDC is better more limited than ASP because it requires no programming.

IDC Capabilities • Create dynamic web pages containing information from a database. • Insert, Update, and delete information from a database based on user input. • Perform all available SQL commands • The link library for IDC is contained in HTTPODBC.DLL

IDC Architecture • Two types of files are used for this architecture: • .idc - Must contain at least the following: • ODBC data source • Reference to a template file (.htx) • SQL statement to be processed by database.

IDC Architecture- Cont’d • .htx - Provides template for HTML output document after database information has been merged into it by HTTPODBC.DLL. • The .htx file performs two tasks: • Processes results returned by the database query • Formats these results and combines them with the static HTML in the file. • HTX stands for HTML extension. This is because you can do loops, handle logic, and work with variables.

Developers Tasks • Developers perform three main tasks: • Write code to pass the URL and form fields to the server • Write the .idc file to define the processing for a data source. • Prepare the .htx file to format the results for the client Web Browser.

ODBC DSN • Before accessing any ODBC compliant database, a DSN (Data Source Name) must be defined. • This DSN is used in the .idc file to access the database. • Use the windows ODBC administrator to accomplish this.

DSN Definition

HTML Example Using IDC <HTML> <HEAD> <TITLE>Northwind Products Table Query</TITLE> </HEAD> <BODY> <H2>Access to IDC Conversion</H2> <H3>In order to submit the query: <A HREF="http://localhost/webpub/products_1.idc?"> Click here</A></H3> </BODY> </HTML>

Sample .idc File Datasource:Nwind Template:Products_1.htx SQLStatement:SELECT * FROM [Products] Password: Username:

IDC Dataflow • URL is sent to IIS via HTTP. • HTTPODBC.DLL is loaded with information from the URL. • IDC files are mapped to HTTODBC.DLL and the IDC is provided from the URL passed to IIS. • HTTPODBC.DLL reads the IDC file.

IDC Dataflow - Cont’d • HTTPODBC.DLL connects to the ODBC data source. • SQL statements are sent to the SQL server via the ODBC driver. • HTTPODBC.DLL receives data from the database and merges it into the .htx file. • HTTPODBC.DLL sends mereged document back to IIS which then send results to client browser.

Database Tools • Development tools are usually built around the selected database. • If Access is to be used, then you would use the Access table and query designer. • Sometimes tools supplied are not adequate for Web databases. • A work around for windows is to use Visual InterDev.

Database Schema's • Building a database for Web usage is similar to the traditional method. • Some considerations: • Keep lookup tables small. • Don’t specify validation rules in the database. • Once the database is designed, create the ODBC DSN.

Access Server Specific • Access can create .idc and .htx files for you. • Enter the query in access and then view the results. • Once the results are displayed, inform Access to SAVE As HTML and the files are created. • Disadvantage to this is that the SQL statements are static. • Only use if you are going to place links for popular and unchanging query.

Stored Procedures • Access does not support stored procedures. • Advantage to stored procedures is that they reside on the database server and thus can be optimized. • This allows the web client to simply call the stored procedure in the database.

HTX Files • All .htx files have a section similar to: <%begindetail%> <%first_name%>: <%last_name%> <%enddetail%> • If statements: • <%if condition%> HTML text goes here <%else%> More HTML text <%endif%>

HTX Comparison Operators • EQ - equals • LT - less than • GT - greater than • CONTAINS - If any part of a string contains the search parameter.

HTX variables • MaxRecords and CurrentRecord can only be used in <%if%> statements. • CurrentRecord variable contains the iterations through the <%begindetail%> section. • Parameters from .idc files can be accessed as follows: • <%idc.birth_date%>

Parameter Passing • In HTML File: <SELECT MULTIPLE NAME=‘region” <OPTION VALUE=“West”> <OPTION VALUE=“East”> </SELECT> • In IDC File: SQLStatement: SELECT name, region +FROM Customer +WHERE region IN (‘%region%’)

Parameter Passing to IDC Datasource:Nwind Template:boxidc.htx SQLStatement: + SELECT Count(Orders.OrderID) + AS TotalNumberOfOrders, + Month([OrderDate]) AS RetMonth, + Year([OrderDate]) AS RetYear + FROM Orders + WHERE (((Month([OrderDate]))=%Month%) + AND ((Year([OrderDate]))=%Year%)) + GROUP BY + Month([OrderDate]), Year([OrderDate]);

Parameter Passing to HTX Header stuff omitted: <BODY> <H2>Northwind order count as of month = <%RetMonth%>, and year = <%RetYear%></H2> <B>Total Number of Orders = </B> <%TotalNumberOfOrders%> <P>Date of this query: <SCRIPT LANGUAGE="VBS"> RightNow = Now Document.Write RightNow </SCRIPT> </BODY>

Updating Database Records • Adding, deleting, updating, and appending is similar to the queries we have already looked at. • Main difference here is that the SQL in the .idc file changes. • Sample SQL code for an insert is as follows: • SQLStatement: + INSERT INTO Shippers + (CompanyName, Phone) + VALUES(`%CompanyName%`, `%Telephone%`);

INTERNET DATABASE CONNECTOR