8.6. Using ASP to Query a Database and Display Results

OPC DataHub™ : Version 6.4
	Chapter 8. OPC to Web

Using the OPC DataHub'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 the DataHub 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 Submit button, the page sends the query to the DataHub, 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\Cogent\OPC 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>


8.5. Modifying the ASP and AJAX Demo Files		8.7. Creating Passwords