MATLAB DATABASE Configuration

1. MATLAB DATABASE Configuration

2. 1st time use: • A data source is a combination of: • Data we want to access • How to find the data • Data sources are accessed by ODBC drivers

3. Configuration: • Open Matlab • Click Start • Toolboxes • Database • Visual Query Builder

4. Query  Define ODBC Data Source..

5. Click the Add button Choose SQL Server and click Finish

6. Type in the data source name “gene_arch”, choose LIRANSRV for the server and click finish Click OK

7. Now “gene_arch” is showing in the data sources list • Back in Matlab – a conn object of type database was added • Please notice that you receivea valid handle

8. 2nd time use and up: • Any other time after the first configuration just type conn = database('gene_arch','','password')

9. Observing database structure • DB’s tables: dbmeta = dmd(conn); databasetables = tables(dbmeta, 'Gene-Architecture','dbo') • Table’s columns:dbmeta = dmd(conn); tabcolumns = columns(dbmeta, 'Gene- Architecture','dbo',’TableName’)

10. MATLAB Data manipulation

11. Fetch data results= fetch(connection,select) • connection = the connection we established before • select = SQL select statement: 'select ColumnName1,…, ColumnNameN, from TableName‘ • Example: results= fetch(conn,'select Name, Details from Species');

12. Insert data fastinsert(conn,’TableName’,columnNames,data) • columnNames = {‘ColumnName1';’ColumnName2’; … ; ColumnNameN'}; • data = either • cell array (i.e.{'A';'B'}) • cell array in a structure (i.e.st.Names={'A';'B'},st.Details={'Apple';'Banana'}) • Example: fastinsert(conn,'Species`, {'Name';'Details'}, data);

13. Update data update(conn,’TableName’,columnNames,data,where) • where = SQL where clause: {‘where ColumnName1 operator value1’; … ; ‘where ColumnName2 operator value2’} • operators: =, >, <, like, in,… • Example: update(conn,‘Species‘,{‘Details’},data,{‘where Name like ‘’Anopheles gambiae’’’});

14. Execute general query curs = exec(conn,query) • query = Any SQL query. i.e.: 'delete from Orthologous_db where idOrthologous_db=12’ • curs = A cursor, an object which holds information about the transaction with the database • Why not to use exec?

15. Commit & Rollback • commit(conn)makes permanent changes made to the database connection since the last commit or rollback function was run • rollback(conn)reverses changes made to a database using fastinsert, insert, or update (and not to exec!) via the database connection • To use rollback, the AutoCommit flag for conn must be off • set(conn,'AutoCommit','off')

16. DB Preferences: • setdbprefs Sets preferences for retrieval format, errors, NULLs, and more • ErrorHandling: store/report/… • DataReturnFormat: cellarray /numeric/structure • NullStringRead: NULL/’’/… • Example:s = setdbprefs;s.ErrorHandling = 'report‘;setdbprefs(s);

17. A good demo • Can be found here • Combines tools from both bioinformatics and database matlab toolboxes • Note it uses insert instead of fastinsert (slower and older) • A “shlif” of functions can be found in the same folder (shlif.m) • Also, an example connection script (connecting.m)