--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/m-toolbox/classes/+utils/@jmysql/dbquery.m	Wed Nov 23 19:22:13 2011 +0100
@@ -0,0 +1,164 @@
+% DBQUERY query an AO repository database.
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+%
+% DESCRIPTION: DBQUERY query an AO repository database.
+%
+% CALL:        info = dbquery(conn);
+%              info = dbquery(conn, query);
+%              info = dbquery(conn, table, query);
+%
+% INPUTS:
+%           conn   - a database connection object such as that returned by
+%                    utils.jmysql.connect().
+%           query  - a valid MySQL query string
+%           table  - a table name
+%
+% OUTPUTS:
+%           info - the returned 'info' structure contains the fields from
+%                  each matching record.
+%
+% EXAMPLES:
+%
+%   >> info  = utils.jmysql.dbquery(conn, 'select * from objmeta where id>1000 and id<2000');
+%   >> info  = utils.jmysql.dbquery(conn, 'ao',           'id>1000 and id<2000');
+%   >> info  = utils.jmysql.dbquery(conn, 'objmeta',      'name like "x12"');
+%   >> info  = utils.jmysql.dbquery(conn, 'users',        'username="aouser"');
+%   >> info  = utils.jmysql.dbquery(conn, 'collections',  'id=3');
+%   >> info  = utils.jmysql.dbquery(conn, 'collections',  'obj_ids="1,2"');
+%   >> info  = utils.jmysql.dbquery(conn, 'transactions', 'user_id=3');
+%   >> info  = utils.jmysql.dbquery(conn, 'transactions', 'obj_id=56');
+%
+%   >> tables  = utils.jmysql.dbquery(conn)
+%
+%   The 'tables' cell-array will contain a list of the tables in the database.
+%
+%   >> info  = utils.jmysql.dbquery(conn, query)
+%
+%   The 'info' cell-array will contain the results from the SQL query.
+%
+% VERSION:     $Id: dbquery.m,v 1.2 2010/01/19 20:55:30 ingo Exp $
+%
+% HISTORY:     10-05-2007 M Hewitson
+%                 Creation
+%
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+
+function varargout = dbquery(varargin)
+
+  conn = varargin{1};
+  if ~isa(conn, 'mpipeline.repository.RepositoryConnection')
+    error('### First input must be a database connection object.');
+  end
+
+  if nargin == 1
+    % get table list
+    info = getTableList(conn);
+
+  elseif nargin == 2
+    % execute query
+    info = simpleQuery(conn, varargin{2});
+
+  elseif nargin == 3
+    % query a table
+    table    = varargin{2};
+    query    = varargin{3};
+    info = runQuery(conn, table, query);
+  else
+    error('### Incorrect inputs.');
+  end
+
+  varargout{1} = info;
+end
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+% Get table list
+function info = getTableList(conn)
+
+  info = {};
+  
+  % open a connection
+  try
+    q = 'show tables';
+    results = conn.query(q);
+    while results.next
+      info = [info {char(results.getString(1))}];
+    end
+  catch
+    error('### Failed to get table list.');
+  end
+end
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+% Get field list
+function info = getFieldList(conn, table)
+
+  info = {};
+  try
+    q = ['describe ' table];
+    results = conn.query(q);
+    while results.next
+      info = [info {char(results.getObject(1))}];
+    end
+  catch
+    error('### Failed to get field list.');
+  end
+end
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+% Get field list
+function info = simpleQuery(conn, q)
+
+  % open a connection
+  info = {};
+  try
+    results = conn.query(q);
+    mt = results.getMetaData;
+    Ncols = mt.getColumnCount;
+    row = 1;
+    while results.next
+      for kk=1:Ncols
+        info{row,kk} = convertValue(results.getObject(kk));
+      end
+      row = row + 1;
+    end
+    
+  catch ME
+    disp(ME.message)
+    error('### Failed to execute query.');
+  end
+end
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+% Run a query
+function info = runQuery(conn, table, query)
+
+  % Run query
+  info = {};
+
+  fields = getFieldList(conn, table);
+  f = '';
+  fs = {};
+  for j=1:length(fields)
+    % special cases
+    f  = [f fields{j} ',' ];
+    fs = [fs fields(j)];
+  end
+  q = sprintf('select %s  from %s where %s', f(1:end-1), table, query);
+  disp(['** QUERY: ' q]);
+  try
+    info = simpleQuery(conn, q);
+  catch
+    error('### Failed to query table.');
+  end
+end
+
+function val = convertValue(val)
+  
+  switch class(val)
+    case 'java.sql.Timestamp'
+      val = char(val);
+    otherwise
+  end
+end
+
+