--- a/m-toolbox/classes/+utils/@jmysql/connect.m	Mon Dec 05 16:20:06 2011 +0100
+++ b/m-toolbox/classes/+utils/@jmysql/connect.m	Mon Dec 05 16:20:06 2011 +0100
@@ -11,55 +11,15 @@
 % in the LTPDA user preferences.
 %
 % CALL:        conn = connect(hostname)
-%              conn = connect(hostname, dbname)
-%              conn = connect(hostname, dbname, dbuser, dbpass)
+%              conn = connect(hostname, database)
+%              conn = connect(hostname, database, username, password)
 %
 % VERSION:     $Id: connect.m,v 1.13 2011/04/01 08:36:49 hewitson Exp $
 %
 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
 
-function varargout = connect(varargin)
-  
-  dbuser = '';
-  dbpass = '';
-  dbhost = '';
-  dbname = '';
+function conn = connect(varargin)
+
+  conn = LTPDADatabaseConnectionManager().connect(varargin{:});
   
-  if nargin == 1
-    dbhost = varargin{1};
-  elseif nargin == 2
-    dbhost = varargin{1};
-    dbname = varargin{2};
-  elseif nargin == 3
-    dbhost = varargin{1};
-    dbname = varargin{2};
-  elseif nargin == 4
-    dbhost = varargin{1};
-    dbname = varargin{2};
-    dbuser = varargin{3};
-    dbpass = varargin{4};
-  elseif nargin == 5
-    dbhost = varargin{1};
-    dbname = varargin{2};
-    dbuser = varargin{3};
-    dbpass = varargin{4};
-  end
-  
-  rm = LTPDARepositoryManager();
-  conn = rm.findConnections(dbhost, dbname, dbuser, dbpass);
-  
-  if isempty(conn)
-    conn = rm.newConnection(dbhost, dbname, dbuser, dbpass);
-  end
-  
-  if ~isempty(dbpass) && isempty(char(conn.getPassword))
-    conn.setPassword(dbpass)
-  end
-  
-  % If we have more than one matching connection, let the user choose.
-  if numel(conn) > 1
-    conn = rm.manager.selectConnection([]);
-  end
-  
-  varargout{1} = conn(1);
 end

--- a/m-toolbox/classes/+utils/@jmysql/dbquery.m	Mon Dec 05 16:20:06 2011 +0100
+++ b/m-toolbox/classes/+utils/@jmysql/dbquery.m	Mon Dec 05 16:20:06 2011 +0100
@@ -1,27 +1,25 @@
-% DBQUERY query an AO repository database.
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-%
-% DESCRIPTION: DBQUERY query an AO repository database.
+function varargout = dbquery(conn, varargin)
+% 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
+%
+%    conn  - a database connection object implementing java.sql.Connection
+%   query  - a valid MySQL query string
+%   table  - a table name
 %
 % OUTPUTS:
-%           info - the returned 'info' structure contains the fields from
-%                  each matching record.
+%
+%     info - structure containing 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, '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"');
@@ -30,111 +28,65 @@
 %
 %   >> tables  = utils.jmysql.dbquery(conn)
 %
-%   The 'tables' cell-array will contain a list of the tables in the database.
+%   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.
+%   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.');
+  if ~isa(conn, 'java.sql.Connection')
+    error('### first argument should be a java.sql.Connection object');
   end
 
-  if nargin == 1
-    % get table list
-    info = getTableList(conn);
-
-  elseif nargin == 2
-    % execute query
-    info = simpleQuery(conn, varargin{2});
+  switch nargin
+    case 1
+      % get table list
+      try
+        info = utils.mysql.execute(conn, 'SHOW TABLES');
+      catch ex
+        error('### failed to get table list. %s', ex.message);
+      end
 
-  elseif nargin == 3
-    % query a table
-    table    = varargin{2};
-    query    = varargin{3};
-    info = runQuery(conn, table, query);
-  else
-    error('### Incorrect inputs.');
+    case 2
+      % execute query
+      try
+        info = utils.mysql.execute(conn, varargin{1});
+      catch ex
+        error('### failed to execute query. %s', ex.message);
+      end
+
+    case 3
+      % query a table
+      table = varargin{1};
+      query = varargin{2};
+      info = runQuery(conn, table, query);
+
+    otherwise
+      error('### incorrect inputs');
   end
 
   varargout{1} = info;
 end
 
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-% Get table list
-function info = getTableList(conn)
 
+function info = getFieldList(conn, table)
   info = {};
-  
-  % open a connection
   try
-    q = 'show tables';
-    results = conn.query(q);
-    while results.next
-      info = [info {char(results.getString(1))}];
+    rows = utils.mysql.execute(conn, sprintf('DESCRIBE `%s`', table));
+    for kk = 1:size(rows, 1)
+      info = [info rows(kk,1)];
     end
-  catch
-    error('### Failed to get table list.');
+  catch ex
+    error('### failed to get field list. %s', ex.message);
   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
+function info = runQuery(conn, table, query)
   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 = {};
@@ -143,22 +95,10 @@
     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]);
+  q = sprintf('SELECT %s FROM %s WHERE %s', f(1:end-1), table, query);
   try
-    info = simpleQuery(conn, q);
-  catch
-    error('### Failed to query table.');
+    info = utils.mysql.execute(conn, q);
+  catch ex
+    error('### failed to query table. %s', ex.message);
   end
 end
-
-function val = convertValue(val)
-  
-  switch class(val)
-    case 'java.sql.Timestamp'
-      val = char(val);
-    otherwise
-  end
-end
-
-

--- a/m-toolbox/classes/+utils/@jmysql/getsinfo.m	Mon Dec 05 16:20:06 2011 +0100
+++ b/m-toolbox/classes/+utils/@jmysql/getsinfo.m	Mon Dec 05 16:20:06 2011 +0100
@@ -1,79 +1,56 @@
-% GETSINFO This function returns an sinfo object containing many useful information about an object retrieved from the repository
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+function sinfo = getsinfo(conn, varargin)
+% GETSINFO  Retrieved objects submission info from the repository.
+%
+% CALL:
 %
-% usage:   sinfo = getsinfo(id, conn)
+%   sinfo = getsinfo(conn, id, id)
 %
-% inputs:  id    = obj_id from objmeta table
-%          conn  = utils.mysql.connect(server, dbname);
+% INPUTS:
+%
+%      id - object ID
+%    conn - repository connection implementing java.sql.Connection
 %
-% outputs: sinfo cell array object containing info about object having the
-%          given obj_id on the repository.
-%          sinfo contains the following fields:
-%          - name
-%          - experiment_title
-%          - experiment_desc
-%          - analysis_desc
-%          - quantity
-%          - additional_authors
-%          - additional_comments
-%          - keywords and reference_ids
+% OUTPUTS:
+%
+%   sinfo - array of sinfo structures containing fields
 %
-%          A Monsky 05-02-2009
-%
-% version: $Id: getsinfo.m,v 1.2 2011/03/29 13:40:16 hewitson Exp $
+%    - name
+%    - experiment_title
+%    - experiment_desc
+%    - analysis_desc
+%    - quantity
+%    - additional_authors
+%    - additional_comments
+%    - keywords
+%    - reference_ids
 %
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-function [varargout] = getsinfo(varargin)
-  
-  import utils.const.*
-  utils.helper.msg(msg.PROC3, 'running %s/%s', mfilename('class'), mfilename);
-  
-  % Collect input variable names
-  in_names = cell(size(varargin));
-  for ii = 1:nargin,in_names{ii} = inputname(ii);end
-  
-  % Collect all ids and plists and connections
-  pl = utils.helper.collect_objects(varargin(:), 'plist');
-  id = utils.helper.collect_objects(varargin(:), 'double');
-  conn  = utils.helper.collect_objects(varargin(:), 'mpipeline.repository.RepositoryConnection');
-  
-  if isempty(conn)
-    error('### This method needs a java connection (mpipeline.repository.RepositoryConnection).');
-  end
-  
-  if isempty(id)
-    error('### This method needs at least one object id.');
+% VERSION: $Id: getsinfo.m,v 1.2 2011/03/29 13:40:16 hewitson Exp $
+
+  if ~isa(conn, 'java.sql.Connection')
+    error('### first argument should be a java.sql.Connection object');
   end
-  
-  if nargout == 0
-    error('### lscov can not be used as a modifier method. Please give at least one output');
-  end
-  
-  % combine plists
-  pl = combine(pl, plist());
-  
-  % Algorithm
-  % Get complete experiment information for each input id
+
   sinfo = [];
-  for ii=1:length(id)
-    qall = ['select name,experiment_title,experiment_desc,analysis_desc,quantity, '...
-      'additional_authors,additional_comments,keywords,reference_ids FROM objmeta ' ...
-      sprintf('where objmeta.obj_id=%d',id(ii))];
-    
-    infoall = utils.jmysql.dbquery(conn, qall);
+  for kk = 1:length(varargin)
+
+    q = ['SELECT name, experiment_title, experiment_desc, analysis_desc, ' ...
+         'quantity, additional_authors, additional_comments, keywords, ' ...
+         'reference_ids FROM objmeta WHERE obj_id = ?'];
+
+    info = utils.mysql.execute(conn, q, varargin{kk});
+
     s.conn                    = conn;
-    s.name                    = infoall{1};
-    s.experiment_title        = infoall{2};
-    s.experiment_description  = infoall{3};
-    s.analysis_description    = infoall{4};
-    s.quantity                = infoall{5};
-    s.additional_authors      = infoall{6};
-    s.additional_comments     = infoall{7};
-    s.keywords                = infoall{8};
-    s.reference_ids           = infoall{9};
+    s.name                    = info{1};
+    s.experiment_title        = info{2};
+    s.experiment_description  = info{3};
+    s.analysis_description    = info{4};
+    s.quantity                = info{5};
+    s.additional_authors      = info{6};
+    s.additional_comments     = info{7};
+    s.keywords                = info{8};
+    s.reference_ids           = info{9};
+
     sinfo = [sinfo s];
   end
-  
-  % Set output
-  varargout{1} = sinfo;
+
 end

--- a/m-toolbox/classes/+utils/@jmysql/query.m	Mon Dec 05 16:20:06 2011 +0100
+++ b/m-toolbox/classes/+utils/@jmysql/query.m	Mon Dec 05 16:20:06 2011 +0100
@@ -1,70 +1,44 @@
-% QUERY query an LTPDA repository.
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+function varargout = query(conn, query, varargin)
+% QUERY  Query an LTPDA repository.
 %
-% DESCRIPTION: QUERY query an LTPDA repository.
+% DEPRECATED! Use utils.mysql.execute instead!
 %
-% CALL:                     results = query(conn, q)
-%              [results, col_names] = query(conn, q)
+% CALL:                [results] = query(conn, q, varargin)
+%              [results, cnames] = query(conn, q, varargin)
 %
 % INPUTS:
-%              conn - an mpipeline.repository.RepositoryConnection object,
-%                     as is returned by utils.jmysql.connect
-%              q    - a valid MySQL query string
+%
+%      conn  - database connection object implementing java.sql.Connection
+%     query  - a valid SQL query
+%  varargin  - optional query parameters
 %
 % OUTPUTS:
-%                results - a cell-array of the results
-%              col_names - a cell-array of the column names in the query
-%
-% VERSION:     $Id: query.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $
 %
-% HISTORY:     24-05-2007 M Hewitson
-%                 Creation
+%   results  - a cell-array of the results
+%    cnames  - a cell-array of the column names in the query
 %
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-
+% VERSION: $Id: query.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $
+%
 
-%              (*) the expiry time for MySQL logins is a property of the
-%              LTPDA Toolbox and can be set in the LTPDA Preferences.
-
-
-function varargout = query(varargin)
+  warning('!!! deprecated. use utils.mysql.execute instead');
   
-  prefs = getappdata(0, 'LTPDApreferences');
-  
-  if ~isa(varargin{1}, 'mpipeline.repository.RepositoryConnection')
-    error('### The first argument should be a RepositoryConnection');
+  if ~isa(conn, 'java.sql.Connection')
+    error('### first argument should be a java.sql.Connection object');
   end
   
-  if ~ischar(varargin{2})
-    error('### The second argument should be a query string.');
+  stmt = conn.prepareStatement(query);
+  for kk = 1:numel(varargin)
+    stmt.setObject(kk, varargin{kk});
   end
   
-  % Inputs
-  conn = varargin{1};
-  q    = varargin{2};
-  
-  % Outputs
-  results  = {};
-  colnames = {};
+  % execute query
+  resultSet = stmt.executeQuery();
   
-  % Check connection
-  if ~conn.isConnected
-    conn.openConnection
-  end
-  
-  if ~conn.isConnected
-    conn.display;
-    error('### Failed to open connection');
-    return
-  end
-  
-  resultSet = conn.query(q);
-    
-  % Set outputs
+  % set outputs
   if nargout > 0
     varargout{1} = resultSet;
     if nargout == 2
-      % Get column names from the meta data
+      % get column names from the meta data
       rsm = resultSet.getMetaData;
       Nc = rsm.getColumnCount;
       colnames = cell(1,Nc);

--- a/m-toolbox/classes/+utils/@jmysql/resultsToCell.m	Mon Dec 05 16:20:06 2011 +0100
+++ b/m-toolbox/classes/+utils/@jmysql/resultsToCell.m	Mon Dec 05 16:20:06 2011 +0100
@@ -1,58 +1,67 @@
-% RESULTSTOCELL converts a java sql ResultSet to a cell-array
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+function varargout = resultsToCell(resultSet)
+% RESULTSTOCELL  Converts a java.sql.ResultSet to a cell array
 %
-% DESCRIPTION: RESULTSTOCELL converts a java sql ResultSet to a cell-array.
+% DEPRECATED! Use utils.mysql.execute instead!
 %
-% CALL:        [results, colnames] = utils.jmysql.resultsToCell(resultSet);
+% CALL:
+%
+%   [results, colnames] = utils.jmysql.resultsToCell(rs);
 %
 % INPUTS:
-%              resultSet - a result set like that returned from
-%                          utils.jmysql.query
+%
+%   rs       - a java.sql.ResultSet like that returned from utils.jmysql.query
 %
 % OUTPUTS:
-%              results      - a cell array of results
-%              col_names    - a cell array of column names
 %
+%   results  - a cell array of results
+%   colnames - a cell array of column names
 %
-% VERSION:     $Id: resultsToCell.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $
+% VERSION: $Id: resultsToCell.m,v 1.1 2009/07/27 19:46:21 hewitson Exp $
 %
-% HISTORY:     24-05-2007 M Hewitson
-%                 Creation
-%
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+
+  warning('!!! deprecated. use utils.mysql.execute instead');
+  
+  if ~isa(resultSet, 'java.sql.ResultSet')
+    error('### first argument should be a java.sql.ResultSet');
+  end
 
+  % get results into a cell array
+  md = rs.getMetaData();
+  nc = md.getColumnCount();
+  row = 1;
+  while rs.next()
+    for kk = 1:nc
+      % convert to matlab objects
+      rows{row, kk} = java2matlab(rs.getObject(kk));
+    end
+    row = row + 1;
+  end
+      
+  % get column names
+  names = cell(1, nc);
+  for kk = 1:nc
+    names{kk} = char(md.getColumnName(kk));
+  end
+      
+  % assign output
+  switch nargout
+    case 0
+      varargout{1} = rows;
+    case 1
+      varargout{1} = rows;
+    case 2
+      varargout{1} = names;
+      varargout{2} = rows;
+    otherwise
+      error('### too many output arguments');
+  end      
+end
 
 
-function varargout = resultsToCell(varargin)
-  
-  prefs = getappdata(0, 'LTPDApreferences');
-  
-  if ~isa(varargin{1}, 'java.sql.ResultSet')
-    error('### The first argument should be a ResultSet');
+function val = java2matlab(val)
+  % matlab converts all base types. just add a conversion for datetime columns
+  switch class(val)
+    case 'java.sql.Timestamp'
+      val = time(plist('time', char(val), 'timezone', 'UTC'));
   end
-  
-  % Inputs
-  resultSet  = varargin{1};
-  
-  os = mpipeline.repository.RepositoryConnection.resultSetToObjectArray(resultSet);
-  disp(sprintf('*** converted result set: [%dx%d]', numel(os), numel(os(1))));
-  
-  % Set outputs
-  if nargout > 0
-    varargout{1} = cell(os);
-    if nargout == 2
-      % Get column names from the meta data
-      rsm = resultSet.getMetaData;
-      Nc = rsm.getColumnCount;
-      colnames = cell(1,Nc);
-      for kk=1:Nc
-        colnames{kk} = char(rsm.getColumnName(kk));
-      end
-      
-      varargout{2} = colnames;
-    end
-  end
-  
-  
 end
-