The customer does not have a webserver to serve as a
transaction engine, so the ASP route is a no-go. However, it occurred to me
that I can just connect straight from the browser to the DB. And since I can
use Javascript to write other Javascript (thank you, innerHTML), there's not
much of a paradigm shift.
I decided to use Javascript instead of VBScript. Portability
wasn't an issue (as use of ADO already restricted clients from using Firefox),
but I consider Javascript to be a much cleaner language, and I am more
productive when using it. Throughout, I assume fluency in Javascript, ADO, and SQL. There are plenty of good primers for all three languages; the scope of this
article is merely how to integrate them in one particular way.
Reinventing the Wheel
A key aspect of the traditional web
application paradigm is that the server-side script is a program that writes
another program. The server script creates a document containing markup and
client-side script which executes when received by the client. This allows
database content to easily and directly modify the program (as received by the
user) directly. We no longer have the clean distinction between server- and
client-side programs, but thankfully a browser-based Javascript application can
re-write itself thanks to the innerHTML property of many DOM objects.
A common task in web applications
is to display a drop-down list from data in the database. For example, we might
have a Rank table with three fields: a unique ID number, a textual ShortName,
and a textual LongName. An example of a record might be (12, "LtCol",
"Lieutenant Colonel"). The select entity (the HTML implementation of a list
box) contains option entities, each of which has a value and text. We want to
generate options whose text corresponds to ShortName and value corresponds to
ID. We can easily write a function to generate the options:
function gen_list_options(query, id, val) {
var html = ''
var rs = new ActiveXObject("ADODB.Recordset")
rs.open(query, conn, adOpenDynamic, adLockOptimistic)
if(!rs.bof) {
rs.MoveFirst()
while(!rs.eof) {
html += '<option value="' + rs.fields(id).value +
'">' + rs.fields(val).value + '</option>'
rs.MoveNext()
}
}
rs.close()
return html
}
Why only generate the options? Why not generate the whole
select entity? The reason is simple: this gives us more flexibility. We don't
have to worry about passing parameters for all the various properties of the
select itself, and we are free to add option elements before or after our
auto-generated ones. For example
var html = ''
html += '<p><span
id="rank_p">Rank</span> <select
id="trainer_rank">'
html += '<option
value="0" />'
html +=
gen_list_options("SELECT ID, ShortName FROM Rank", "ID",
"ShortName")
html +=
'</select></p>'
I chose to prepend a "blank" option
which allows our form to not indicate a particular rank while in its default
state.
The gen_list_options() function
illustrates some key points. First, for those of you familiar with VBScript,
you'll notice that in place of the canonical
set rs =
CreateObject("ADODB.Recordset")
we have instead
var rs = new
ActiveXObject("ADODB.Recordset")
The functionality is equivalent but
representative of the differing styles between the languages. The call to the
recordset's open() method is similar to what you'd see in VBScript. conn is, in
my application, a global object which we'll get to in a moment. The last two
arguments are ADO constants whose values are 2 and 3, respectively.
In VBScript, read-only properties and zero-argument methods are largely indistinguishable. In Javascript, however, one must be keenly aware of the difference; for example that MoveFirst() and MoveNext() are methods while eof is a property.
This seems fairly arbitrary to me, but it's what we have to work with.
The connection object is created
very similarly to how one would create it in VBScript.
var conn = new
ActiveXObject("ADODB.Connection")
conn.open(conn_str, "",
"")
conn_str is, of course, a string
containing a DSN name or a connection string for a database provider.
First Principles
Now that we have some markup
generated with the help of the database, we need to display it. The easiest way
to do this is to set the innerHTML property of some element (say, a div). For
example,
<body
onload="show_list()">
<div id="main"
/>
</body>
Now, we just make surround our
markup-generation code in a function called "show_list()":
function show_list() {
var html = ""
// as before
document.getElementById("main").innerHTML
= html;
}
And voila! The drop-down list
appears. Later, the innerHTML may be re-written in response to button clicks
and other DOM events.
Second Principles
To transfer data between form elements and the database, I
chose to use an associative array as an intermediary. This extra level of
abstraction allows us to represent the data as a single entity in a form
convenient for manipulation. Two functions facilitate this.
function rs2arr(rs, arr) {
for(var i=0; i!= rs.fields.count; ++i)
arr[rs.fields(i).name] = rs.fields(i).value;
}
rs2arr() is a simple function that
reads (field_name, value) pairs for each field of a record in the database and
stores it in an associative Javascript array. Its companion function, arr2rs is
similarly simple.
function arr2rs(arr, rs) {
for(var f in arr)
if(rs.fields(f))
rs.fields(f).value = arr[f];
}
arr2rs() performs the opposite
function, with the important distinction that before writing a value to the
database, it first checks to make sure that the field exists. Often, a single
form will update more than one table, in which case a single recordset cannot
store all the data in the associative array.
Putting it all together
Here I present a simple application
using the techniques described above.
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&
gt;
<html
xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"
lang="en">
<head>
<script
type="text/javascript">
<!--
var adOpenDynamic = 2
var adLockOptimistic = 3
var conn_str = "..."
var conn = new
ActiveXObject("ADODB.Connection")
conn.open(conn_str, "",
"")
function get_selected_value(sel) {
return
sel.options[sel.options.selectedIndex].value;
}
function gen_list_options(query,
id, val) {
var html = ''
var rs = new ActiveXObject("ADODB.Recordset")
rs.open(query, conn,
adOpenDynamic, adLockOptimistic)
if(!rs.bof) {
rs.MoveFirst()
while(!rs.eof) {
html += '<option
value="' + rs.fields(id).value + '">' + rs.fields(val).value +
'</option>'
rs.MoveNext()
}
}
rs.close()
return html
}
function rs2arr(rs, arr) {
for(var i=0; i!= rs.fields.count;
++i)
arr[rs.fields(i).name] =
rs.fields(i).value
}
function arr2rs(arr, rs) {
for(var f in arr)
if(rs.fields(f))
rs.fields(f).value = arr[f]
}
function show_menu() {
var html = ''
html += '<input
type="button" value="Rank"
onclick="show_rank()">'
document.getElementById("main").innerHTML = html
}
function show_rank() {
// write the HTML for the form
var html = ''
html += '<p id="selector"><select
id="rank_list"
onchange="get_rank(get_selected_value(this))">'
html += '<option
value="0">New rank</option>'
html +=
gen_list_options("SELECT * FROM Rank", "ID",
"ShortName")
html +=
'</select></p>'
html += '<p><span
id="shortname_p">Short Name</span> <input
type="text" id="rank_short" /></p>'
html += '<p><span
id="longname_p">Long Name</span> <input
type="text" id="rank_long" /></p>'
html += '<p
id="control_p"><input type="button"
value="Update" onclick="update_rank()" />'
html += ' <input
type="button" value="Delete"
onclick="delete_rank()" />'
html += ' <input
type="button" value="Back" onclick="show_menu()"
/></p>'
document.getElementById("main").innerHTML = html
}
function get_rank(id) {
var data = new Array()
if(id == 0) {
// Show the new guy
data["ShortName"] =
""
data["LongName"] =
""
}
else {
var rs = new
ActiveXObject("ADODB.Recordset")
rs.open("SELECT * FROM
Rank WHERE ID = " + id, conn, adOpenDynamic, adLockOptimistic)
rs2arr(rs, data)
rs.close()
}
// Read the resulting transaction
state into the form
document.getElementById("rank_short").value =
data["ShortName"]
document.getElementById("rank_long").value =
data["LongName"]
}
function delete_rank() {
var id =
get_selected_value(document.getElementById("rank_list"))
conn.execute("DELETE FROM
Rank WHERE ID = " + id)
show_rank()
}
function update_rank() {
var id =
get_selected_value(document.getElementById("rank_list"))
// Store form state in array
var data = new Array();
data["ShortName"] =
document.getElementById("rank_short").value
data["LongName"] =
document.getElementById("rank_long").value
// Build the query, selecting
only the current record if it exists
var str_sql = "SELECT * FROM
Rank"
if(id != 0)
str_sql += " WHERE ID =
" + id
// Get the record
var rs = new
ActiveXObject("ADODB.Recordset")
rs.open(str_sql, conn, 2, 3)
if(id == 0)
rs.AddNew()
arr2rs(data, rs)
// dump the data into the record
rs.Update()
// Refresh the UI
var sel =
document.getElementById("rank_list").selectedIndex
show_rank()
document.getElementById("rank_list").selectedI
ndex = sel
get_rank(id)
}
//-->
</script>
<title>JS Client-side ADO example</title>
</head>
<body onload="show_menu()">
<div id="main" />
</body>
</html>
Going beyond
The above source listing can
clearly be improved upon. Object orientation could make the code much clearer
and more extendable, and Javascript sports some very nice OO features. I'd love
to hear any other suggestions.
Issues
Obviously, these techniques are only suitable in a LAN or other highly-trusted environment. Even so, Internet Explorer will, by default, prompt the user with no fewer than two warnings about running possibly unsafe code.
There is also the issue of the code being available for the user to peruse. This can be mitigated by restricting all user access through stored procedures. A user will be able to create new clients which utilize the stored procedures, but if those procedures are designed carefully, the user will be unable to perform inappropriate actions.
As far as I know, there is no ODBC support in Mozilla-based browsers. If it existed, one could implement either a work-alike for the ADO objects or a wrapper for both Mozilla and IE which allowed for more cross-platform code. Unfortunately, I was unable to find any such functionality available for Mozilla.
![]](images/rt_edge3.gif){kind=small}
![[XML]](images/rssblue.gif){kind=small}
![[P]](images/print3.gif){kind=small}
