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...

IAW2A4 [nt] (none / 0) (#6)
by LodeRunner on Thu Jul 14, 2005 at 04:45:23 PM EST


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

How about security (none / 0) (#8)
by trane on Thu Jul 14, 2005 at 06:37:35 PM EST

you could potentially dump the entire database's contents to your browser...

[ Parent ]
...and then... (none / 0) (#14)
by LodeRunner on Fri Jul 15, 2005 at 02:07:10 AM EST

...the browser could send it to a remote site. One could potentially do this to make a webpage that harvests local databases and sucks their data.

The possibilities of malicious code that can be done with ActiveX in such a way that all the user has to do is open a webpage to have their system compromised are endless. That alone makes IE unacceptable.

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

Possibly (none / 0) (#21)
by curien on Fri Jul 15, 2005 at 03:23:20 AM EST

The user will have to click through no fewer than two warnings about the script possibly doing something harmful. Even so, I'm not sure that a site in a non-trusted zone is even capable of using the necessary functionality.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
While I appreciate... (none / 0) (#33)
by LodeRunner on Fri Jul 15, 2005 at 12:49:20 PM EST

...the warning messages, I have no illusions about the average user's reflex instincts in "okaying away" dialog boxes just to get rid of them. :(

Last I heard, it was still possible to write an ActiveX control that can, say, format your hard drive from your browser (ie, do arbitrary things to your filesystem). Apparently, Microsoft did not implement the same sandboxing security measures that Java applets have.

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

So you add security (none / 0) (#20)
by curien on Fri Jul 15, 2005 at 03:21:19 AM EST

at the RDBMS level.

If you think that a DB is more secure because they don't have the source code to your server-side scripts, you're relying on security by obscurity.

This is not meant for use outside a LAN environment. I thought that much was obvious, but I guess I'll have to make it explicit.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]

A Subject (none / 1) (#23)
by warrax on Fri Jul 15, 2005 at 03:47:30 AM EST

[...] If you think that a DB is more secure because they don't have the source code to your server-side scripts, you're relying on security by obscurity.
How did you arrive at that interpretation? He's relying on the users not being able to run arbitrary queries against a DB unless they can run arbitrary code on the web server. (Your DB server better be restricted to only accept connections from places it's supposed to).

Even in a LAN environment your sceme is horribly insecure because it requires all clients to be able to initiate direct connections to the DB server. A big part of security is having multiple layers of protection and your approach has absolutely none.

-- "Guns don't kill people. I kill people."
[ Parent ]

Addressed in the article now (none / 1) (#24)
by curien on Fri Jul 15, 2005 at 04:23:20 AM EST

You do everything through stored procedures. As you noted, restricting access through the Javascript code is insufficient.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
Cool. (none / 1) (#32)
by warrax on Fri Jul 15, 2005 at 09:54:39 AM EST

i'm still gonna vote -1, but that's just because i don't really find it interesting... or it could be that i'm just a bastard. who knows?

-- "Guns don't kill people. I kill people."
[ Parent ]
Javascript (none / 1) (#53)
by JVincent on Sat Jul 16, 2005 at 09:20:18 AM EST

isn't evil!! It just got a bad rep with flashy backgrounds and all that. Get of it's back!!

[ Parent ]
It's too often used for evil (none / 0) (#58)
by trane on Sat Jul 16, 2005 at 03:33:53 PM EST

I don't trust it.

[ Parent ]
Don't loose faith! (none / 1) (#64)
by JVincent on Sat Jul 16, 2005 at 05:15:42 PM EST

It can be used for good aswell! We just need to help it in the right direction!!

[ Parent ]
True enough - but interesting still n/t (none / 0) (#79)
by 123456789 on Thu Jul 21, 2005 at 06:30:32 PM EST



---
People demand freedom of speech to make up for the freedom of thought which they avoid.
- Soren Kierkegaard
[ Parent ]
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
HTA over HTML (none / 1) (#31)
by krytae on Fri Jul 15, 2005 at 09:45:25 AM EST

I've run into MS installs that don't recognize the HTA extension. HTA is fine as long as you can be sure the MS OS is installed somewhat coherently.

[ Parent ]
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.

If security is an issue (none / 1) (#19)
by curien on Fri Jul 15, 2005 at 03:17:43 AM EST

you shouldn't be using Jet in the first place. You use a proper DBMS like Oracle, MSSQL (or MSDE), Postgres, etc which can restrict access based on credentials. You run queries as stored procedures so as not to expose your database.

Perhaps I'll add something about that in the final section.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]

yeah that was sort of silly (none / 0) (#27)
by army of phred on Fri Jul 15, 2005 at 05:19:01 AM EST

if the database is in mdb, I'm going to have at it without using javascript, regardless if theres any javascript there in the first place.

"Republicans are evil." lildebbie
"I have no fucking clue what I'm talking about." motormachinemercenary
"my wife is getting a blowjob" ghostoft1ber
[ Parent ]
And while where on the criticing design mode (none / 0) (#36)
by twickham on Fri Jul 15, 2005 at 01:13:35 PM EST

What the hell ever happened to the concept of decoupling? I realise this seems to be just a micky mouse "lets talk to JET" from a browser. Yipee!" application. But personally Id go that extra half day implementation effort and move database code to the server side.

[ Parent ]
Why? (none / 0) (#55)
by curien on Sat Jul 16, 2005 at 02:39:16 PM EST

Why should you need a web server to talk to a database? Stored procedures and ODBC do a perfectly good job of decoupling all by themselves.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
As an aside... (none / 1) (#75)
by ckaminski on Wed Jul 20, 2005 at 08:42:30 AM EST

Why can't all the OSS developers coalesce around ODBC?  I'm sick of having to use MySQL for these Php projects out there, especially eCommerce ones, when I'm a postgres shop, and both Pgsql and MySQL support ODBC?

Apps I'm currently victimized by:
   mediawiki
   osCommerce

Ugh.

[ Parent ]

Ajax (none / 1) (#45)
by ucblockhead on Fri Jul 15, 2005 at 06:44:59 PM EST

A good way to do this is to use Ajax, and put all the nasty database code on the server.
-----------------------
This is k5. We're all tools - duxup
[ Parent ]
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

some folks aren't that worried (none / 0) (#41)
by army of phred on Fri Jul 15, 2005 at 04:44:36 PM EST

about portability. Where I work we have a windows only mindset and management has forbidden python interpreters as they tend to be vectors for malicious code and file sharing nodes for illegal porn. (For those folks wanting line oriented syntax, we let them use vb in word).

While I understand that unix is ok for fatassed lardbuckets like that admin in "jurassic park", you've got to remember some places are solidly in the windows camp and portability means ready to run on longhorn.

2 cents.

"Republicans are evil." lildebbie
"I have no fucking clue what I'm talking about." motormachinemercenary
"my wife is getting a blowjob" ghostoft1ber
[ Parent ]

I did my best with my resources (none / 0) (#56)
by curien on Sat Jul 16, 2005 at 02:46:09 PM EST

Honestly, I should have used an HTA, as pointed out by someone else.

Yeah, using python would have been fine. I like Javascript better than Python, though. (I really, really like the language. Not the DOM, the language.) Besides, it's not authorized on our systems. We have a whitelist policy -- if it's not explicitly authorized, it's illegal.

Really, the only reason I used the browser was as a host for Javascript. I started off using just Windows Scripting Host, but I switched to take advantage of the easy GUI functionality of the browser.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]

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?


Use Apache and MySQL (3.00 / 2) (#43)
by Specks on Fri Jul 15, 2005 at 06:14:45 PM EST

Use Apache and MySQL. They're both free and work on windows. Both PHP and Perl have packages that can read access databases. That's free too.
Specks
[ Parent ]
I can't (none / 1) (#44)
by SocratesGhost on Fri Jul 15, 2005 at 06:23:40 PM EST

Like I said, I won't have access to a web server. I have to make use of the infrastructure that is currently in place.

Seriously, do you think I haven't argued for something like that?

-Soc
I drank what?


[ Parent ]
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?

I guess if you trust your clients (none / 0) (#46)
by trane on Fri Jul 15, 2005 at 07:19:28 PM EST

because the user/password to the db is only a click away.

[ Parent ]
only a click away? (none / 0) (#52)
by SaintPort on Sat Jul 16, 2005 at 07:28:51 AM EST

why click? Everybody knows its Admin/"".

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

[ Parent ]
I like your attitude (3.00 / 2) (#59)
by trane on Sat Jul 16, 2005 at 03:37:33 PM EST

passwords suck.

[ Parent ]
Tech support (3.00 / 2) (#69)
by curien on Mon Jul 18, 2005 at 08:13:16 AM EST

Hi SaintPort,

I probably should have mentioned this in the article. Make sure you specify the absolute path to the MDB file.

var conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/path/to/rank.mdb";

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]

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.

Reality vs. cheap sound bite. (3.00 / 2) (#51)
by purephase on Sat Jul 16, 2005 at 03:38:36 AM EST

Enjoy it. You apparently live in some controlled experiment that allows you to dictate not only what is appropriate software, but who can actually write code to interact with said software.

Congratulations. You also seem to speak for a wide audience of K5 members when you so eloquently dictate what can, or cannot be posted and/or voted up. If only everyone else had listened to your enlightened wisdom!

Finally, "having no knowledge of software development" and posting a relatively informed article on interacting with MS development environments and said tools is somewhat counterintuitive. Next time, before speaking-up, how about re-reading your post prior to submission. It will save you the hassle and embarrassment of coming across like a complete ass. Although, judging from this comment, it is probably a realm that you're fairly comfortable with.

[ Parent ]
Go to Microsoft land. (none / 1) (#60)
by tweetsybefore on Sat Jul 16, 2005 at 03:51:01 PM EST

this is the land of free GNU/Hippies. We don't like your kind here now get out!

I'm racist and I hate niggers.
[ Parent ]
He didnt have many options (none / 1) (#62)
by Holonic Zenmaster on Sat Jul 16, 2005 at 04:33:03 PM EST

When doing software developments for people other than yourself, this is a situation that you need to deal with. Sometimes it is not easy to use anything but Microsoft products. If he had the choice I am sure he would have employed a nice setup using perl and MySQL. Hell he might have done the full mile and used Ruby on Rails. The situation did not evolve this way.

He instead made an interesting hack to do what he wanted, the hack did involve lots of Microsoft-ware. Still it got the job done, and shows creativity to some limited degree. It wont function too long as portable long-term solution, but it did the job. On a future job if the customer trusts him, He might do it differently.

I apologize for any words I put in the author's mouth, and welcome further comments.

[ Parent ]

That is why you must never work for anyone. (none / 0) (#71)
by tweetsybefore on Wed Jul 20, 2005 at 12:15:34 AM EST

If you do you must invariably compromise your own ideals.

I'm racist and I hate niggers.
[ Parent ]
Why do you say this? (none / 0) (#76)
by ckaminski on Wed Jul 20, 2005 at 08:47:49 AM EST

I don't know a single self-proclaimed libertarian who thinks:

<quote>
Most libertarians think it should be legal to spray a clip full of bullets into a kindergaten, as long as you miss everyone.
</quote>

is okay.  Someone has to pay for plugging the holes in the walls, or the destroyed trees/lawns, and that's completely against the Libertarian mindset, making someone else pay for the resources you use...

[ Parent ]

well the bullets all went through an open window. (none / 0) (#77)
by tweetsybefore on Wed Jul 20, 2005 at 01:39:23 PM EST



I'm racist and I hate niggers.
[ Parent ]
LOL nt (none / 0) (#81)
by 123456789 on Thu Jul 21, 2005 at 06:37:00 PM EST



---
People demand freedom of speech to make up for the freedom of thought which they avoid.
- Soren Kierkegaard
[ Parent ]
Hi my name is 123456789, (none / 1) (#80)
by 123456789 on Thu Jul 21, 2005 at 06:35:25 PM EST

I'd like to welcome you to "THE REAL WORLD." Here in the real world, real developers have to solve problems that non-technical people get their companies into because they are stupid. Unfortunately here in The Real World we don't always have the option of circle-jerking our Linux friends while we code in PHP against a mySQL db. We might like to, but we can't.

So stop acting like an elitist snob and cope.

---
People demand freedom of speech to make up for the freedom of thought which they avoid.
- Soren Kierkegaard
[ Parent ]
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.

Awesome, but... (none / 0) (#57)
by curien on Sat Jul 16, 2005 at 02:49:23 PM EST

Why not just supply ODBC?

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
Its for local storeage only (none / 0) (#72)
by blackpaw on Wed Jul 20, 2005 at 01:35:44 AM EST

No good if you want to acess a central server.

Also opaque to other apps - can only be accessed via the moz engine.

[ Parent ]

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.

-1 Linux fanboy (nt) (none / 0) (#68)
by Veritech on Mon Jul 18, 2005 at 08:08:07 AM EST



[ Parent ]
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)
Missed the point (none / 0) (#67)
by curien on Mon Jul 18, 2005 at 08:04:30 AM EST

The point was that I wanted to use Javascript, not that I wanted to use a browser. The Access wizards all use some form of VBScript.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
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


Good point! (none / 0) (#66)
by curien on Mon Jul 18, 2005 at 08:02:22 AM EST

Yes, debugging using innerHTML is difficult, especially when combinedwith IEs inability to distinguish between the HTML file and included .js files. Other browsers (Firefox and Konqueror) are much better about that sort of thing.

--
We are not the same. I'm an American, and you're a sick asshole.
[ Parent ]
Wuss! (none / 0) (#78)
by 123456789 on Thu Jul 21, 2005 at 06:29:51 PM EST

Yes, using innerHTML can cause problems if you're not careful, but I grow very weary of this argument. It has its place, and often turns what would be 300 lines of DOM-manipulating headache into 70 lines of direct output.

Yes, it is one level harder to debug, but really not impossible at all. I should know - I've written over a thousand lines of javascript for a project (one file) which contains much usage of .innerHTML out of necessity/expedience. I have been maintaining this setup (infrastructure wasn't my choice, btw) for a year now and code changes are quite manageable. The main trick is to innerHTML a DIV or something else that's hard to break from the inside, and then mind your ps and qs when you start churning out your HTML output.

---
People demand freedom of speech to make up for the freedom of thought which they avoid.
- Soren Kierkegaard
[ Parent ]
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!