MATLAB Examples

Tutorial for adodb_toolbox Package

By Jarek Tuszynski

Package adodb_toolbox allows communication with different types of databases through Microsoft's ADO (ActiveX Data Objects) OLEDB component. The package was designed to work on Microsoft SQL Server, Oracle, Microsoft Access, MySQL, other databases.

This package can connect to dozen different database types, perform wide range of different query types and convert results to MATLAB Struct data structures as well as regular cell tables. Matlab struct output uses similar format as used by xml_io_tools and csv2struct libraries. Reading and writing BLOB objects is supported.

This package can be studied, modified, customized, rewritten and used in other packages without any limitations. All code is included and documented. Software is distributed under BSD Licence (included).

Contents

Credit

Parts of this code were based on or inspired by: * Tim Myers oledb*.m functions * Martin Furlan's adodb package * Joerg Buchholz's myblob package

Change History

  • 2010-12-03 - original version

Licence

The package is distributed under BSD Licence

format compact; % viewing preference
clear variables;
type('BSD_Licence.txt')
Copyright (c) 2007, Jaroslaw Tuszynski
All rights reserved.

Redistribution and use in source and binary forms, with or without 
modification, are permitted provided that the following conditions are 
met:

    * Redistributions of source code must retain the above copyright 
      notice, this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright 
      notice, this list of conditions and the following disclaimer in 
      the documentation and/or other materials provided with the distribution
      
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
POSSIBILITY OF SUCH DAMAGE.

Access: Open connection to MS Access file

See websites below for syntax of the connection strings

DB = adodb_connect('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=adotest.accdb; Persist Security Info=False;');

Access: Read single record from DB

record6 = adodb_query(DB, 'select top 1 * from TestTable where office=6');
disp(record6)
      lastname: 'Simpson'
     firstname: 'Maggie'
    profession: 'Musician'
        office: 6

Access: Read multiple records from DB

[Struct, Table] = adodb_query(DB, 'select * from TestTable');
disp('Output in in struct format:')
disp(Struct)
disp('')
disp('Output in in table format:')
disp(Table)
Output in in struct format:
      lastname: {13x1 cell}
     firstname: {13x1 cell}
    profession: {13x1 cell}
        office: {13x1 cell}
Output in in table format:
    'Smith'        'John'       'Minister'            [ 1]
    'Myers'        'Tim'        'Programmer'          [ 2]
    'Simpson'      'Bart'       'Skateboarder'        [ 3]
    'Simpson'      'Homer'      'Nuclear Engineer'    [ 4]
    'Simpson'      'Marge'      'Home Maker'          [ 5]
    'Schmoe'       'Joe'        'Stock Broker'        [ 7]
    'Appleseed'    'Johnny'     'Arborist'            [ 8]
    'Boone'        'Daniel'     'Hunter'              [ 9]
    'Lincoln'      'Abraham'    'President'           [10]
    'John'         'DeLemon'    'Programmer'          [13]
    'Peter'        'Stone'      'Preacher'            [12]
    'Rogerio'      'Dias'       'Dottore animali'     [11]
    'Simpson'      'Maggie'     'Musician'            [ 6]

Access: Struct output provides more intuitive interface

for i = 1:length(Struct.lastname)
  fprintf('%s, %s, %s, %i\n', Struct.lastname{i}, Struct.firstname{i}, ...
                              Struct.profession{i}, Struct.office{i});
end
Smith, John, Minister, 1
Myers, Tim, Programmer, 2
Simpson, Bart, Skateboarder, 3
Simpson, Homer, Nuclear Engineer, 4
Simpson, Marge, Home Maker, 5
Schmoe, Joe, Stock Broker, 7
Appleseed, Johnny, Arborist, 8
Boone, Daniel, Hunter, 9
Lincoln, Abraham, President, 10
John, DeLemon, Programmer, 13
Peter, Stone, Preacher, 12
Rogerio, Dias, Dottore animali, 11
Simpson, Maggie, Musician, 6

Access: Delete record and verify that number of records decresed

before = adodb_query(DB, 'select count(*) as num_rec from TestTable');
adodb_query(DB, 'delete from TestTable where office=6');
after  = adodb_query(DB, 'select count(*) as num_rec from TestTable');
fprintf('Number of records before deletion is %i, and after is %i\n', ...
  before.num_rec, after.num_rec);
Number of records before deletion is 13, and after is 12

Access: Insert record and verify that number of records incresed

sql = 'INSERT INTO TestTable (LASTNAME, FIRSTNAME, PROFESSION, OFFICE) VALUES (''%s'', ''%s'', ''%s'', %i) ';
sql = sprintf(sql, record6.lastname,   record6.firstname, ...
                   record6.profession, record6.office);
adodb_query(DB, sql);
after  = adodb_query(DB, 'select count(*) as num_rec from TestTable');
fprintf('Number of records after insertion is %i\n', after.num_rec);
Number of records after insertion is 13

Access: Struct output default column labels

In case column labeld can not be deduced from select statement adodb_query will use A, B, C, ... convention, similat to Excel

x = adodb_query(DB, 'select count(*), max(office) from TestTable');
disp(x)
    A: 13
    B: 13

Access: Close DB connection

DB.release;

MS SQL: Initialize MS SQL database

cn_str = 'PROVIDER=SQLOLEDB; Data Source=database-name; initial catalog=ANALYSIS; User ID=john_doe; password=pword';
DB = adodb_cn(cn_str, 240);

MS SQL: Create TEMP table

sql = ['CREATE TABLE TEMP ( ',...
         'blob    varbinary(max), ',...
         'name    varchar(64)',...
       ');'];
adodb_query(DB, sql);

MS SQL: Insert two new records

adodb_query(DB, ['insert into TEMP (name) values (''football''); ',...
                 'insert into TEMP (name) values (''baseball''); ']);
[~, Table] = adodb_query(DB, 'select * from TEMP');
disp(Table);
    [NaN]    'football'
    [NaN]    'baseball'

MS SQL: Update one of the records to add a blob

selectRecordSql = 'SELECT TOP(1) * FROM TEMP WHERE name=''football''';
fname = 'football.jpg';
imwrite(imread(fname),fname); % copy file to current directry
adodb_update_blob(DB, selectRecordSql, 'blob', fname);
[~, Table] = adodb_query(DB, 'select * from TEMP');
disp(Table);
    [1x13155 uint8]    'football'
    [          NaN]    'baseball'

MS SQL: Read a blob from the database and store it to the hard disk

x = adodb_query(DB, selectRecordSql);
fid = fopen('MyFootball.jpg', 'wb');
fwrite(fid, x.blob, 'uint8');     % dump the raw binary to the hard disk
fclose(fid);
I = imread('MyFootball.jpg');     % read it as an image
imshow(I);

MS SQL: Run statement with multiple queries

sql = 'Select count(*) from TEMP;    select * from TEMP';
[Struct Table] = adodb_query(DB, sql);
disp('Output in in struct format:')
disp(Struct)
disp('Output in in table format:')
disp(Table)
Output in in struct format:
    A: 2
Output in in table format:
    [2]

MS SQL: Run statement with multiple queries with Pref.MultipleQuery = true flag

Pref = []; Pref.MultipleQuery = true;
[Struct Table] = adodb_query(DB, sql, Pref);
disp('Output of query 1 in struct format:')
disp(Struct{1})
disp('Output of query 1 in table format:')
disp(Table{1})
disp('Output of query 2 in struct format:')
disp(Struct{2})
disp('Output of query 2 in table format:')
disp(Table{2})
Output of query 1 in struct format:
    A: 2
Output of query 1 in table format:
    [2]
Output of query 2 in struct format:
    blob: {2x1 cell}
    name: {2x1 cell}
Output of query 2 in table format:
    [1x13155 uint8]    'football'
    [          NaN]    'baseball'

MS SQL: Run multi-query sql with an incorect second statement

First correct statement did not modify database

sql = ['Update TEMP set name=''basketball'' where name=''baseball''; ',...
       'select WrongName from TEMP'];
try
  x = adodb_query(DB, sql);
catch ME
  disp(ME.message);
end
[~, TEMP] = adodb_query(DB, 'select * from TEMP');
disp('Output in table format:')
disp(TEMP)