Accessing mySQL database through Matlab This documents describes the requirements and explains the functions matlab uses to perform queries to the mySQL database. Requirements 1. The ODBC drivers for mySQL must already be installed. You can get these from http://spx.arizona.edu/database.htm 2. The database that will be accessed must be added as an ODBC data source. The procedure to do this is described at http://spx.arizona.edu/Database/Setup%20Instructions.pdf 3. Matlab R2006b or newer with database toolbox Matlab Functions In order to access the database first a connection to the database must be created. This can be done by the database() function. Below the function is explained. database(data source, username, password) data source should be the name of the data source in the ODBC for Windows given as a string. This has to be configured before running MATLAB and the procedure to do this is described in the Setup Database as a source on a workstation guide. username is the username the user has on the database he/she wishes to access. This should also be given as a string. password This must be the password given with the above username to access the database. Again it should be supplied to the database() function as a string. Once the connection is established queries to the database can be performed using the fetch() function. This function is structured as follows:
fetch(Connection Pointer,SQL Syntax)
Connection Pointer This is the connection created with the database() function SQL Syntax This is a string containing the desired SQL query These are the most common functions that can be used to execute queries on a database. Other functions are available from the database toolbox. Refer to the toolbox manual for more information. Example Code The code listed here will create a connection to the patientstudy database and will create a report of how many patients were added within a given period of time and which studies they participate in.
%This file demonstrates the use of the database toolbox in Matlab %It will create a connection to the patientstudy database and will %retrieve all the patients that were added within a given period of time %and will find which studies each is participating in and create an excel %file
clear all %Input data StartDate = 'MM/DD/YYYY'; EndDate = 'MM/DD/YYYY'; %It would be best if username & password were asked from the user %during runtime for security purposes dbUsername = 'Username'; %Username for the database dbPassword = 'Password'; %Passwor associated with the above username ExcelFileName = 'PatientReport.xls'; %The filename for the output excel file %Process the input data to construct the SQL query StartSlashLoc = find(StartDate == '/'); %Find the locations of the slash EndSlashLoc = find(EndDate == '/'); %in the start and end date to determine %the month, day, year %Create the SQL date in the SQL format YYYY-MM-DD SQLStartDate = [StartDate(StartSlashLoc(2)+1:end) '-'. StartDate(1:StartSlashLoc(1)-1) '-'. StartDate(StartSlashLoc(1)+1:StartSlashLoc(2)-1) ]; SQLEndDate = [EndDate(EndSlashLoc(2)+1:end) '-'. EndDate(1:EndSlashLoc(1)-1) '-'. EndDate(EndSlashLoc(1)+1:EndSlashLoc(2)-1) ]; %Construct the SQL query SQLquery = ['select ID from patient where create_date > '''. SQLStartDate ''' and create_date < ''' SQLEndDate '''']; %Create the connection to the database with the credentials specified above NewConn = database('patientstudy',dbUsername,dbPassword); %Execute the query on the database and fetch the data DataPatients = fetch(NewConn, SQLquery); %Check if data are available for the given dates. If no data are %available then give an error and stop execution if (isempty(DataPatients)) errordlg('No patients were found within that date range') close(NewConn); % close the connection on error return end %Find on which study each patient is currently enrolled %Get the study descriptions from the STUDY table StudyIDs = fetch(NewConn, 'select STUDY_NUMBER from study'); DataStudies = cell(0); %define as empty cell array for Loop1 = 1:length(DataPatients) %For all patients find which studies they are enrolled from the %Study_Consent table SQLquery = ['select study_id from study_consent where patient_id ='. ,num2str(DataPatients{Loop1})]; %Execute the SQL statement and get the data TempDat = fetch(NewConn, SQLquery); %if patient is not enrolled in any studies yet then fill the field %with the default message if (isempty(TempDat)) DataStudies(Loop1) = {'Patient not enrolled in any studies'}; else %Fill in the first study as text

DataStudies(Loop1) = StudyIDs(TempDat{1}); %If patient is enrolled in more studies then append those to %the previous study separated by a comma if (length(TempDat) ~= 1) DataStudies(Loop1) = StudyIDs(TempDat{1}); for Loop2 = 2:length(TempDat) DataStudies(Loop1) = {[cell2mat(DataStudies(Loop1)),', '. cell2mat(StudyIDs(TempDat{Loop2}))]}; end end end end close(NewConn); %Close the connection if all is done %Write the excel file with the filename specified above xlswrite(ExcelFileName, [{'Patient number'},{'Studies enrolled'};. DataPatients, DataStudies']);

Geodise Database Toolbox for Matlab Tutorial Version 0.2
J. L. Wason, Z. Jiao, and S. J. Cox {j.l.wason, z.jiao, sjc}@soton.ac.uk Southampton Regional e-Science Centre School of Engineering Sciences University of Southampton Higheld, Southampton SO17 1BJ United Kingdom
1. If you do not have a CEDC Unix user account contact Neil Bresslo (N.W.Bresslo@soton.ac.uk) to request one. 2. Follow the instructions from Globus Basics Tutorial to setup your certicates, and Geodise Compute Toolbox for Matlab Tutorial to set up the Java CoG. 3. Contact Jasmin Wason (j.l.wason@soton.ac.uk) to have your user details (username and certicate subject) added to the Geodise database. The certicate subject can be found near the top of your usercert.pem le, often located in the.globus directory of your home directory. e.g. subject=/C=UK/O=eScience/OU=Southampton/L=SeSC/CN=joe bloggs where the certicate subject is the part after subject=. 4. Contact Hakki Eres (Hakki.Eres@soton.ac.uk) to have your certicate subject and CEDC Unix username added to the gridmap le on utp-10 so that you can store and retrieve les from there. 5. Login to utp-10 6. Matlab on utp-10 has been congured, so you do not need to make any conguration changes. 7. Copy the.geodise directory from /usr/local/geodise/DatabaseToolbox/ into your home directory. 8. Start Matlab. % matlab &
c The Geodise Project, University of Southampton, 2001-2004
9. Generate your proxy certicate. >> gd_createproxy
A new window pops up, and you need to enter your certicate passphrase and click Create button. After the proxy has been generated, you click Cancel and press Enter at the Matlab prompt. 10. Archive a le with some metadata. Create a metadata structure containing some information that describes your le. This can be any combination of doubles, strings, arrays, cell arrays, complex numbers and substructures. >> m.model.name = test_design; >> m.model.params = [1 4.7 5.3]; >> m.product = 25.5431;
Add some standard information (localName, format, comment or version) about the le. >> m.standard.comment = Test design model file; >> m.standard.version = 1.2.0;
Archive the le and metadata. >> fileID = gd_archive(/home/jlw/myfile.txt, m) fileID = myfile_txt_4957b83b-322c-4f06-822c-4da38791cdfa
In addition to the optional metadata structure, gd_archive takes a string representing the full path name of a local le and stores it on a remote le store (specied in the /home/username/.geodise/ClientConfig.xml le). An ID is returned which is a unique handle that can be used to retrieve the le. The metadata is stored in a database and can be later queried to help you nd relevant les. Some additional metadata is automatically generated and stored in the standard substructure. This consists of localName (the original name of the le), byteSize, format, archiveDate, createDate (when the original le was created/modied) and userID. You can specify your own overriding values for standard.localName and standard.format if you prefer. 11. Query le metadata. A call to gd_query returns a cell array of structures, one for each matching result.

>> result = gd_query(standard.version=1.2.0 & product>25.4) result = [1x1 struct] >> result{1} ans = standard: [1x1 struct] model: [1x1 struct] product: 25.5431 12. Display query results. gd_display is a convenient way to view your query results. >> gd_display(result) *** Content of the structure result{1} (Total structures: 1) *** standard.ID: myfile_txt_4957b83b-322c-4f06-822c-4da38791cdfa standard.localName: myfile.txt standard.byteSize: 34 standard.format: txt standard.createDate: 2003-09-18 17:08:31 standard.archiveDate: 2003-12-09 15:09:48 standard.userID: jlw standard.comment: Test design model file standard.version: 1.2.0 standard.datagroups: model.name: test_design model.params: 1.0000 4.7000 5.3000 product: 25.5431 *** No more results. *** 13. Query GUI. Use gd_query without any input arguments to use the graphical query interface. Hyperlinks are supported for downloading and browsing data. See Fig. 1. 14. Retrieve a le to the local lesystem. A le can be retrieved to the local lesystem by specifying its unique ID. This string is returned when the le is archived and also appears in the metadata query results as standard.ID. >> ID = result{1}.standard.ID; % Retrieve to a specified file location >> gd_retrieve(ID, /home/jlw/myfile2.txt) ans = /home/jlw/myfile2.txt
Figure 1: Query GUI. % Retrieve to a specified directory (the original file % name is used) >> gd_retrieve(ID, /home/jlw/filesdir) ans = /home/jlw/filesdir/myfile.txt
15. Archive, query and retrieve Matlab variables. >> >> >> >> v.width = 12; v.height = 6; metadata.standard.comment = measurements variable; varID = gd_archive(v, metadata);
% If the archived variable is a structure it can be % queried >> gd_query(height=6, var); % You can also query a variables metadata
>> gd_query(standard.comment=measurements variable,. >> varmeta); >> x = gd_retrieve(varID) x = width: 12 height: 6
16. Grouping data. Related data can be logically grouped together using a datagroup. % Specify metadata that applies to the whole group >> group_metadata.standard.comment =. >> Group of data for experiment 123; % Create a datagroup, giving it a name >> groupID=gd_datagroup(Experiment 123,group_metadata); % Add archived files or variables to the datagroup >> gd_datagroupadd(groupID,fileID); >> gd_datagroupadd(groupID,varID); % Archive a new file (with no metadata this time) and % add it to the datagroup. >> gd_archive(/home/jlw/anotherfile.txt,[],groupID); % The datagroup metadata now contains references to the % files and variables it contains. >> result=gd_query(standard.datagroupname=Experiment 123,. >> datagroup); >> gd_display(result) *** Content of the structure result{1} (Total structures: 1) *** standard.ID: dg_1ef2641f-928f-40d9-a575-7f229856493f standard.datagroupname: Experiment 123 standard.archiveDate: 2003-12-09 15:44:57 standard.userID: jlw standard.comment: Group of data for experiment 123 standard.files.fileID: myfile_txt_4957b83b-322c-4f06-822c-4da38791cdfa standard.files.fileID: anotherfile_txt_06e8cd18-974s-49ff-ba3b-d9da7ca6e057 standard.vars.varID: var_b373b432-127b-4a13-9f6e-a91c8f33f5b0 *** No more results. ***

Metadata for the les and variables also contain references to the datagroup(s) they belong to.
17. Granting access to data. The gd_addusers function allows you to grant other users permission to query particular les, variables and datagroups that you own. These users may also retrieve the variables to their local Matlab workspace and the les to their local lesystem (providing they have read permission for the appropriate directory on the Globus le server, e.g. utp-10). In the following examples replace user bob with an existing user, e.g. jlw. >> users = {bob}; >> gd_addusers(ID, users);
Access may also be granted as part of the metadata when a le or variable is archived, or when a datagroup is created. >> m.access.users = {bob}; >> gd_archive(/home/jlw/myfile.txt, m);
18. Further information. All of these functions have help information which can be viewed by using the help command in Matlab. >> help gd_datagroupadd gd_datagroupadd adds some data to a datagroup. gd_datagroupadd(datagroupID, dataID) The datagroup is specified with the datagroupID handle and the data is specified with the dataID handle. The datagroup must have been created with gd_datagroup and the data must be file or a structure that was archived using gd_archive.