LTPDA_DBQUERY query an AO repository database. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% DESCRIPTION: LTPDA_DBQUERY query an AO repository database. CALL: info = ltpda_dbquery(conn); info = ltpda_dbquery(conn, query); info = ltpda_dbquery(conn, table, query); INPUTS: conn - a database connection object such as that returned by mysql_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 = ltpda_dbquery(conn, 'select * from objmeta where id>1000 and id<2000'); >> info = ltpda_dbquery(conn, 'ao', 'id>1000 and id<2000'); >> info = ltpda_dbquery(conn, 'objmeta', 'name like "x12"'); >> info = ltpda_dbquery(conn, 'users', 'username="aouser"'); >> info = ltpda_dbquery(conn, 'collections', 'id=3'); >> info = ltpda_dbquery(conn, 'collections', 'obj_ids="1,2"'); >> info = ltpda_dbquery(conn, 'transactions', 'user_id=3'); >> info = ltpda_dbquery(conn, 'transactions', 'obj_id=56'); >> info = ltpda_dbquery(conn) The 'info' structure will contain a list of the tables in the database. >> info = ltpda_dbquery(conn, query) The 'info' structure will contain a list of records resulting from the SQL query. VERSION: $Id: ltpda_dbquery.m,v 1.5 2008/02/24 13:12:46 hewitson Exp $ HISTORY: 10-05-2007 M Hewitson Creation %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
0001 function varargout = ltpda_dbquery(varargin) 0002 % LTPDA_DBQUERY query an AO repository database. 0003 % 0004 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 0005 % 0006 % DESCRIPTION: LTPDA_DBQUERY query an AO repository database. 0007 % 0008 % CALL: info = ltpda_dbquery(conn); 0009 % info = ltpda_dbquery(conn, query); 0010 % info = ltpda_dbquery(conn, table, query); 0011 % 0012 % INPUTS: 0013 % conn - a database connection object such as that returned by 0014 % mysql_connect(). 0015 % query - a valid MySQL query string 0016 % table - a table name 0017 % 0018 % OUTPUTS: 0019 % info - the returned 'info' structure contains the fields from 0020 % each matching record. 0021 % 0022 % EXAMPLES: 0023 % 0024 % >> info = ltpda_dbquery(conn, 'select * from objmeta where id>1000 and id<2000'); 0025 % >> info = ltpda_dbquery(conn, 'ao', 'id>1000 and id<2000'); 0026 % >> info = ltpda_dbquery(conn, 'objmeta', 'name like "x12"'); 0027 % >> info = ltpda_dbquery(conn, 'users', 'username="aouser"'); 0028 % >> info = ltpda_dbquery(conn, 'collections', 'id=3'); 0029 % >> info = ltpda_dbquery(conn, 'collections', 'obj_ids="1,2"'); 0030 % >> info = ltpda_dbquery(conn, 'transactions', 'user_id=3'); 0031 % >> info = ltpda_dbquery(conn, 'transactions', 'obj_id=56'); 0032 % 0033 % >> info = ltpda_dbquery(conn) 0034 % 0035 % The 'info' structure will contain a list of the tables in the database. 0036 % 0037 % >> info = ltpda_dbquery(conn, query) 0038 % 0039 % The 'info' structure will contain a list of records resulting from the SQL query. 0040 % 0041 % VERSION: $Id: ltpda_dbquery.m,v 1.5 2008/02/24 13:12:46 hewitson Exp $ 0042 % 0043 % HISTORY: 10-05-2007 M Hewitson 0044 % Creation 0045 % 0046 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 0047 0048 ALGONAME = mfilename; 0049 VERSION = '$Id: ltpda_dbquery.m,v 1.5 2008/02/24 13:12:46 hewitson Exp $'; 0050 CATEGORY = 'Internal'; 0051 0052 % Check if this is a call for parameters 0053 if nargin == 1 0054 if ischar(varargin{1}) 0055 in = char(varargin{1}); 0056 if strcmp(in, 'Params') 0057 varargout{1} = plist; 0058 return 0059 elseif strcmp(in, 'Version') 0060 varargout{1} = VERSION; 0061 return 0062 elseif strcmp(in, 'Category') 0063 varargout{1} = CATEGORY; 0064 return 0065 end 0066 end 0067 end 0068 0069 conn = varargin{1}; 0070 if ~isa(conn, 'database') 0071 error('### First input must be a database connection object.'); 0072 end 0073 0074 % get table list 0075 if nargin == 1 0076 info = getTableList(conn); 0077 % execute query 0078 elseif nargin == 2 0079 info = simpleQuery(conn, varargin{2}); 0080 % query a table 0081 elseif nargin == 3 0082 0083 table = varargin{2}; 0084 query = varargin{3}; 0085 info = runQuery(conn, table, query); 0086 0087 else 0088 error('### Incorrect inputs.'); 0089 end 0090 0091 varargout{1} = info; 0092 0093 %-------------------------------------------------------------------------- 0094 % Get table list 0095 function info = getTableList(conn) 0096 0097 % open a connection 0098 try 0099 curs = exec(conn, 'show tables'); 0100 curs = fetch(curs); 0101 info = curs.Data; 0102 close(curs); 0103 catch 0104 error('### Failed to get table list. Server returned: %s', curs.Message); 0105 end 0106 0107 %-------------------------------------------------------------------------- 0108 % Get field list 0109 function info = getFieldList(conn, table) 0110 0111 try 0112 curs = exec(conn, sprintf('describe %s', table)); 0113 curs = fetch(curs); 0114 info = curs.Data; 0115 close(curs); 0116 catch 0117 error('### Failed to get field list. Server returned: %s', curs.Message); 0118 end 0119 0120 %-------------------------------------------------------------------------- 0121 % Get field list 0122 function info = simpleQuery(conn, q) 0123 0124 % open a connection 0125 0126 try 0127 curs = exec(conn, sprintf('%s', q)); 0128 curs = fetch(curs); 0129 info = curs.Data; 0130 close(curs); 0131 catch 0132 error('### Failed to execute query. Server returned: %s', curs.Message); 0133 end 0134 0135 0136 0137 %-------------------------------------------------------------------------- 0138 % Run a query 0139 function info = runQuery(conn, table, query) 0140 0141 % Run query 0142 info = []; 0143 0144 fieldlist = getFieldList(conn, table); 0145 fields = fieldlist(:,1); 0146 f = ''; 0147 fs = {}; 0148 for j=1:length(fields) 0149 % special cases 0150 f=[f fields{j} ',' ]; 0151 fs = [fs fields(j)]; 0152 end 0153 q = sprintf('select %s from %s where %s', f(1:end-1), table, query); 0154 disp(['** QUERY: ' q]); 0155 try 0156 curs = exec(conn, q); 0157 curs = fetch(curs); 0158 info = curs.Data; 0159 close(curs); 0160 catch 0161 error('### Failed to query table. Server returned: %s', curs.Message); 0162 end 0163 0164 0165 % END