FrontPage - Personal advice | Traps and Lessons Learned | FP, Oracle,
ASP | FP Wish List | Faughnan
Rev: 01 Nov 2004.
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
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
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.
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.
- 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.
- 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
- 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!
- 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
- 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:
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
- 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.
- 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).
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.
- 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
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.