1 / 23

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

buffy
Download Presentation

INTERNET DATABASE CONNECTOR

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. INTERNET DATABASE CONNECTOR Colorado Technical University IT420 Tim Peterson

  2. 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.

  3. 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

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. DSN Definition

  9. 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>

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

  11. 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.

  12. 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.

  13. 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.

  14. 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.

  15. 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.

  16. 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.

  17. 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%>

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

  19. 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%>

  20. 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%’)

  21. 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]);

  22. 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>

  23. 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%`);

More Related