line source
+ − % EXECUTEQUERY query a LTPDA repository database.
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − %
+ − % DESCRIPTION: EXECUTEQUERY query a LTPDA repository database.
+ − %
+ − % CALL: info = executeQuery(pl);
+ − % info = executeQuery(query);
+ − % info = executeQuery(table, query);
+ − % info = executeQuery(query, hostname, database, username);
+ − % info = executeQuery(table, query, hostname, database, username);
+ − %
+ − % INPUTS: pl - a PLIST object.
+ − % query - a valid MySQL query string
+ − % table - a table name
+ − % hostname - hostname of the LTPDA database
+ − % database - LTPDA database
+ − % username - user name
+ − %
+ − % OUTPUTS: info - the returned 'info' structure contains the fields
+ − % from each matching record.
+ − %
+ − % REMARK: If you don't specify a hostname, database and user name then
+ − % do this method the following:
+ − % The repository manager have the following numer of connections:
+ − % 0: A GUI will open for creating a new connection.
+ − % 1: The method will use this connection.
+ − % >1: A GUI will open where you have to select a connection
+ − %
+ − % EXAMPLES:
+ − %
+ − % >> info = LTPDARepositoryManager.executeQuery('select * from objmeta where id>1000 and id<2000');
+ − % >> info = LTPDARepositoryManager.executeQuery('ao', 'id>1000 and id<2000');
+ − % >> info = LTPDARepositoryManager.executeQuery('objmeta', 'name like "x12"');
+ − % >> info = LTPDARepositoryManager.executeQuery('users', 'username="aouser"');
+ − % >> info = LTPDARepositoryManager.executeQuery('collections', 'id=3');
+ − % >> info = LTPDARepositoryManager.executeQuery('collections', 'obj_id="1,2"');
+ − % >> info = LTPDARepositoryManager.executeQuery('transactions', 'user_id=3');
+ − % >> info = LTPDARepositoryManager.executeQuery('transactions', 'obj_id=56');
+ − %
+ − % The 'info' cell-array will contain the results from the SQL query.
+ − %
+ − % <a href="matlab:web(LTPDARepositoryManager.getInfo('LTPDARepositoryManager.executeQuery').tohtml, '-helpbrowser')">Parameters Description</a>
+ − %
+ − % VERSION: $Id: executeQuery.m,v 1.4 2011/04/08 08:56:35 hewitson Exp $
+ − %
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ −
+ − function varargout = executeQuery(varargin)
+ −
+ − % Check if this is a call for parameters
+ − if utils.helper.isinfocall(varargin{:})
+ − varargout{1} = getInfo(varargin{3});
+ − return
+ − end
+ −
+ − import utils.const.*
+ − utils.helper.msg(msg.PROC3, 'running %s/%s', mfilename('class'), mfilename);
+ −
+ − % Get the repository manager - there should only be one!
+ − rm = LTPDARepositoryManager();
+ −
+ − % Collect all plists
+ − [pl, invars, rest] = utils.helper.collect_objects(varargin(:), 'plist');
+ −
+ − pl = combine(pl, getDefaultPlist);
+ − query = pl.find('query');
+ − table = pl.find('table');
+ − hostname = pl.find('hostname');
+ − database = pl.find('database');
+ − username = pl.find('username');
+ −
+ − % Check through 'rest'
+ − if (numel(rest) == 1) && (ischar(rest{1}))
+ − query = rest{1};
+ − elseif numel(rest) == 2
+ − table = rest{1};
+ − query = rest{2};
+ − elseif numel(rest) == 4
+ − query = rest{1};
+ − hostname = rest{2};
+ − database = rest{3};
+ − username = rest{4};
+ − elseif numel(rest) == 5
+ − table = rest{1};
+ − query = rest{2};
+ − hostname = rest{3};
+ − database = rest{4};
+ − username = rest{5};
+ − end
+ −
+ − % Get connection
+ − conn = rm.findConnections(hostname, database, username);
+ − if numel(conn) == 0
+ − conn = rm.newConnection(hostname, database, username);
+ − elseif numel(conn) > 1
+ − conn = rm.manager.selectConnection([]);
+ − end
+ −
+ − if isempty(conn)
+ − error('### It is necessary to create or select a connection.');
+ − end
+ −
+ − % open connection
+ − conn.openConnection();
+ − if ~conn.isConnected()
+ − error('### Can not open the connection.');
+ − end
+ −
+ − % make sure
+ − try
+ − mustUnlock = ~conn.isLocked();
+ −
+ − % Lock connection
+ − conn.setLocked(true);
+ −
+ − if isempty(table) && ~isempty(query)
+ − % execute query
+ − info = simpleQuery(conn, query);
+ − elseif ~isempty(query) && ~isempty(table)
+ − % query a table
+ − info = runQuery(conn, table, query);
+ − else
+ − error('### Incorrect inputs. Please specify at least a query and/or a table.');
+ − end
+ − catch Exception
+ − if (mustUnlock)
+ − conn.setLocked(false);
+ − end
+ − error(Exception.message);
+ − end
+ −
+ − if (mustUnlock)
+ − conn.setLocked(false);
+ − end
+ −
+ − varargout{1} = info;
+ − end
+ −
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − % Local Functions %
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ −
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − % 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 Exception
+ − disp(Exception.message);
+ − 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 Exception
+ − disp(Exception.message);
+ − 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 Exception
+ − disp(Exception.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 Exception
+ − disp(Exception.message);
+ − error('### Failed to query table.');
+ − end
+ − end
+ −
+ − function val = convertValue(val)
+ −
+ − switch class(val)
+ − case 'java.sql.Timestamp'
+ − val = char(val);
+ − otherwise
+ − end
+ − end
+ −
+ −
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − %
+ − % FUNCTION: getInfo
+ − %
+ − % DESCRIPTION: Returns the method-info object
+ − %
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − function ii = getInfo(varargin)
+ − if nargin == 1 && strcmpi(varargin{1}, 'None')
+ − sets = {};
+ − pl = [];
+ − else
+ − sets = {'Default'};
+ − pl = getDefaultPlist;
+ − end
+ − % Build info object
+ − ii = minfo(mfilename, 'LTPDARepositoryManager', 'ltpda', utils.const.categories.helper, '$Id: executeQuery.m,v 1.4 2011/04/08 08:56:35 hewitson Exp $', sets, pl);
+ − end
+ −
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − %
+ − % FUNCTION: getDefaultPlist
+ − %
+ − % DESCRIPTION: Returns the default PLIST
+ − %
+ − %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+ − function pl = getDefaultPlist()
+ −
+ − % Initialise plist
+ − pl = plist();
+ −
+ − % query
+ − p = param({'query', 'A valid MySQL query string.'}, paramValue.EMPTY_STRING);
+ − pl.append(p);
+ −
+ − % table
+ − p = param({'table', 'A table name.'}, paramValue.EMPTY_STRING);
+ − pl.append(p);
+ −
+ − % hostname
+ − p = param({'hostname', 'The hostname of the repository to connect to.'}, paramValue.EMPTY_STRING);
+ − pl.append(p);
+ −
+ − % database
+ − p = param({'database', 'The database on the repository.'}, paramValue.EMPTY_STRING);
+ − pl.append(p);
+ −
+ − % username
+ − p = param({'username', 'The username to connect with.'}, paramValue.EMPTY_STRING);
+ − pl.append(p);
+ −
+ − end