1 / 17

MATLAB DATABASE Configuration

MATLAB DATABASE Configuration. 1 st 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. Configuration: Open Matlab Click Start Toolboxes Database Visual Query Builder. Query

Download Presentation

MATLAB DATABASE Configuration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related