FrontPage - Personal advice | Traps and Lessons Learned | FP, Oracle, ASP | FP Wish List | Faughnan Home

FrontPage 98, ASP, IIS 4, ODBC, SQLNet and Oracle

  • Introduction
  • ODBC Lock-Ups
  • SQLNet and DSN
  • ODBC Data Source Name (DSN) and Drivers
  • Internet Information Server 4 (IIS 4)
  • FrontPage 98
  • Troubleshooting
  • Links
  • History
  • Footnotes
  • Rev: 01 Nov 2004.


    Introduction

    Notes on getting FrontPage, ODBC, SQLNet, Oracle, IIS4, TCP/IP and ASP to play nice together. I wrote this in May of 1999, a bit of it is obsolete but it's mostly still correct.

    FrontPage 98 generates the Active Server Page code. IIS 4 interprets the ASP code, and passes a SQL query to ODBC. ODBC talks to SQLNet who talks to Oracle via TCP/IP. And then back.

    Read all these notes before you start! All the pieces must fit together.

    ODBC Lock-Ups

    IIS 4.0, FP 98 and ODBC combine to produce a very frustrating development environment. I found that ill-formed SQL would lock my system out of communication with Oracle. After one failure all queries would fail until I rebooted. I never found any other way to clear this state -- it seemed to be blocked at a very low level of the system. I think, however, that IIS 5 may be less vulnerable.

    SQLNet and Data Source Name (DSN)

    This was written for Oracle 7. With later versions of Oracle it's possible for a network administrator to set things up so that the DSN (data source naming service) is not required -- basically the network DNS resolves a string to the data source. Otherwise this is still true. Oracle 8's setup tools for configuring the DSN have also changed, but they do the same thing.

    1. QL*Net is an Oracle native protocol running on top of TCP/IP, IPX/SPX or other local network protocol. ODBC connects to Oracle using SQL*Net.
    2. Assuming the Oracle administrator has everything setup on the server end, this is the first step you talk.
    3. SQLNet defines the Data Service Name (DSN).
    4. Enter the Alias and Instance names: in my setup they are identical. These are defined by your Oracle DBA.
    5. Enter the TCP/IP host name in standard notation.

    ODBC Data Source Name (DSN) and Drivers

    1. Use Oracle's tools to define the Data Source Names (DSN) -- see SQLNet and Data Source Name (DSN)
    2. Use Microsoft's ODBC administrator to create a System reference to the DSN you defined above. IIS uses the system DSNs, not the User DSNs. (The DSN must have been previously defined using SQLNet, see below).
    3. I had success with Microsoft OracleODBC drivers. I'm read that only the very latest versions of Oracle's ODBC drivers will work.
    4. In the Microsoft Oracle ODBC configuration, do NOT enter the true server name. Instead where it requests server name, enter the DSN defined in SQLNet. The DSN name is entered on this screen in two places: once in the Data Source Name field, and once in the Server field.
    5. Test your setup with Oracle's 32-bit ODBC test  and a simple database query. This tests the User DSN, not the System DSN.

    Internet Information Server 4 (IIS 4)

    FrontPage 98

    1. FP 98 will generate the Active Server Page (ASP) for you. The simplest ASP page runs and displays a query when you click on a link that references it. More complex pages can accept parameters. The default way of passing parameters is by PUT, but you can edit the pages to do a GET instead.  (see below)
    2. Be careful to save your ASP page with the right extension. If you rename it to .HTM/.HTML you get an empty result with no error message!
    3. When testing out with a very simple query; use the same SQL you used in your OBDC test (see clean SQL code).
    4. FrontPage 98 defaults to the PUT method for web-database interaction (see below).
    5. The Database Region Wizard (Insert:Database) will create the ASP code. It needs:
    6. The resulting code contains the statement: fp_sValue = Request.Form(fp_sField). This will work with a Form that uses the PUT method. To work with the GET method, you must open the ASP file with a plain text editor, not FrontPage editor, and manually edit this string to read: fp_sValue = Request.Querystring(fp_sField). Every time you edit this page with FP Editor, you will have to repeat this change. The advantage of the GET method is that you can embed queries in a URL, and thereby turn a hyperlink into a stored query. (Disadvantages are low security, and query size limitations)
    7. If a cell has no data, FP seems to omit the <DT></DT> tags, producing a somewhat stupid looking table.
    8. You can't edit the result of an ASP query as rendered by IE 4/5.0 in FrontPage; FP attempts to interpret the embedded webbots. See disabling webbots. You can however view the page in IE 5.5, select all, and paste into FP 98.

    Troubleshooting

    1. I had the best luck with searching DejaNews or AltaVista using the error codes as search terms.
    2. Microsoft's tech support and developer libraries were of limited value.
    3. Use Oracle's SQL Plus tool to test your SQL queries before you put them into FrontPage 98 (see above).

    Links

    These links point to external resources. If you hunt around the various folders installed with IIS, either on the CD or after an installation that includes documents, there's a surprising amount of material hidden away.

    ASP

    FrontPage

    IIS

    History

    Footnotes

    [1]
    [2]

    Author: John G. Faughnan.  The views and opinions expressed in this page are strictly those of the page author. Pages are updated on an irregular schedule; suggestions/fixes are welcome but they may take weeks to years to be incorporated. Anyone may freely link to anything on this site and print any page; no permission is needed for citing, linking,  printing, or distributing printed copies.