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