Home > m > mysql > ltpda_dbquery.m

ltpda_dbquery

PURPOSE ^

LTPDA_DBQUERY query an AO repository database.

SYNOPSIS ^

function varargout = ltpda_dbquery(varargin)

DESCRIPTION ^

 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 $

CROSS-REFERENCE INFORMATION ^

This function calls: This function is called by:

SUBFUNCTIONS ^

SOURCE CODE ^

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

Generated on Fri 08-Jun-2007 16:09:11 by m2html © 2003