H O M E | I N T R O D U C T I O N | A B O U T | R E P O R T | P R I N T | E D I T | S E T T I N G S | M A T E R I A L | D A T A

Database
For the past fifteen years soil test data has been stored on the mainframe in a VSAM file. For the past eight years coinciding with a change of units (from Imperial to Metric) data has been stored in the same format and file. This data file works well with mainframe programs but not so well with PC based programs. Since Windows, and PC’s provide us with considerably more control over things like printing, email, and data re-use a PC based program was an obvious choice.

Upon deciding that a PC based application was the best approach it was also decided that moving the VSAM data to a more modern format would also be a good idea. The type of data and the relatively small amount of data allowed us to consider a low end database format. In developer terms this database format is refered to as Jet 4. It’s more commonly known as Microsoft Access and has a file extension *.mdb. It’s important to understand however Microsoft Access is not needed to run STDC.

The location of the data file will reside on a server in Materials & Test Bureau - the server name is Mtnts2 and the application and its database will be deployed in WebStuff/Soil/ directory. Access to the database will be made exclusively using STDC which is found by using Windows Web Explorer and navigating to http://msnts2/soil/.

The database is located in //mtnts2/webstuff/soil/db/. This location should be backed up daily so that your test data will also be backed up daily. The name of the database is soillab.mdb. It contains three tables. The trial data is stored in a single table called soiltest, the user settings are stored in a table called sgnittes, and the materials are stored in a table called materials. The definitions of each table follow.

soiltest Database Table Structure
All data except for the primary key is stored as text strings 50 characters in length – this is due in part to the legacy format of the VSAM file. The primary key is called index and is an Autonumber field of type Long.



To log in, type your user name - this will likely be the same as your window's user name, for example my user name is stefanchukm for Mark Stefanchuk. Tab, or use the mouse to move to the password field. Here you type the password you were given when you received the link to the application. Now click on the Log On button to start the STDC program.

When you click the log on button you will be taken to the "Reports" page. Some or all of the controls will become enabled. The combination of controls enabled depends on your user settings. And these are set by the program administrator.

sgnittes Database Table Structure
Primary key is index and is an AutoNumber of type Long. The following is a breakdown of the remaining table fields.
Userid – the user’s login name, char 50
Userac – the user’s password, char 50
Fullname – the first and last name of the user, char 255
Canread – the user can view reports, bool
Canedit – the user can add trial data, bool
Caneditdiv – 0 to 10 indicating edit control 0 means edit and add – 1 to 10 means add only and only to that division number, Long
Cangrantac – the user can add users and change settings, bool
Emailaddr – email address, char 255


 

materials Database Table Structure
Primary key is index and is an AutoNumber of type Long. The following is a breakdown of the remaining table fields.
stmonth - the month code for the material, char 50, "01", "02", etc.
styear - the year that this material applies to, char 50, 2004, 2003, etc.
material - the name of the material, char 255,  CLAY, SAND, etc