12.3. Using ASP to Query a Database and Display Results
Using DataHub program's ODBC
scripting functionality, it is possible to send an SQL query from a web
page to a database, and view the results in the page. The
dbquery.asp demo page included in the DataHub distribution
gives an example of how this is done. With a DataHub instance running on your local
machine, you can view this page by typing localhost/dbquery.asp
into your web browser:
You simply enter a DSN, user name, and password (if any) for the database, and an SQL query. When you press the button, the page sends the query to the DataHub instance, which passes the query to the database, and returns the results, displaying them in the table on this page.
The source file for this page is included with all the other ASP files here in your DataHub distribution:
C:\Program Files (x86)\Cogent\Cogent DataHub\Plugin\Webserver\ html\dbquery.asp C:\Program Files\Cogent\Cogent DataHub\Plugin\Webserver\ html\dbquery.asp
Here is a copy of the page source:
<html>
<head>
<%
/*
* This file presents an entry form for the user to specify a
* DSN, user name, password and SQL query to be executed by the
* DataHub. The result is displayed as a table in the user's
* browser.
*
* Normally, the DSN, user name and password would be hard-coded
* into the Gamma portion of this ASP file. The ASP file is
* stored on the server running the DataHub, and all Gamma code
* is stripped from the file and executed before the result is
* returned to the user. Therefore the DSN, user name and
* password are never transmitted across the network to the user.
*/
require ("AJAXSupport");
require ("ODBCSupport");
local cmpfn, getArg, sortfn;
/* Set up some function for quickly accessing the URL arguments */
function cmpfn(x,y) { strcmp(x[0],y); }
function sortfn(x,y) { strcmp(x[0],y[0]); }
function getArg(name, dflt)
{
local arg = bsearch(_vars, name, cmpfn);
if (arg && !undefined_p(car(arg)))
arg = car(arg)[1];
else
arg = dflt;
arg;
}
_vars = sort(_vars,sortfn);
/* Read the input URL arguments */
local DSN = getArg("DSN","Enter DSN");
local Password = getArg("Password","Enter Password");
local Username = getArg("Username","Enter Username");
local Query = getArg("Query","");
/* Connect to the database */
local env, conn, Connect, DoQuery;
function Connect ()
{
local ret;
/* Create the ODBC environment and connection */
env = ODBC_AllocEnvironment();
conn = env.AllocConnection();
/* Attempt the connection. */
ret = conn.Connect (DSN, Username, Password);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
error (conn.GetDiagRec());
}
function DoQuery()
{
local result = conn.QueryToTempClass(nil, Query);
}
%>
</head>
<body>
Enter the database information:
<form>
DSN: <input type="text"
id="DSN" name="DSN" value="<%= DSN %>">
User: <input type="text"
id="Username" name="Username" value="<%= Username %>">
Pass: <input type="password"
id="Password" name="Password" value="<%= Password %>">
<br>
<br>
Enter the database query to perform and press Submit:
<br>
<textarea name="Query" wrap="logical" rows="10" cols="80">
<%= Query %></textarea>
<br>
<input type="submit" id="Submit" value="Submit">
</form>
<%
try {
if (Query != "")
{
Connect();
local result = DoQuery();
if (!result || length(result) == 0)
{
}
else
{
local first = result[0];
%> <div style="overflow: auto; width: 660; height: 300"> <%
%> <table border="1"> <%
%> <tr> <%
with var in instance_vars(first) do
{
%> <th><%= car(var) %></th> <%
}
%> </tr> <%
with row in result do
{
%> <tr> <%
with var in instance_vars(row) do
{
%> <td><%= cdr(var) %></td> <%
}
%> </tr> <%
}
%> </table> <%
%> </div> <%
}
}
} catch {
%>
A problem occurred while running server-side scripts
on this page:<br>
<%= _last_error_ %>
<%
}
/* Do a little cleanup. The garbage collector would eventually
get to this, but we can be kind and unwind. */
if (conn)
{
conn.Disconnect();
destroy (conn);
}
if (env)
destroy (env);
%>
</body>
</html>