Database (old)

Setup connector

• Download and install the latest ODBC connector from MySQL
• Check for old versions and uninstall them
  • Control Panel => Programs and Features 
  • Look for MySQL Connector/ODBC /[version/] and uninstall any remain old versions
  • Delete any Data Sources that use this old driver (see next section)
• Create the "sidata64" DSN connection to the "sidata" database
  • Control Panel -> Administrative Tools -> Data Sources (ODBC) 
  • Check the connection sidata64 is not already setup in User DSN or System DSN
    • To test an existing ODBC connection, select sidata64 and click "Configure" then Test (see below)
  • System DSN, "Add..."

• • Select the MySQL driver, Finish

• • Enter the "Data Source Name" sidata64, the server name, and the MySQL user and password.

• • Click "Test" - it should return OK. Then select the "sidata" database (ODBC is only needed for the old SPM8 database, and the Access file for connecting subjects) 

Background

The database contains coded subject information, including links to all MRI, physiology and other studies and measurements obtained from subjects. An Access file stored on the study data drive (e.g., W:\Database\ or S:\Database) allows added, viewing and editing through the "subject" form. (See also Using MS Access.])

The actual database is stored on the server (kiwi), and is a MySQL database. There is one database for heart failure (hfdata) and one for everything else (sidata). Connections are made through ODBC.

Backup: setup a regular database backup through MySQL Database Backup.

For Windows XP, see Installing 32-bit MySQL Connector on 64-bit Systems.

Changing Subject Designation in Database

Installing MySQL

Automatically Update TIMESTAMP

For some odd reason the default behavior of the TIMESTAMP was only to modify on creation; here's how to change that:
curs = exec(conn,'ALTER TABLE event MODIFY modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')

MS Access Database

Make sure connector is setup ok. The HF data are accessed through a "DSN" (Data Source Name) hfdata64, and the harperlab data through a connector sidata64.

The Access file for the harperlab data are on the "Study Data" drive (usually W:), and the HF data are on the "HF Study Data" drive (usually S:). On each, there is a subfolder "Database," within which is a file "HF 64-bit DSN (intranet)" or "sidata 64-bit DSN (intranet)."

Link tables

Open the database (often called sidata.mdb), go to the Tables view, right-click over the tables, select "linked Table Manager" (see below)

Click Select All, check Always prompt for new location, click OK

Click Machine Data Source tab, select sidata64 (or sometimes sidata), click OK

You should be done; to check, open (double-click) a table and make sure you don't get an error (if you have loaded the database you should see entries in various tables, e.g., subjects).

Note: do not set the "BIGINT to INT" option in the connector; this setting is recommended for MS Access compatibility, but causes problems for CSPM, as the id/exam numbers are LONGINT's.