LTPDA_DBQUERY query an AO repository database. Usage: info = ltpda_dbquery(table, query); >> info = ltpda_dbquery('aos', 'id>1000 and id<2000'); >> info = ltpda_dbquery('aos', 'name like "x12"'); >> info = ltpda_dbquery('AOusers', 'name="aouser"'); >> info = ltpda_dbquery('collections', 'UserID=3'); >> info = ltpda_dbquery('collections', 'aoids="1 2"'); >> info = ltpda_dbquery('transactions', 'UserID=3'); >> info = ltpda_dbquery('transactions', 'aoid=56'); The returned 'info' structure contains the fields from each mathcing record. >> info = ltpda_dbquery() The 'info' structure will contain a list of the tables in the database. >> info = ltpda_dbquery(query) The 'info' structure will contain a list of records resulting from the SQL query. M Hewitson 10-05-07 $Id: ltpda_dbquery.html,v 1.1 2007/06/08 14:15:10 hewitson Exp $
0001 function varargout = ltpda_dbquery(varargin) 0002 0003 % LTPDA_DBQUERY query an AO repository database. 0004 % 0005 % Usage: info = ltpda_dbquery(table, query); 0006 % 0007 % >> info = ltpda_dbquery('aos', 'id>1000 and id<2000'); 0008 % >> info = ltpda_dbquery('aos', 'name like "x12"'); 0009 % >> info = ltpda_dbquery('AOusers', 'name="aouser"'); 0010 % >> info = ltpda_dbquery('collections', 'UserID=3'); 0011 % >> info = ltpda_dbquery('collections', 'aoids="1 2"'); 0012 % >> info = ltpda_dbquery('transactions', 'UserID=3'); 0013 % >> info = ltpda_dbquery('transactions', 'aoid=56'); 0014 % 0015 % The returned 'info' structure contains the fields from each mathcing 0016 % record. 0017 % 0018 % >> info = ltpda_dbquery() 0019 % 0020 % The 'info' structure will contain a list of the tables in the database. 0021 % 0022 % >> info = ltpda_dbquery(query) 0023 % 0024 % The 'info' structure will contain a list of records resulting from the SQL query. 0025 % 0026 % M Hewitson 10-05-07 0027 % 0028 % $Id: ltpda_dbquery.html,v 1.1 2007/06/08 14:15:10 hewitson Exp $ 0029 % 0030 0031 % get table list 0032 if nargin == 0 0033 0034 info = getTableList(); 0035 0036 % get field list 0037 elseif nargin == 1 0038 0039 % info = getFieldList(varargin{1}); 0040 info = simpleQuery(varargin{1}); 0041 0042 elseif nargin == 2 0043 0044 table = varargin{1}; 0045 query = varargin{2}; 0046 0047 info = runQuery(table, query); 0048 0049 else 0050 error('### Incorrect inputs.'); 0051 end 0052 0053 varargout{1} = info; 0054 0055 %-------------------------------------------------------------------------- 0056 % Get table list 0057 function info = getTableList() 0058 0059 % open a connection 0060 conn = mysql_connect(); 0061 curs = exec(conn, 'show tables'); 0062 curs = fetch(curs); 0063 info = curs.Data; 0064 close(curs); 0065 0066 % Close MySQL connection 0067 close(conn); 0068 0069 %-------------------------------------------------------------------------- 0070 % Get field list 0071 function info = getFieldList(table) 0072 0073 % open a connection 0074 conn = mysql_connect(); 0075 0076 curs = exec(conn, sprintf('describe %s', table)); 0077 curs = fetch(curs); 0078 info = curs.Data; 0079 close(curs); 0080 0081 % Close MySQL connection 0082 close(conn); 0083 0084 %-------------------------------------------------------------------------- 0085 % Get field list 0086 function info = simpleQuery(q) 0087 0088 % open a connection 0089 conn = mysql_connect(); 0090 0091 curs = exec(conn, sprintf('%s', q)); 0092 curs = fetch(curs); 0093 info = curs.Data; 0094 close(curs); 0095 0096 % Close MySQL connection 0097 close(conn); 0098 0099 0100 %-------------------------------------------------------------------------- 0101 % Run a query 0102 function info = runQuery(table, query) 0103 0104 % open a connection 0105 conn = mysql_connect(); 0106 0107 % Run query 0108 info = []; 0109 0110 fieldlist = getFieldList(table); 0111 fields = fieldlist(:,1); 0112 f = ''; 0113 fs = {}; 0114 for j=1:length(fields) 0115 % special cases 0116 switch table 0117 case 'aos' 0118 if ~strcmp(fields{j}, 'AO') 0119 f=[f fields{j} ',' ]; 0120 fs = [fs fields(j)]; 0121 end 0122 otherwise 0123 f=[f fields{j} ',' ]; 0124 fs = [fs fields(j)]; 0125 end 0126 end 0127 q = sprintf('select %s from %s where %s', f(1:end-1), table, query); 0128 disp(['** QUERY: ' q]); 0129 curs = exec(conn, q); 0130 curs = fetch(curs); 0131 info = curs.Data; 0132 close(curs); 0133 0134 % Close MySQL connection 0135 close(conn); 0136 0137 0138 % END