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.

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.

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.
Assuming the Oracle administrator has everything setup on the server end, this is the first step you talk.
SQLNet defines the Data Service Name (DSN).
Enter the Alias and Instance names: in my setup they are identical. These are defined by your Oracle DBA.
Enter the TCP/IP host name in standard notation.

ODBC Data Source Name (DSN) and Drivers

Use Oracle's tools to define the Data Source Names (DSN) -- see SQLNet and Data Source Name (DSN)
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).
I had success with Microsoft OracleODBC drivers. I'm read that only the very latest versions of Oracle's ODBC drivers will work.
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.
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)

Supports Active Server Pages. There's a free 'personal' (limited connects) version for NT 4/SP3. Be careful not to download the server version, and to specify only IIS 4. This is a big and tedious download; I had to get 28 or so files in sequence.
Need to turn on script access in the folder where the ASP page lives using FrontPage Explorer.
When installing IIS, include the documentation if you have room!
IIS creates a FrontPage 98 folder. I think this might be the best place to install the complete FrontPage 98 into, but I'm not sure!

FrontPage 98

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)
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!
When testing out with a very simple query; use the same SQL you used in your OBDC test (see clean SQL code).
FrontPage 98 defaults to the PUT method for web-database interaction (see below).
The Database Region Wizard (Insert:Database) will create the ASP code. It needs:
- clean SQL code
  - develop the SQL on the DBMS you will be using. In Access you can do this by using the 'pass-through' SQL query feature. If you're going against an Oracle DBMS, regular Access SQL won't work (see below).
  - SEMICOLONS: If you've ever worked directly with Oracle (SQL Plus) you know Oracle wants SQL statements to end with a semicolon. I'm not sure, but I think that whether or not you need a semicolon after the sql you enter in FrontPage depends on what ODBC driver you use and possibly which version it is. Try it both ways. I think Microsoft's ODBC drivers don't want the semicolon -- you may get an Oracle error about an illegal character.
  - Watch table names: Oracle allows table names that Access does not. In particular, Oracle uses a period (.) as a separator in schema.table_names, but Access uses an underscore. You might get an error like: [Oracle][ODBC][Ora]ORA-00903 invalid table name. The solution is to find out what the TRUE table names are on the Oracle server and use those. The name you need is the one that works with Access' pass-through SQL.
  - The SQL must be one continuous string without carriage returns or line breaks. I think if you use the 'paste from clipboard' feature that FP may strip out the CR/LF characters, but if you paste directly they may mangle things.
- an ODBC data source name (DSN) and your ODBC data source username and password (same as used in Oracle's SQLPlus or with Access). Your username and the DSN, are stored in clear text in the ASP file.
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)
If a cell has no data, FP seems to omit the <DT></DT> tags, producing a somewhat stupid looking table.
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

I had the best luck with searching DejaNews or AltaVista using the error codes as search terms.
Microsoft's tech support and developer libraries were of limited value.
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

Feb 10, 2001: Some partial updates.
May 28, 1999: More comments on setting up SQL for use in FP 98.
Feb 19, 1999: fixed SQLNet description. Thanks to SZ.
Feb 16, 1999: more comments about table name errors.
Feb 15, 1999: added links, some updates.
Feb 11, 1999: original version

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.

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