Chapter 16

Database Connectivity

CONTENTS

FrontPage 97 allows you to integrate with ODBC-compliant databases and integrate with your database files dynamically. Using FrontPage and Microsoft Internet Information Server or the Microsoft Personal Web Server, corporations can create forms that retrieve ordering information, survey information, customer feedback, project data, and more. The FrontPage Database Connector Wizard can help generate Internet Database Connector files, which allow Web pages to interface with the data on the corporation's Internet or intranet site. This chapter gives an overview of creating Internet Database Connector files and query results files.

Creating Your Form

When you design a form for use with the Internet Database Connector Wizard, you design it like any other. You should note the names of the form fields that you use in the form, however, because you'll need to use the same form field names when you create your IDC file with the Database Connector Wizard.

The form shown in Figure 16.1 will be used with the Database Connector Wizard. As you can see, the form isn't any different than the others you see in FrontPage. The form contains the following field names (from top to bottom): FirstName, LastName, Your E-Mail, Your Web Site URL, and Add your Comments. These field names will be referenced in the database results file and when using the Database Connector Wizard. If the data entered into your form must meet certain criteria, you can use form field validation to assign the criteria to the form field.

Figure 16.1 : Any form can be used with the Internet Database Connector.

After you design your form and configure the database results file, you generate an IDC file using the Database Connector Wizard. You assign this IDC file as a form handler in the Form Properties dialog box as shown in Figure 16.2.

Figure 16.2 : The IDC file that you create is used as a form handler and assigned in the Form Properties dialog box.

Creating a Database Results File

In order for the form shown in Figure 16.1 to work correctly with the IDC file, it needs a special kind of results file. Here's where the procedures start to differ from forms used with the standard FrontPage form handling bots discussed in Chapter 22, "Runtime Bots-The Heartbeat of FrontPage Forms." The results file used with IDC-related forms must be saved to your web with an .htx extension. In addition, you enter SQL query data into the page with commands found in the Edit | Database menu of the FrontPage Editor. An example of a simple database results file is shown in Figure 16.3.

Figure 16.3 : This simple database results file thanks the user for signing the guest book. If a person by that name has already registered, the user is informed as such.

You begin to create a database results file as follows:

  1. From the FrontPage Editor, choose File | New (Ctrl+N). The New Page dialog box appears.
  2. From the Template or Wizard field, select Database Results and click OK. A new database results page appears.
  3. Add Detail Sections, Database Column Values, IDC Parameter Values, and if-then-else statements as outlined in the following steps.
  4. Save the file with an .htx extension. You will enter this filename in the Query Results Template field in the first panel of the Internet Database Connector Wizard.

Adding a Detail Section

A detail section is HTML that is returned once for each record that is returned as a result of a database query. A detail section is marked by a pair of detail section markers, which you can see at the beginning and end of the data contained in the second cell of the table shown in Figure 16.4. Detail sections can contain other database elements such as database column values, IDC parameters, and if-then-else statements. To create a detail section, follow these steps:

Figure 16.4 : Detail section markers define an area of the database results file that is returned once for each record returned by a database query.

  1. Create or open your database results file. This file should have an .htx extension.
  2. Select the region on your web page that you want to define as a detail section.
  3. Choose Edit | Database | Detail Section. Detail section markers appear on your page, with the HTML you selected between the markers.
  4. Click OK.

Adding Database Column Values

Database Column Values insert values from one column in a database record into a database results file. The column names must agree with the columns set in your ODBC database table. In addition, they must be contained within a detail section. Figure 16.4 shows several database column value entries in the database results file. Database column values are surrounded by brackets and percentage signs when they appear on your page (example: <%FirstName%>).

To display a database record column value, follow these steps:

  1. Create or open your database results file. This file should have an .htx extension.
  2. Position the insertion point where you want the database column value to appear.
  3. Choose Edit | Database | Database Column Value. The Database Column Name dialog box shown in Figure 16.5 appears.
    Figure 16.5 : Enter the database column value in the Database Column Name dialog box.

  4. In the Database column name field, enter the name of a column in the database record associated with the current database results file.
  5. Click OK. The database column name appears on your page, surrounded by brackets and percentage signs.

Adding an IDC Parameter Value

An IDC parameter value can be the name of one of the form fields used to compose a database query, or a default parameter specified in an IDC file. For example, the results file shown in Figure 16.6 returns the first name and last name of a single entry in a guest book.

Figure 16.6 : IDC parameters are added to the page in the IDC Parameter Value dialog box.

To display an IDC parameter, follow these steps:

  1. Create or open your database results file. This file should have an .htx extension.
  2. Position the insertion point where you want the IDC parameter value to appear.
  3. Choose Edit | Database | IDC Parameter value. The IDC Parameter Value dialog box shown in Figure 16.6 appears.
  4. In the "Parameter name" field, enter the name of the IDC parameter to display, for example FirstName.
  5. Click OK. The IDC parameter name appears on your page, surrounded by brackets and preceded by idc (example: <%idc.FirstName%>).

Adding an If-Then Conditional Section

You use an If-Then conditional section to display a portion of an .htx file if a Boolean expression is true. The FrontPage Editor marks the beginning of an If-Then conditional statement with an If-Then condition marker, and its end with an End condition marker. You can add any content within the section after it is defined. In the example shown in Figure 16.3, the If-Then markers surround the text that reads Thanks for signing the guest book!.

To create an If-Then conditional section, follow these steps:

  1. Create or open your database results file. This file should have an .htx extension.
  2. Select the page contents that you want to display if a Boolean expression evaluates as true. In the case of the example shown in Figure 16.3, the text that reads Thanks for signing the guest book is highlighted.
  3. Choose Edit | Database | If-Then conditional section.
  4. Click OK. The If-Then Conditional Section dialog box shown in Figure 16.7 appears.
    Figure 16.7 : You define your If-Then statement in the If-Then Conditional Section dialog box.

  5. The purpose here is to define a Boolean statement that says "If the First Item relates to the Second Item in the condition I select, then display the text I just highlighted." In the First Item section, select the type of data you are using to define the first part of the Boolean expression from the Type drop-down menu.
    Database Column Value specifies the value of a database column.
    IDC Parameter Value specifies the value of an IDC parameter. This is usually a value used by the query.
    Constant Value specifies a constant value, which can be a number or a string.
    CurrentRecord always equals the number of records returned by the current database query. If no matching sections are found, the value is zero. No value is required.
    MaxRecords is the maximum number of records that can be returned from a database query. No value is required.
    HTTP Variable specifies the value of an HTTP variable.
  6. If the First Item type is anything other than CurrentRecord or MaxRecords, enter a value for the first item in the First Item's Value field.
  7. Select a comparison operator from the Condition drop-down menu. Available choices are
    Equals. The value of the first item should equal the value of the second item to equate as true.
    Less Than. The value of the first item should be less than the value of the second item to equate as true.
    Greater Than. The value of the first item should be greater than the value of the second item to equate as true.
    Contains. Any part of the value of the first item should be contained within the value of the second item to equate as true.
  8. To define the second item in the Boolean expression, select the type of data from the Second Item's Type drop-down menu. The available choices are the same as those listed in step 5 for the first item.
  9. If the Second Item type is anything other than CurrentRecord or MaxRecords, enter a value for the second item in the Second Item's Value field.
  10. Click OK. The If-Then markers appear on your page, surrounding the data that you selected.

Adding an Else Conditional Section

You add an Else conditional section to display a section of the page when your If-Then conditional section (described in the previous task) equates to false. In Figure 16.3, the query results file returns the FirstName and LastName IDC parameters if a user is already registered in the database. All content between the Else marker and the End Condition marker is displayed when the condition is false.

You can only add an Else conditional section inside an If-Then conditional section. To add an Else conditional section, follow these steps:

  1. Position the insertion point inside an If-Then conditional section inside an .htx file.
  2. Choose Edit | Database | Else Conditional Section. An Else Conditional Statement marker appears on your page. Enter the data that you want to display before the marker.

Removing Database Directives

When you delete database directives, all IDC parameter values, database column values, beginning and ending markers for detail sections, and beginning and ending markers for conditional statements are removed.

To remove database directives from a database results file, follow these steps:

  1. Select the region that contains one or more database directives.
  2. Choose Edit | Database | Remove Database Directive.
  3. Click OK. The directives are removed from the selected area.

Using the Internet Database Connector Wizard

Before you use the Database Connector Wizard, your server administrator will need to set up and configure the ODBC data source file on your server. Setup will vary depending on the ODBC driver that you use. To use the Database Connector Wizard, you'll need to know the name of the ODBC data source, a username and password required to log on to the data source (optional), and the name of a query results template that will be used to handle the data received from the query.

If you are using Windows 95, the Internet Database Connector requires the use of the Microsoft Personal Web Server, furnished with the FrontPage Bonus Pack. Do not install the Microsoft Personal Web Server if you are running Windows NT.

Creating an IDC File

An Internet Database Connector (IDC) file works with Open Database Connectivity (ODBC) compliant databases. ODBC is a standard that is used to access different database systems. Structured Query Language (SQL) queries are used to communicate with any ODBC compliant database that is accessible from the Web server. Using these queries, you can extract information from the database. The connection information is specified in the IDC file.

The IDC file also specifies a query results template file in your FrontPage web, which traditionally use an .htx extension. The .htx file is a Web page that contains special parameters, variables, and control information that relate to your SQL database queries. IDC files contain the following types of information, which control the data that is displayed at runtime:

How to connect to the database.
The SQL query to execute.
The name of the .htx file used to process and display the results of the query.
Database record column values.
IDC Parameter values.
If-Then-Else statements.

You use the Internet Database Connector Wizard to create an Internet Database Connector (IDC) file. The SQL query and database connection information is contained in an IDC file. The IDC parameters that you define in the Database Connector Wizard should agree with those that appear on the form you design. In the case of the example covered in this chapter, use the same names as the form shown in Figure 16.1.

To create an IDC file, follow these steps:

  1. From the FrontPage Editor, choose File | New (Ctrl+N). The New Page dialog box appears.
  2. From the Template or Wizard field, select Database Connector Wizard.
  3. Choose OK. The Database Connector Wizard opens.

NOTE
You can also start the Database Connector Wizard by clicking the Database Connector Wizard button on the Advanced toolbar.

Specifying your ODBC Data Source

In the first screen of the Database Connector Wizard, shown in Figure 16.8, you specify your ODBC data source, a username and password (if required), optional advanced options, and the name of the query results template that will be located in your web. To complete the screen, follow these steps:

Figure 16.8 : In the first screen of the Database Connector Wizard, you specify your data source, advanced options, and query results template.

  1. In the ODBC data source field, enter the data source to which you want to connect, as configured by the ODBC or Web system administrator.
  2. If your database system requires a username and password for access, check the Username box and enter the name of the user. If a password is required, check the Password field and enter the password.
  3. To set optional advanced options, click the Advanced Options button. For more information, see "Specifying Advanced Options," later in this chapter.
  4. In the Query results template field, enter the name of the file that will be used to format and display the query results data for the IDC file. This is a required field. Click the Browse button to locate the folder and file in your current web. The URL you enter here should be relative to the location of the IDC file. In addition, the file should have an .htx extension. After you select your file from the Current Web dialog box, click OK to return to the Database Connector Wizard.
  5. Click Next to continue with the FrontPage Internet Database Connector Wizard.

Inserting SQL Queries

In the second screen of the Internet Database Connector Wizard, shown in Figure 16.9, you enter your SQL queries. The query is entered into the data field provided in the dialog box. Where a database parameter name is required, insert the parameter name with the Insert Parameter button.

Figure 16.9 : In the second screen of the Database Connector Wizard, you add query information into the IDC file.

To insert queries, follow these steps:

  1. Enter the text for your query in the query field. Consult your ODBC driver documentation for additional information on the parameters that the ODBC driver can accept.
  2. To insert a database parameter name into the query, click the Insert Parameter button. The Insert Parameter dialog box shown in Figure 16.10 appears.
    Figure 16.10 : Enter the parameter name and value in the Insert Parameter dialog box.

  3. In the Form field name field, enter the name of the form field that will be used to retrieve the information for the parameter.
  4. Choose OK. You return to the wizard screen, and the form field name appears in the SQL query list, surrounded by percentage signs.
  5. Add additional parameters as necessary. If you click the Clear Query button, all queries are removed from the query list.
  6. To add another query to the same IDC file, click the arrow on the SQL Query drop-down menu, and select Create Additional Query. Repeat steps 1 through 5 as necessary.
  7. When your queries are complete, click Next to continue.

Specifying Default Parameter Values

In the third screen of the Internet Database Connector Wizard, you specify default parameter values. These are parameter values that will always be used whenever the SQL query is run. These parameters are combined with those entered by the user and passed to the form handler. If a user follows a link to the IDC file, only these parameters are used.

Figure 16.11 : Add default parameter values, if required, in the third screen of the Database Connector Wizard.

To specify default parameter values:

  1. Click the Add button. The Add Parameter dialog box appears.
  2. In the Parameter name field, enter the name of the parameter.
  3. In the Default value field, enter the default value for the parameter.
  4. Click OK to return to the wizard screen.
  5. Add additional default parameters as required.
  6. Click Finish to generate the IDC parameter file.

To modify a default parameter value, highlight the parameter you want to modify, and click the Modify button. The Modify Parameter dialog box appears. Edit the Parameter name or Default value and choose OK. You return to the Internet Database Connector Wizard.

To remove a default parameter value, highlight the default parameter name you want to remove, and click the Remove button. The parameter is removed from the list.

Saving Your HTX File

Before you exit the Internet Database Connector Wizard, the Current Web dialog box shown in Figure 16.12 appears. You are prompted to save your IDC file into the current web. You must save the page into a directory into which executable scripts can be stored.

Figure 16.12 : Save your Internet Database Connector File to a directory in your web that allows executable scripts to be run.

To save your .htx file, follow these steps:

  1. In the Current Web dialog box, use the Look in box to locate a directory in your current web that can contain executable files.

NOTE
To determine whether a folder can hold executable scripts, open the FrontPage Explorer. Select Folder View. Select a folder and choose Edit | Properties. The Properties dialog box for the folder appears as shown in Figure 16.13. If the folder is marked Allow scripts or programs to be run, it can hold executable scripts.

Figure 16.13 : Check the Allow scripts or programs to be run option in the folder's Properties dialog box to make an executable directory in your web.

  1. In the Save As field, enter the name of the IDC file, or select a file from the current web. IDC files must have an .idc extension. If no extension is supplied, the Internet Database Connector Wizard adds one.
  2. Click OK. The Database Connector Wizard closes, and you return to the FrontPage Editor.

Specifying Advanced Options

Advanced options in the Internet Database Connector Wizard are optional, and require familiarity with ODBC system administration procedures. Consult your ODBC driver documentation for further recommendations on settings to be used here. You can access the Advanced Options settings from the first screen in the Database Connector Wizard, by clicking the Advanced Options button.

Specifying Query Options

Query options allow you to specify settings for SQL escape sequence scanning, cached results expiration times, query timeouts, transaction isolations, and content type. If your ODBC content also requires foreign character translations, you can specify the translation files here.

To specify advanced query options, select the Query tab in the Advanced Options dialog box, check one or more of the following options, and enter values in the respective data entry fields:

Figure 16.14 : Specify advanced query options using the Query tab in the Database Connector Wizard's Advanced Options dialog box.

Do not scan for SQL escape sequences. Select this option to prevent the ODBC driver from scanning SQL strings for escape clauses. The statements will be sent directly to the data source instead. If your SQL statement does not contain any ODBC escape clauses (enclosed by curly braces), then you will see a small performance gain if you check this option.
Cached results expiration time (sec). Enter the number of seconds to wait before refreshing a cached output page. This is used when you want to force another query of the database after a certain period of time. IDC only caches pages when this field is used.
Query Timeout (sec). Check this option to set the number of seconds to wait for an SQL statement to execute before the query is canceled. If the timeout value exceeds the maximum timeout setting in the data source, the driver substitutes the maximum value. If the query timeout value is less than the minimum timeout setting in the data source, the driver substitutes the minimum value.
Transaction isolation. Check this option to set the transaction isolation level. This setting allows you to retrieve data that has not been committed to the database by other transactions.
Content type. Check this option to specify the type of content that will be contained in the POST request. The default is text/html.
HTML Translation File. Check this option if your database is in a language other than English. A translation file maps each special character in the following format: value=string<CR>, where value is an international character, and string is the HTML translation code. Enter the full path to the file that maps non-English characters so that Web browsers can display them properly in HTML format.
ODBC Translation DLL. Check this option to enter the name of a DLL that contains the functions SQLDriverToDataSource and SQLDataSourceToDriver. These functions are used to perform tasks such as character set translation.
ODBC Translation Option. Check this option to specify a value that controls translation functionality, specific to the translation DLL being used. You will need to consult the documentation for the ODBC driver and translation DLL for details.

Specifying Connection Options

Connection options allow you to enable read-only access, to specify a logging file of OBDC calls, to turn ODBC connection pooling on or off, to specify an SQL logon timeout value, and to specify a network packet size.

To specify advanced connection options, select the Connection tab in the Database Connector Wizard's Advanced Options dialog box. Choose one or more of the following options, and enter values in their respective data entry fields.

Figure 16.15 : Select your ODBC connection options in the Connection tab of the Database Connector Wizard's Advanced Options dialog box.

Read-only access. Checking this option will prevent the IDC connection from changing the data in the data source that it connects to.
Enable logging of ODBC calls. Select this option to log each function call made by the ODBC driver. To use a logging file other than a default name of sql.log, enter a name in the Log File field.
ODBC connection pooling. Select this option and choose On to keep the connection to the data source open in between data requests.
SQL logon timeout (sec). Check this option to specify the number of seconds to wait for a logon request before disconnecting, and enter a value in seconds in the timeout field. If you enter a value of zero, the timeout is disabled and waits indefinitely.
Network packet size (bytes). Check this option to specify the network packet size. This option is not supported by some data sources.

Specifying Limits Options

Use the Limits tab in the Advanced Options dialog box, shown in Figure 16.16, to specify advanced settings for ODBC driver limits and IDC limits.

Figure 16.16 : Specify ODBC driver and IDC limits in the Limits tab of the Advanced Options dialog box.

To specify ODBC driver limits options, use the ODBC Driver Limits section in the Limits tab.

Maximum field length (bytes). Check this option to specify the maximum amount of data returned from a character or binary column. This should only be used when the data source in a multiple-tier driver can implement it.
Maximum number of data rows returned. Check this option to specify the maximum number of rows returned for a SELECT statement. All rows are returned by default.

Use the IDC Limits section in the Limits tab to specify the following Internet Database Connector options:

Maximum displayed field length. Check this option to specify the maximum buffer space allocated per field by the IDC file. Any characters beyond this space are truncated. The default value is 8192 bytes.
Maximum number of data rows fetched. Check this option to limit the number of records returned from one query. No value is specified by default, so a query can return up to four billion records.

Specifying Driver Specific Options

To specify driver-specific options, use the Driver Specific tab in the Advanced Options dialog box of the Database Connector Wizard. You will need to refer to your ODBC driver documentation for descriptions of available options.

Figure 16.17 : Use the Driver Specific tab in the Advanced Options dialog box to specify driver-specific options.

To specify driver-specific options, follow these steps:

  1. Click the Add button. The Add Option dialog box appears.
  2. Specify the option number and value and click OK to return to the Driver Specific tab. The option and value appear in the ODBC driver specific options field.
  3. To modify an existing value, click the Modify button. The Modify Option dialog box appears. Specify a new option number or value and click OK to return to the Driver Specific tab.
  4. To remove an existing value, highlight an option and click Remove. The option is removed from the list.

Workshop Wrap-Up

Using FrontPage and the Internet Information Server or Microsoft Personal Web Server, you can create dynamic interfaces with any ODBC-compliant database. Your data can be maintained and updated in real time over the Internet. It can be used to track orders, keep a record of all the people that have visited your Web site, maintain project records, and more. You can use FrontPage to develop the files and pages required to process the data dynamically.

Next Steps

If you're the type of person who likes to get into the heart of the matter, the next chapter shows you how to add your own HTML code into your FrontPage Web pages. For more topics that relate to database connectivity, refer to the following chapters:

Q&A

Q:Can I just enter the Database Column Values and IDC Parameter Values on my page surrounded by brackets and percentage signs?
A:No, you must use the Edit | Database Commands to do so. Special codes are added to your HTML source code that define these items as database column values and IDC parameter values.