Which DB Engine?

[The Stoat]

Hi all,

I tried to find out as much as i can about this issue but i keep finding conflicting views.

I am just about to buy a new server for work. I will be running a number of databases (4-5) on a website. The server will be using IIS windows server 2003. The databases will be accessed anywhere from many times an hour to once or twice a day. And in total the databases could theortically be accessed by ~20 people at the same time. Both data input and viewing reports.

I am unsure as to which db engine i should use.

I am only familiar with VBScript so what ever i use needs to allow me to work with the database in this language.

How many users can an online access database deal with?

I have tested some stuff using MSDE but i keep finding information saying that the engine is limited to 5 concurrent users i not sure this is going to cut it.


Could stretch to SQLServer but hate the idea of spending £3000+ on the license.


Is there another Engine that would fullfill this role and be more cost effective?


Thanks for any help.

TS

 

[ReAn]

If you use ASP pages (glorified version of vba with web objects and no variable types) you can use as many as you need, you're just using ADODB insread of DAO so ADODB is way more picky about the lockfiles.

 

[KenHigg]

1. Is you web stuff internet or intranet?
2. How secure does you web stuff need to be?
3. How robust do you need your db web frontend need to be?

One of the more simple web solutions is Access and Cold Fusion.

???
ken

 

[The Stoat]

Hi,

Basically;

The pages can only be viewed from with the NHS net NWW. not WWW. This means Internet like i.e. viewed from many sites but with certain restrictions on access. + much quicker then standard internet.

The data will need to be secure. I can do a 256bit encrypted password login to the appropriate pages and make sure the DB is outside of the root after that i'm not sure what else i can do.

The frontend/s will need to be robust in terms of serverside and clientside validation. I would have liked to try ASP.net but i simply don't have the time it takes to learn it hence ASP pages with VBscript.

I have a copy of Dreamweaver MX which supports coldfusion but i've never used the CF part. Interested to hear more.


Thanks

TS

 

[KenHigg]

So will your stuff be for general consumption or will it be restricted use, by people within your (customer's) organisation? If not, you may need to consider sessions, cookies, tokens, etc. And do you just need simple data input type screens?

CF is like ASP in that it builds the html on the fly with embeded tags. But the tags and contructs are more like basic where ASP tags kind of looks like C++.


ken

 

[ReAn]

ASP is a dumbed off version of Visual basic, PHP looks like c++ but not ASP.

 

[KenHigg]

ASP is a dumbed off version of Visual basic, PHP looks like c++ but not ASP.

Sorry, I meant in comparison to cf.

ken

 

[The Stoat]

So will your stuff be for general consumption or will it be restricted use, by people within your (customer's) organisation? If not, you may need to consider sessions, cookies, tokens, etc.

The data captured on the site is only intended to be used by certain people. As i should have direct access to the DB on the server from the Hospitals internal network i can use a desktop fe to the db to create user accounts that will restrict access to the pages. This way there is no way that a user of the site can happen upon a createuser.asp page for example.

When the user navigates to the data entry part of the site they will be asked to provide their user account details which will include a password. The 256 bit encryption of the password will be stored in the database and the entered password will be encrypted and compared to the stored password. If it is correct i'll need to generate a semi-persistent key that will have to be present for each page of the site to be viewed i.e. no good just typing in the url.

And do you just need simple data input type screens?

Certainly i'll need to input data. I'm not sure about simple. It seems their are limitations to using an asp page

This is an example that i'm still not sure what to do with

You ask the server to produce a page that contains a number of check boxes and a drop down list. The user fills in 80% of the details and then comes to the drop down list where their selection isn't available- no way for me to know before hand. So they need to add this to the database. As far as i can see i cam easily add a new item to the list in the DB but would have to reload the page to get the item to appear in the list box and therefore lose the data already entered. Any work arounds i can think of seem messy.

Anyway.

CF is like ASP in that it builds the html on the fly with embeded tags. But the tags and contructs are more like basic where ASP tags kind of looks like C++.

Do you think so. Maybe i'm not using ASP correctly then. All i use asp for is processing forms. i.e. chuck some HTML together a form and some VBscript. submit the form. check the entries, if ok processes the code add stuff to db say congrats to the user. I'm sure it's going to get ugly when i start trying to do graphs but up till now i've just done reports.

So CF is what exactly? and why will this be preferential for us with access Will i be able to allow more user to access the accessdb using CF rather than vbscript??

Thanks

TS

 

[KenHigg]

Hum... This is getting complicated fast...

VBScript

Where I used vbscript was to do some simple form data field validation before the form data was submitted back to the web server. Like, make sure textbox1 was filled in, etc. You see this a lot where a web page form has certain 'Required' text boxes. This takes some of the load off the web server. If you (or anyone here), has ever used vbscript to communicate directly with the web sever, I would of like to hear how (in layman's terms).

CF
I have seen ASP code but have never written any and put it into production so my comments are qualified, but I'll try to explain how I used CF, and how (I understand) they are similar.

CF 101:

In the most basic scenario, when a user requests a web page from sever, all the sever does is send or transfer an html file back to requestor. The sever does nothing but send an html format text file that somebody coded up and ftp'd to the sever.

In CF, the user builds a standard static html web page but also puts additional cf tags in the html text file and saves this file with a .cfm extension. They put this file on a web sever with the cf services installed and running. Now, when a user requests this web page with the .cfm extension, the cf services on the web sever intercepts the page, compiles the cf tags in the page, and builds a new version of the web page with all the dynamic data stuff in it, and sends it to the end user...

This is very similar to the way all (most?) dynamic web pages are built. This forum uses a technology call php to build the dynamic stuff and MySql as the database sever (I think). ASP also works like this - building a web page on the fly.

You can literally do a simple dynamic web page in cf with just two tags. Try doing that with ASP.

Anyway hope that helps a little, I wasn't sure how much you already know...

???
ken

 

[The Stoat]

Hum... This is getting complicated fast...

VBScript

Where I used vbscript was to do some simple form data field validation before the form data was submitted back to the web server. Like, make sure textbox1 was filled in, etc. You see this a lot where a web page form has certain 'Required' text boxes. This takes some of the load off the web server. If you (or anyone here), has ever used vbscript to communicate directly with the web sever, I would of like to hear how (in layman's terms).

CF
I have seen ASP code but have never written any and put it into production so my comments are qualified, but I'll try to explain how I used CF, and how (I understand) they are similar.

CF 101:

In the most basic scenario, when a user requests a web page from sever, all the sever does is send or transfer an html file back to requestor. The sever does nothing but send an html format text file that somebody coded up and ftp'd to the sever.

In CF, the user builds a standard static html web page but also puts additional cf tags in the html text file and saves this file with a .cfm extension. They put this file on a web sever with the cf services installed and running. Now, when a user requests this web page with the .cfm extension, the cf services on the web sever intercepts the page, compiles the cf tags in the page, and builds a new version of the web page with all the dynamic data stuff in it, and sends it to the end user...

This is very similar to the way all (most?) dynamic web pages are built. This forum uses a technology call php to build the dynamic stuff and MySql as the database sever (I think). ASP also works like this - building a web page on the fly.

You can literally do a simple dynamic web page in cf with just two tags. Try doing that with ASP.

Anyway hope that helps a little, I wasn't sure how much you already know...

???
ken

As far as i understood it. If you use vbscript in an asp page the data has to be processed on the server. So if you fill out the form and submit it, the data goes the the server, the script is processed and comes back to the browser. In doing so you can get it to create html by imbeding the tags in the script to be processed as well as write stuff to a db.

Equally when the page is requested from the server, scripts can be run that populate listboxes for example. This sounds exactly like CF.

In order to do client side validation i thought you need to use javascript that is processed by your browser on your machine?.

I can understand how a language may be more efficient i.e. better constructed but surely it is the Db engine that is doing the work in serving the actual data.

TS

 

[KenHigg]

In order to do client side validation i thought you need to use javascript that is processed by your browser on your machine?.

VBScript and JavaScript serve the same purpose - client side scripting. Just two versions of the same methodology. Having said that I think you are using the word 'scripting' out of context.

Doing sever side work can be viewed as 'scripting' but it is more commonly know as compiling or intrepeting.

Here is a pseudo example of cf:

mywebpage.cfm
<cfRecordsource = mytable>
<cfquery Select from mytable
name
address
</cfquery>
<html>
<body>
My Addresses<br><br>
<cfoutput>
#name#
#address#
<br><br>
</cfoutput>
</body>
</html>

This would do a web page like:

My Addresses

john doe
100 pine st

ed white
112 1st ave

jill green
10 peach st

...
...

???
ken

 

[KenHigg]

Anyway - Access and CF works well together. I'm sure ASP makes this provision as well...

Ken

 

[The Stoat]

VBScript and JavaScript serve the same purpose - client side scripting. Just two versions of the same methodology. Having said that I think you are using the word 'scripting' out of context.

Doing sever side work can be viewed as 'scripting' but it is more commonly know as compiling or intrepeting.

Here is a pseudo example of cf:




This would do a web page like:



???
ken

Ok semantics apart. In order to open a table you must need to specify the path of the DB yesno? Does CF provide it's own connectivity or do you use an ODBC connection?

Can you use traditonal controls such as lisboxes txt boxes or is there another method of selecting input?

How do you write to the database?

What validation do you use?

And more importantly is the burden on the db engine reduced by using CF?

Thanks

TS

 

[KenHigg]

Ok semantics apart. In order to open a table you must need to specify the path of the DB yesno? Does CF provide it's own connectivity or do you use an ODBC connection?

There is a CF admin console that sets up the datasource for the cfrecordsource tag (mytable).

<cfRecordsource = mytable>

Actually, I'm not sure it's called cfRecordSource, been a while.

Can you use traditonal controls such as lisboxes txt boxes or is there another method of selecting input?

traditonal controls = traditonal html controls = yes

How do you write to the database?

I'm not sure you may already know about html but: The most common way an html page will send data back to the sever is through a 'Form'. Forms have text boxes, check boxes, etc. When you click a submit button on a from, the values from the text boxes etc, are passed back to the sever. In the case of a cf application, the submit button will be sending the values of mytextfield1 and mytextfield2 to the sever and the requested page getdat.cfm will take those values, and by means of some cf tags, will use them to append or change (or delete) some fields in a table.

"www.myweb.com/folder1/getdat.cfm?mytextfield1=john&mytextfield2=doe"

After the cf tags execute, the sever can send whatever it needs back to the user; a confirmation web page, etc.

What validation do you use?

Html form fields are a bit open ended and simplistic which accounts for the need to do client side script type testing on the data before you attempt to send it back to the server...

And more importantly is the burden on the db engine reduced by using CF?

Not sure what you mean here - Somewhere you have to go through a engine, with CF, ASP, PHP, or whatever.

???
Ken

 

[ReAn]

VBScript and JavaScript serve the same purpose - client side scripting. Just two versions of the same methodology. Having said that I think you are using the word 'scripting' out of context.

Woah Woah Woah!!! Hold up, javascript is client side yes, and so is vbscript, but both can be set up to parse via the asp parser, producing a HTML file (in other words, server-side scripting)

In HTML sent to the client:
<script language="javascript">
<!--

-->
</script>

That is a client block

In ASP parsed by the server:
<script language="javascript">

</script>

That is a server block

More common for asp is:

<script language="vbscript">
</script>

but most people use this form:

<%



%>

anything in these blocks is interpreted by either a dll or exe (for all serverside scripting) and based on the execution path and the data involved, it outputs html code (defined by the way you program it, i can output picture data if you so desire) to the console window, and the server captures this output and spits it in a stream to the client.

PHP, CGI, PERL, ASP, CF, JHTML, SHTML, XML all do the same style. The HTTP protocol dosent support for persistant connections and client-server direct interations.

 

[KenHigg]

Woah Woah Woah!!! Hold up, javascript is client side yes, and so is vbscript, but both can be set up to parse via the asp parser, producing a HTML file (in other words, server-side scripting)

Cool - Said I was a bit slack on ASP...

Maybe it would better if you could advise Stoat - Hope I haven't mucked it up to bad so far - been a while.


Ken

 

[ReAn]

http://www.w3schools.com/

Read SQL, ASP, ADO


Here's an example:

Database: testdb.mdb
__Table: tblTest
____Fields/Data:_____ID___NAME
___________________1___Brian
___________________2___Kevin
___________________3___Gill

Asp:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("testdb.mdb");

Set rst = Server.CreateObject("ADODB.Recordset")
Dim sql

sql = "SELECT * FROM tblTest"
rst.Open sql, conn

rst.MoveFirst()
While Not rst.EOF
  Response.Write "Value(" & rst("ID") & ") = " & rst("NAME") & "<br>";
  rst.MoveNext()
WEnd

Set rst = Nothing

conn.close()
Set conn = Nothing

%>

Output:
Value(1) = Brian
Value(2) = Kevin
Value(3) = Gill

 

[ReAn]

Forgot to mention that if you use ADODB.Command you can call pre-defined queries and input parameters right into a query in the database.

Mind you, you can SELECT * FROM {insert query name here} already, but it's where you can specify parameters and have much more detailed WHERE clauses ad ADODB is prettymuch plain SQL where JetSQL (MS Jet Engine for Access is a super sexified version of SQL)

 

[The Stoat]

Hi,

Thanks for clearing up the client side serverside issues, however - and not intending to be rude here - this seems to have gotten of the beaten path. My question was, which db engine would satisfy my requirements namely that it can be run on a windows iis server and that i can interact with it using vbscript. I have found out that MSDE can have up to 25 concurrent users on the internet and performance should be unaffected. I have also found out that i can run a number of MSDE engines at the same time. So if each one of my 4-5 db's has it's own engine i would be ok. I like the sound of CF and it's something i may look into but at the moment i will stick to vbscript in a standard asp page.

Thanks for all your help.

TS