...

View Full Version : Help with clientside-only solution



nataddrho
01-03-2012, 06:07 PM
I am a govt. employee wanting to make a multi-user database. This is a replacement for the current solution:

1. Single-user Excel sheet on a shared network folder.

Here are my design limits:

1. The system uses Windows XP only.
2. I do not have communication with the network admins.
3. I will never have permission to install servers anywhere. They are forbidden.
4. I do not have permission to install any software on any computer.
5. My group only has access to one shared network folder.

I've brainstormed some solutions, including:

A. Marrying a VB Application with SQLite, with the SQLite file sitting in the shared folder.

B. Using how to use SharePoint to develop my application.

C. Using Microsoft Access as a client-side GUI which connects to another .mdb on the shared folder.

D. Using Internet Explorer with Javascript and ADO ActiveX Objects to manipulate records in a .mdb on the shared folder.

I choose D. Choice A is out because I can not install any software development tools. B may not work for me because my database is heirarchical in nature and I want more control over the GUI. Same reason for C, and Access is a little micky-mouse.

Amazingly, the security settings on IE allow the creation of ActiveX objects through Javascript. I can also develop with javascript because I have Wordpad. To test the code I can simply run it by launching the HTML. This allows users to open the same HTML file in the shared folder instead of distributing front-ends.

The javascript must all be client-side, including all data manipulation. However, I am not concerned about security issues because only our group will be using the system, and all traffic is already contained within Navy networks. So I don't care if I use window.location for storing variables since it works for this case.

Does anyone have anything to say about this? I'm looking for pointers, gotchas, guidelines, and any other, better solutions. Thanks.

jmrker
01-03-2012, 06:41 PM
This is not a suggested solution, just a few more requirement questions about the project.

1. Is this to replace the Excel worksheet or will you continue to use it?
2. Are you expecting to change values within the database or is it for display purposes only?
3. Can you use ajax functions to read text data or do you plan on using external JS files?
4. How often will the database being used change contents?
5. Are you limited to a certain browser or can you use some of the new HTML5 features?

Old Pedant
01-03-2012, 08:26 PM
jmrkr: I can answer one of those question, as he already answered it.

> 3. Can you use ajax functions to read text data or do you plan on using external JS files?

He said he can *NOT* install *ANY* server-side software, so AJAX is clearly out of the question.

*******

I'm not clear on how your question 5 is relevant.

Old Pedant
01-03-2012, 08:34 PM
My comments: It's an ugly solution (option D) but I agree with you that it is likely the only viable one.

I might only note that, since you will be forced to use MSIE, anyway, you might consider coding this in VBScript instead of JavaScript. Reason: There are a few ADO capabilities that are clumsy to use in JavaScript, primarily because they were designed back in the mid 1990s and were directly intended for use with VB and VBScript. But if you feel more comfortable with JavaScript, by all means stick with it.

The one issue you will need to watch out for is making sure you don't leave connections open any longer than needful. Access is *NOT* a true multi-user database, and it's easy to have one user that leaves a connection open end up locking out all other users.

So create the ADODB.Connection object once per page, say, but only call Open on it when you are ready to perform some operations and then call Close as soon as the operations are done. And make sure you trap errors (i.e., use try...catch... with JS coding) so you can close the connection if there is an error.

Other than the caveat about connections, I see no reason this can't work just fine.

Oh...you *will* have to be sure that all users have read/write access to the shared folder containing the MDB file. Will this violate any of the strictures you are working under?

Old Pedant
01-03-2012, 08:37 PM
By the by, if you didn't know, you can *ALSO* treat your existing Excel file as a database, using the JET OLEDB driver.

That is, you can open an ADODB.Connection to the Excel file and perform SQL queries on it, using ADODB.Command and/or ADODB.Recordset.

I don't know if that buys you anything useful or not, but remember that the possibility is there.

nataddrho
01-03-2012, 08:52 PM
This is not a suggested solution, just a few more requirement questions about the project.

Q1. Is this to replace the Excel worksheet or will you continue to use it?
Q2. Are you expecting to change values within the database or is it for display purposes only?
Q3. Can you use ajax functions to read text data or do you plan on using external JS files?
Q4. How often will the database being used change contents?
Q5. Are you limited to a certain browser or can you use some of the new HTML5 features?

A1. This is to replace the Excel worksheet.

A2. I am expecting changes. Users can select, update, insert, and delete.

A3. I'm an EE with pockets of computer science knowledge, and confidence to expand them. I am new to javascript. I am unfamiliar with AJAX. From the Wikipedia article's first paragraph, it seems that AJAX needs a server to implement, so I skipped researching it. So far I've had success using the modified preorder tree traversal algorithm to display data in a table generated by jQuery's collapsible/expandable tree table. I've done some PHP programming in the past, so my approach is to load HTML content on the $(document).ready function, and pass variables to other pages through the window.location (since I have no server). I am currently working on the code for form population, validation, and submission.

A4. The database will be updated many times per minute at most, but the number of simultaneous users is at most 5.

A5. Help->About Internet Explorer says: Version 7.0.5730.13. This is all I can use, no plugins or modifications allowed.

I am also novice with standard practices with this type of work. I usually do what I need to do to get it to work properly.

Old Pedant
01-03-2012, 09:14 PM
One of the really nice operations available with ADODB.Recordset is the GetRows method.

This returns a VBScript-style two-dimensional array of all the data in the recordset. It's quite fast and so a code sequence such as

open connection
do the SQL query
call GetRows on the recordset
close the recordset
close the connection
can be done in (typically) milliseconds and you will minimize any contentious locking problems.

But as noted, the result is a VBScript array.

JScript includes a special object for handling VBScript arrays, named (what else?) VBArray.

So you can do:


conn.Open();
var rs = conn.Execute( sql );
var vba = rs.GetRows();
rs.Close();
conn.Close();
var jsArray = vba.toArray();

That converts the VB 2-D array to a JS 1-D array.

Exampple: Suppose the recordset had 3 records and each record had 3 integer field.
So after calling GetRows, the vba variable contains the 2D array ((1, 2, 3), (4, 5, 6), (7, 8, 9)).
After translation via toArray, the JScript array contains: [1, 2, 3, 4, 5, 6, 7, 8, 9].

That's not the most convenient of conversions, but you can see that it wouldn't be hard to write some functions to pass that data to your jQuery code for display.

nataddrho
01-03-2012, 09:15 PM
My comments: It's an ugly solution (option D) but I agree with you that it is likely the only viable one.

I might only note that, since you will be forced to use MSIE, anyway, you might consider coding this in VBScript instead of JavaScript. Reason: There are a few ADO capabilities that are clumsy to use in JavaScript, primarily because they were designed back in the mid 1990s and were directly intended for use with VB and VBScript. But if you feel more comfortable with JavaScript, by all means stick with it.

The one issue you will need to watch out for is making sure you don't leave connections open any longer than needful. Access is *NOT* a true multi-user database, and it's easy to have one user that leaves a connection open end up locking out all other users.

So create the ADODB.Connection object once per page, say, but only call Open on it when you are ready to perform some operations and then call Close as soon as the operations are done. And make sure you trap errors (i.e., use try...catch... with JS coding) so you can close the connection if there is an error.

Other than the caveat about connections, I see no reason this can't work just fine.

Oh...you *will* have to be sure that all users have read/write access to the shared folder containing the MDB file. Will this violate any of the strictures you are working under?

I didn't consider using VBScript, but since I now have a working and beautiful jQuery table I might as well stick with it. The only speed bump I did run into so far is along the lines of what you mentioned. Executing an ADODB.Command object (for update,insert actions) is faster than creating a recordset object, but when called by javascript generates a message box that isn't avoidable with the Navy MSIE settings. The only other solution is to update the database with pure SQL strings. This isn't awful though, because again, I'm not concerned about hackers. I just have to make sure I add #'s for dates.

I also want to be aware of user collisions, so I want to limit update and insert statements to as few as possible. Any advice on this from seasoned developers? I figure the change of a collision is very low.

When I used MS Access a long time ago, the databases would grow huge after moderate use. Should I be concerned about this?

I guess basically I don't trust the stability of an MS database, even sitting by itself. Can anyone make me feel better about it?

nataddrho
01-03-2012, 09:23 PM
One of the really nice operations available with ADODB.Recordset is the GetRows method.

This returns a VBScript-style two-dimensional array of all the data in the recordset. It's quite fast and so a code sequence such as

open connection
do the SQL query
call GetRows on the recordset
close the recordset
close the connection
can be done in (typically) milliseconds and you will minimize any contentious locking problems.

But as noted, the result is a VBScript array.

JScript includes a special object for handling VBScript arrays, named (what else?) VBArray.

So you can do:


conn.Open();
var rs = conn.Execute( sql );
var vba = rs.GetRows();
rs.Close();
conn.Close();
var jsArray = vba.toArray();

That converts the VB 2-D array to a JS 1-D array.

Exampple: Suppose the recordset had 3 records and each record had 3 integer field.
So after calling GetRows, the vba variable contains the 2D array ((1, 2, 3), (4, 5, 6), (7, 8, 9)).
After translation via toArray, the JScript array contains: [1, 2, 3, 4, 5, 6, 7, 8, 9].

That's not the most convenient of conversions, but you can see that it wouldn't be hard to write some functions to pass that data to your jQuery code for display.

oooOO. Is this superior to, say:


//Generate recordset and populate all fields with matching field names
var cn = new ActiveXObject("ADODB.Connection");
var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "+warg.dbPath;
var rs = new ActiveXObject("ADODB.Recordset");
try {
cn.Open(strConn)
try {
rs.Open(SQL, cn);
var elem = document.getElementById('form1').elements;
for(var i = 0; i < elem.length; i++)
{
if (elem[i].name.length>0) {
try {elem[i].value=rs(elem[i].name);}
catch(err) {}
}
}
rs.Close();
cn.Close();
}
catch(err) {
tblHTML = "Could not execute SQL statement: "+SQL+"<br>";
cn.Close();
}
}
catch(err) {
tblHTML = "Could not connect to database "+warg.dbPath+"<br>";

Old Pedant
01-03-2012, 09:29 PM
> When I used MS Access a long time ago, the databases would grow huge after moderate use. Should I be concerned about this?

Yes and no. Just watch the growth for a while and then come up with a schedule for running a "compact and repair".

You can do compact and repair from ADO, but I have found that to fail on rare occasions. So if you go that route, I'd recommend that you first use the Scripting.FileSystemObject to make a backup of the ".mdb" file. If you do the compact and repair from within Access [that is, run Access itself, after hours, say] I've never had it fail. But I'm still paranoid enough that I always make a copy of the ".mdb" file first.

I have one Access DB that runs around 85MB and will occasionally grow to 120MB or so. I run compact and repair on it about once a week and it's been running for 2 years now.

Old Pedant
01-03-2012, 09:33 PM
> Is this superior to, say: ...

Yes, but if you only expect one record it wouldn't make that much difference. And keeping the JS as simple as you show there would probably be worth minor performance hit.

I was envisioning displaying a grid of dozens or more records, each with several fields. *THEN* it would be worth using GetRows.

nataddrho
01-03-2012, 09:45 PM
I'll upload screenshots later in the process to show you. That should help with your comments. Thanks!

Old Pedant
01-03-2012, 09:48 PM
Actually, your code there is hiding a potentially nasty bug.

Suppose that the error occurs on the line in red below:


rs.Close();
cn.Close();
}
catch(err) {
tblHTML = "Could not execute SQL statement: "+SQL+"<br>";
cn.Close();
}
}
catch(err) {
tblHTML = "Could not connect to database "+warg.dbPath+"<br>";
}

So you'll get the "Could not execute SQL statement" error, even though the SQL may have executed perfectly okay, and then (since the first attempt to close the connection failed and we can assume the second one will also) you will replace that error with the "Could not connect to database" error.

If you want to be really and properly paranoid, how about:



function fillFormFromSQL( SQL )
{
//Generate recordset and populate all fields with matching field names
var cn = new ActiveXObject("ADODB.Connection");
var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "+warg.dbPath;
try {
cn.Open(strConn)
} catch( err ) {
return "Error connectiong to database: " + err.toString();
}

try {
try {
var rs = cn.Execute( SQL );
} catch( err ) {
return "Error retrieving record: " + err.toString();
}

try {
var elem = document.getElementById('form1').elements;
for(var i = 0; i < elem.length; i++)
{
if (elem[i].name.length>0) {
elem[i].value=rs(elem[i].name);
}
}
} catch( err ) {
return "Error retrieving fields from record: " + err.toString();
}
} catch( err ) {
return "Unexpected error: " + err.toString();
} finally {
try {
rs.Close();
} catch( ignored ) { }
try {
cn.Close();
} catch(err) {
return "Error closing connection: " + err.toString();
}
}
}

nataddrho
01-04-2012, 02:10 PM
I'd like to create a .js file that contains a function:

UpdateTable(conn,doc)

where conn is an ADODB.COnnection object, and doc is some way of passing a form (or document, etc).

The function will loop through each form element and construct an INSERT or UPDATE sql statement based on the form element values. The "id" of each element will specify "string","date","bool","number" for formatting.

How to I pass a document or form to a function?

Old Pedant
01-04-2012, 08:21 PM
Lots of ways.

One good one: Do it via the onsubmit of the <form>.



<form onsubmit="UpdateTable(this); return false;">
...
<input type="submit" value="Update"/>
</form>


And then:


function UpdateTable(form)
{
for ( var e = 0; e < form.elements.length; ++e )
{
var formField = form.elements[e];
...
}
}

BUT...

Remember that id's must be unique on a page. Names don't have to be. So if you have, for example, a set of radio buttons or checkboxes they can indeed all have the same name. You'll have to handle them slightly special in the UpdateTable function.

nataddrho
01-05-2012, 04:04 PM
Thanks. Next step:

Do you know of any existing javascript functions that can do IsNumeric(), IsDate(), IsString(), etc, for ADODB types? The alternative path is to make my own function that looks at the ADODB Type property (DataTypeEnum) and take actions from this.

nate

Old Pedant
01-05-2012, 09:38 PM
I'd look at the ADODB Type property. It's much more reliable.

You *can* check the *values* of fields, using JavaScript, but if you have a null value (or zero value in the case of a date, for example) you could well infer the wrong type. (And note that JS makes no distinction between an integer and a real number...both are simply Number in JS. Same with objects. If you use JS's typeof operator, all object types are just "Object". Probably okay, as the only variable type you'd expect to see as Object is a DATETIME field, but...)

You don't really need a *function* to check the ADODB type. You could just define a set of constants in JS and check them.

JS doesn't really support CONST as does VB/VBScript/C#/etc., but I use a coding convention to emulate CONST: If the variable name is all upper case, then I treat it as a CONST in my coding.

So, for example, you could do:


var TYPE_INT32 = 3;
var TYPE_REAL64 = 8;

(I think those numbers are right, but check the docs, of course.)

As a mildly more JS-ish alternative, you could instead do:


var ADOTYPE {
INT32 : 3,
REAL64 : 8,
...
};

and then do

if ( field.Type == AODTYPE.INT32 ) ...

nataddrho
01-06-2012, 04:37 PM
Yeah, I've already adopted the CAPS FOR CONST convention from C and C++. It has kept me from getting lost and works well.

My original idea was to have the code check the database data type and format based on the ADODB Type. What I found easier is to force the developer (myself) to instead specify a tag to the form element of which data type it should be (string, number, bool, date are the four so far). Since you mentioned the <id> tag needs to be unique, and the <title> tag is already being used by my jQuery validate function to store validation messages, I prefixed a character to the <name> tag, such as "sTitle", "nPrice", "bAlive", and "dDateEntered". The rest of the title must match the MS Access field name. This seems to work fine. All I have to do is just manually pay attention to the data type when I make the forms.

I also learned that there is no native DateFormat function in javascript. I'm sure you have heard this a thousand times, so I just make a function that formats dates into MS SQL's #yyyy-mm-dd hh:mm:ss# and left it at that.

The only thing left is to research the best way to generate content for combo boxes. The questions concerning this are not yet properly formed so I won't try writing them here yet.

Old Pedant
01-06-2012, 09:14 PM
What are "combo boxes"???

nataddrho
01-06-2012, 10:56 PM
What are "combo boxes"???

"Combo boxes" are MS Access' name for "Drop Down Lists", or the <select> control in HTML. I should stop calling them combo boxes because no one uses that name anymore.

I will be populating combo boxes on forms I make using related database data. I want to make the HTML code as clean as possible. But I think I can figure this out so I don't need to turn this into a thread direction yet.

I have quite a bit of momentum now; I can't think of any other questions for you at the moment. But check back in case I do. Thanks!

Old Pedant
01-06-2012, 11:03 PM
Ahh...I remember. Actually, COMBO BOXES in Access are truly a combination of text field and <select>. Because you can either select from the list or type in a new value. There's no equivalent in HTML, but you can simulate it with a bit of work using CSS and JavaScript.

nataddrho
01-09-2012, 02:21 PM
I'd like to make a dictionary, similar to a Python dictionary. I'm not sure you can do this in javascript. One idea is to use an object and insert values like this:

var x = new Object();
x["Key"] = "Value";

for a long list of values.

Another idea is to create an array and enter a for loop, and testing array[i] against my matching value.

Which is the fastest method?

Edit: I've learned about Arrays and Objects, and about the point that Objects should be used for associative arrays (string-type keys). However, this still doesn't include hash-like operations so I'll just build and un-parse a string. This should work fine.

I've also not found any literature on "re-ordering" a modified preorder tree traversal result. I might try calling a few professors tomorrow for some advice.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum