ODBC Data Source Configuration
.NET files
Access Client Installation & Configuration
Setting up TrialDB requires that you set up a Database Server, a Web server and an Access client. In production mode, each of these should be on a separate machine. In test mode, all three may be on a single machine if you are short of hardware, but it is preferable that the Access client be on a separate machine from the Web/Database server. The skill set required to work with the Server and the Client are different- the latter is meant for power-user study designers who are not necessarily programmers,and so should be installed on a desktop CPU rather than a server-class machine.
The first step in using TrialDB is to ensure that your Web browser is set up correctly. Currently, TrialDB works with Microsoft Internet Explorer version 5.5 or greater on PCs. (We do not support the Macintosh natively, unless you use Virtual PC, because IE for the Mac does not support VBScript .)
You must have 128-bit encryption enabled, so as to be able to use https. You may need to download the 128-bit encryption add-in from Microsoft's Web site.
TrialDB uses popups to pass information from one Web page to another, in at least two circumstances.
When you need to search controlled vocabularies while you are doing data entry, the vocabulary screen opens in a pop-up window, and once you pick the entry you need, it is passed back to the data entry page. (This process uses the ShowModalDialog() routine of the Internet Explorer programming interface.)
"Server-side functions" enable you to execute a routine on the Web server that returns a value that you can use elsewhere - typically this value is part of a field's formula. What happens here is that, when the contents of certain fields change, a pop-up window opens and closes momentarily (you see a fllicker on the screen.)
The Google popup blocker and the blocker built into WIndows XP Service Pack 2 can interfer with TrialDB's operation. In this case, holding the control key down while you perform a mouse action (e.g., clicking a button that lets you search a vocabulary, or mousing into another field) will suppress the popup.
The current version of TrialDB requires Oracle version 8.1 or greater, or SQL Server 2000 or greater. (The SQL Server schema was developed by Tom Hoen, currently at the Johns Hopkins center for aging.) We are just in the process of testing SQL Server 2005.
The file TrialDB_oracle_schema.zip contains the complete Data Definition Language (DDL) instructions for creating a database schema. This script is contained in the file The following steps should be performed in sequence:
create table space ACT_PROD
create user ACT_PROD
create tables in the ACT_PROD tablespace
create foreign keys
create index
create views
create sequences
create triggers
You may have to edit some scripts according to your local Oracle environment. For instance, you may not be able to assign DBA privilege to user ACT_PROD.
The contents should then be restored from the file TrialDB_oracle.dmp
Installation is much simpler. Unzip the file TrialdB_sql_server.zip and restore its contents using the SQL Server Enterprise Manager. (By default, this will restore to a database called act_db, but you can change this.)
(Note: Oracle keeps changing the name of the middleware piece used to populate the TNSNAMES.ORA configuration file - which stores information about the Oracle databases that you wish to connect to from your desktop - with each new release of Oracle. In Oracle 8, this was called Net8 configuration; in Oracle 9, it's just "net" configuration assistant.)
Open Oracle Net Configuration Assistant (Oracle 8) or Net8 Easy Config (Oracle 8).
Add a new service called "ACTRIS". Click 'Next'
Choose TCP/IP and in the subsequent screens, input the host server, port and database SID for the database that you previously created. (For port, you can use the default
Connectivity to Microsoft SQL Server is built into Microsoft Office, so you don't need to do anything special.
Create an ODBC datasource to your database, give it any name you like. (The installation at Yale uses the name "actris" the old name for TrialDB). If using Oracle, make sure that you have the Oracle ODBC driver installed on your desktop machine. (Warning: Don't use the Microsoft ODBC driver for Oracle- this is pretty buggy with respect to Access.)
For SQL Server, you should have no problems specifying the data source. When you connect, it is preferable that you specify that you will be using Integrated (Windows) authentication; this is much more secure than the older SQL Server authentication, and also has the advantage that you will not be prompted repeatedly for a password. (We assume, of course, that access to your desktop is restricted using mechanisms such as password-protected screen savers.)
The Web server should have Internet Information Server, and should be running Windows 2000 Server or Enterprise Editions. You should install the .NET framework, version 1.1 or greater, on the machine. This is available as a free download from Microsoft. (Currently, the reporting code and the module that handles binary data are written using .NET: the rest will be moved over to .NET gradually.)
The following steps should be performed
Extract TrialDB_Web_Files.zip to a local folder on your system. At Yale. this folder is called act_db, and lies under the wwwroot folder.
Open the Internet Services Manager console for IIS.
Right click on the "Default Web Site" node on the tree view in the left frame.
Select "New" and "Virtual Directory".
Now choose and alias and browse to the folder where you extracted the zip file.
The start page for the application is "TrialDB.asp".
Create an ODBC data source on your Web server. The .NET part of the Web application will access the database via ODBC.
The folders ExtractsNET and ReportsNew must be designated as applications. (These are .NEt apps.)
The file TrialDB_net.zip contains all the files necessary for the .NET component of Act_DB (as of Dec '05 - in future, this component will increase and the .ASP component will correspondingly shrink, possibly to the point where it ceases to exist. Inside this file is a Microsoft Word document called NET Upgrade Instructions.doc, which should tell you how to set these files up.The reports, extracts, pedigree drawing, sample tracking, and patient-enterable forms modules are built with .NET, as are a few small screens used elsewhere (a "graphic calendar" and a file upload form).
During development, you will set up two projects:
WebEAV: a class library that provides user-interface widgets and utility routines used by TrialDB.
TrialDB - the Web site project. This uses the WebEAV library, among other things, so you should create a two-project solution to facilitate interactive debugging.
In production, all the ASPX files go in the root folder (e.g, ACT_DB). The two .NET binaries, WebEAV.dll and TrialDB.dll, go into a bin folder under act_db. The bin folder also contains other dlls (such as wrappers around COM objects that the WEbEAV library uses, as well as an open-source library that allows you to zip/unzip files: this is used by the Extracts module.)
To enable secure communication between ASP and ASP.NET (which run in separate processes), your Windows 2000/2003 Server must have Microsoft Message Queue enabled on it, since MSMQ is used for the communication.
Install the Oracle client tools and ODBC drivers on your desktop CPU.
Use Oracle's Net Configuration Assistant to create a TNS Service - call it whatever you like - to point to the Oracle database that you have created. If the database is on a different machine, you will need to specify information such as the machine's IP address, and the name of the database ID.
Unzip the Access client and open it. When you open it, you may be taken to a "Welcome screen". if so, close the Welcome screen and go to the Database Window.
Refresh Linked tables as follows. Choose Tools..Database Utilities...Linked Table Manager from the menu. Click the "Select All" button, and check the box that says "Prompt for New Location". When you click the OK button, you are asked for which data source to choose. Specify the ODBC data source that you created earlier.
Open the module Global_variables in the client, and set the values of the
constants SysUserName and SysPwd to be the username and password for a
"privileged super-user" who is allowed full access - including creation and
deletion of tables - for the TrialDB database at your installation.
(Note: this client is not given to multiple people at your site, but stays
within your facility. End-users do not need to know of its existence.)
Open the table ADMINVAR from your client. Edit the value of the DSN variable, replace the contents with the name of your ODBC data source, e.g., ACTRIS. This value is not case-sensitive. (See "The AdminVar table" for the importance of this table.)