Kuro5hin.org: technology and culture, from the trenches
create account | help/FAQ | contact | links | search | IRC | site news
[ Everything | Diaries | Technology | Science | Culture | Politics | Media | News | Internet | Op-Ed | Fiction | Meta | MLP ]
We need your support: buy an ad | premium membership

[P]
Client-side ADO using Javascript

By curien in Technology
Fri Jul 15, 2005 at 08:42:28 PM EST
Tags: Internet (all tags)
Internet

A friend asked me to do a fairly simple database app for them. The tools at my disposal are pretty much limited to MS Access and whatever else comes with Windows XP and MS Office. This should be pretty easy to do with an Access front-end, but I don't know VBA and I'm not eager to learn. I do, however, have a bit of experience in developing web-based apps using Active Server Pages and ActiveX Data Objects. I have my hammer, and by god I'm going to use it.


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.

Sponsors

Voxel dot net
o Managed Hosting
o VoxCAST Content Delivery
o Raw Infrastructure

Login

Related Links
o MS Access
o VBA
o Active Server Pages
o ActiveX Data Objects
o innerHTML
o Javascript
o ADO
o SQL
o ADO constants
o Also by curien


Display: Sort:
Client-side ADO using Javascript | 82 comments (60 topical, 22 editorial, 0 hidden)
This is sick and wrong on so many levels (2.78 / 19) (#4)
by trane on Thu Jul 14, 2005 at 02:55:52 PM EST

  1. javascript
  2. platform/browser dependent
  3. web programming
  4. mixes presentation with content
etc...

Too...much...code (2.75 / 4) (#11)
by More Whine on Thu Jul 14, 2005 at 08:16:34 PM EST

If I wanted to fall asleep, I'd take some Benadryl.

I'm glad to see this, even if it is simplistic (3.00 / 2) (#12)
by TheQ on Thu Jul 14, 2005 at 11:00:06 PM EST

Whereas many believe the utility of DHTML is consigned to the web and that there should be a server generating client HTML and performing the actual "work" of a web app, this doesn't always make sense for a variety of reasons. I'm glad to see a short and sweet example of using JavaScript more as a client application language.

One thing that could be an improvement, though mainly for aesthetics, is to create the DHTML app you described an a genuine Html Application (HTA). See http://msdn.microsoft.com/library/default.asp?url=/workshop/author/hta/overview/ htaoverview.asp

That way, you could actually make your little bit of DHTML look like a "real" application.


-Q
Neat hack. (3.00 / 8) (#13)
by acceleriter on Thu Jul 14, 2005 at 11:19:45 PM EST

But when I think about what you're doing, I can't help hearing the soundtrack from The Omen playing in my head.

Security (3.00 / 2) (#17)
by Kj0n on Fri Jul 15, 2005 at 02:43:51 AM EST

I like the idea of using JavaScript to offer a web interface to an access database instead of using VBA. However, this does not allow you to hide the internals from your application from your users.

For instance, users can see the connect string to the database and the queries you are executing. This means that anyone who knows a bit about ADO can access the database and run his own queries, retrieving data that he is not allowed to see, or performing updates that are illegal.

To give you another suggestion, we have recently done a project like this, and ended up using a smart client application in Java: a Swing based application, that comes with its own embedded database (Derby) and can be installed with Java Web Start.

Portability (none / 0) (#37)
by LodeRunner on Fri Jul 15, 2005 at 01:18:43 PM EST

[Since this editorial thread morphed into topical...]

Alternative was Access VBA - That would have been soooo much more cross-platform.

Sure, your solution was better than that. And if you were under the restriction that no additional software (.exe's) should be installed in the client machines, then I think you probably did the best that could be done.

Honestly, I think this is more cross-platform than you think. There's nothing stopping someone from coding up a Firefox extension that implements an ADO work-alike. The rest of the Javascript is, as far as I can tell, standard.

I see. Of course, the "there's nothing stopping someone from coding a clone" argument can be used for any piece of proprietary software -- it's more valid in some cases than in others, but I do think you have a point here.

(Quoting the article:) I have my hammer, and by god I'm going to use it.

Thinking about alternatives, do you think a solution involving Python (for flexible, high-level scripting), Py2Exe (for easy deployment) and Python ODBC (for db connectivity) would be feasible? I'm just curious to know if my hammer would do the trick in a situation similar to the one you were in. (About portability, I don't know if Python ODBC is available for Unix yet, but ODBC is, so it should be possible).

---
"dude, you can't even spell your own name" -- Lode Runner

Alone among the naysayers (3.00 / 2) (#40)
by SocratesGhost on Fri Jul 15, 2005 at 04:18:42 PM EST

I'll say "Thank you!" I'm working at a client right now that has a ton of old Access databases and I was considering techniques to get them to be served through a browser so that we could begin standardizing the interface (for the time when we replace the back end database). But, lacking a web server, I didn't want to pursue that line of thought much further. Maybe now I'll give it a second look.

-Soc
I drank what?


got this working (2.00 / 3) (#42)
by SaintPort on Fri Jul 15, 2005 at 05:25:38 PM EST

created rank.mdb file with
 table:rank
   with  fields:
     ID, ShortName, LongName
and in same folder the html code below in rank.hta
(when I name the file rank.html I get an unresponsive button and no error message.)
___________________
CODE:
-------------------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&amp; 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 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=rank.mdb";
  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").selectedIndex = sel;
    get_rank(id);
  }

  //-->
  </script>

  <title>JS Client-side ADO example</title>
  </head>

  <body onload="show_menu()">
    <div id="main" />
  </body>
  </html>

--
Search the Scriptures
Start with some cheap grace...Got Life?

Man. (3.00 / 3) (#48)
by Soviet Russian on Fri Jul 15, 2005 at 10:38:23 PM EST

I would've just used dBase III or Paradox or some shit like that.

Shouldn't this be on Slashdork? [nt] (none / 0) (#49)
by lordDogma on Sat Jul 16, 2005 at 01:26:48 AM EST



My God! (1.00 / 6) (#50)
by tweetsybefore on Sat Jul 16, 2005 at 02:10:12 AM EST

how stupid can you kurons be? I bet nearly everyone who is a real developer doesn't appreciate seeing the obscenity of Microsoft proprietary code. At the bottom of this site it says Kuro5hin.org is powered by Free Software, including Apache, Perl, and Linux. Take your disgusting Microsoft prorietary code elsewhere.

How many people voted this up with having no knowledge about software development? This is not the place to put articles on Microsoft development. Go to a place where Microsoft is appreciated. Not here. I'm disgusted that the users here think Microsoft Software is acceptable to use.

I'm racist and I hate niggers.

Firefox... (none / 0) (#54)
by SiMac on Sat Jul 16, 2005 at 01:32:54 PM EST

The Gecko engine to be included with Firefox 2.0 (1.5?) will include MozStorage support, which will allow one to use the SQLite database engine from JavaScript. Actually, if you look at the latest Mozilla code, it's already in there as a compilation option. There's no docs, but I've managed to create a working sample script. Some features aren't implemented, but it's possible to get a simple database working.

are you sure it was a friend? (2.00 / 5) (#61)
by nietsch on Sat Jul 16, 2005 at 04:08:07 PM EST

Because friends don't let friends use MS products.
Besides, what is the purpose of this article on the frontpage? I got the impression the author is fishing for approval from his peers for his technical skills. I am (and probably a lot of other readers of the frontpage) a javascript or vbscript programmer, and unless he stops using MS products I won't be his friend either.

Access Wizard Data Access Page (none / 0) (#63)
by n8f8 on Sat Jul 16, 2005 at 04:50:22 PM EST

There is a Data Access Page wizard in Access to generate similar functionality. Or just a damn form using the form wizard. The wizards are built into Access 2000 and up. Not that I'm advocating an IE only solution.

Sig: (This will get posted after your comments)
The real problem here -- innerHTML (3.00 / 2) (#65)
by RSevrinsky on Sun Jul 17, 2005 at 03:40:56 AM EST

Ignoring for the moment the single-platform limitation of this app and the fact that it could just as easily be done in VB/VBScript/VBA, I would like to point out that the entire application depends on rewriting the page with innerHTML. In my experience as a web application developer, this will cause you no small amount of pain and suffering (EVIL!!!).

innerHTML basically says, "I'm rewriting this page on the fly, but without bothering to carefully manipulate the DOM tree -- let the browser do the heavy lifting!" Rewriting with innerHTML can easily break the page and makes debugging extremely difficult -- depending on your toolset, it may not be possible to view the source of the altered page.

If page rewriting is necessary, wherever possible, create true DOM nodes and add them to the page's tree. Playing with innerHTML is like dealing with plutonium.

- Richie


It does indeed suck, but... (none / 0) (#74)
by ckaminski on Wed Jul 20, 2005 at 08:38:55 AM EST

I've built a number of web apps over the years, and some of them needed to work offline.  In my infinite amount of spare time, I've been modifying one CRM/Helpdesk app to support disconnected use through a very similar mechanism.  At run time, the system determines if it can access the remote website, and will load it, if not, it can run locally.  It's been a PITA to convert, and it has it's flaws still.  Separate code base since I haven't gotten around to AJAXing it yet.  

But I can certainly see the desire for such a thing.

No webserver, no database? Download one!! (none / 1) (#82)
by La Camiseta on Tue Jul 26, 2005 at 02:37:38 PM EST

I don't know whether or not you realised it, but you can just download some free opensource webservers scripting languages and even a selection of two databases, all of which run on WindowsXP/2000

If there would be multiple people using the program simultaneously, you'd have to have the database shared anyways, so why not just run something like Apache/PHP/MySQL on the computer that would be serving anyways. Hell, WindowsXP and 2000 even come with a regular version of IIS that you could install PHP or Perl on.

Plus this has the added advantage that it could be easily migrated across systems (Windows/MacOS), webbrowsers (IE/Firefox/Opera/OmniWeb/others), or even easily hosted offsite, should that become necessary.
־‮־

Don't reinvent the wheel (none / 0) (#83)
by gkknow on Sat Aug 13, 2005 at 02:16:29 PM EST

There is plenty of proven solution out there

Client-side ADO using Javascript | 82 comments (60 topical, 22 editorial, 0 hidden)
Display: Sort:

kuro5hin.org

[XML]
All trademarks and copyrights on this page are owned by their respective companies. The Rest © 2000 - Present Kuro5hin.org Inc.
See our legalese page for copyright policies. Please also read our Privacy Policy.
Kuro5hin.org is powered by Free Software, including Apache, Perl, and Linux, The Scoop Engine that runs this site is freely available, under the terms of the GPL.
Need some help? Email help@kuro5hin.org.
My heart's the long stairs.

Powered by Scoop create account | help/FAQ | mission | links | search | IRC | YOU choose the stories!