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.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 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.html,v 1.14 2008/03/31 10:27:42 hewitson Exp $

 HISTORY:     10-05-2007 M Hewitson
                 Creation

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CROSS-REFERENCE INFORMATION ^

This function calls: This function is called by:

SUBFUNCTIONS ^

SOURCE CODE ^

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.html,v 1.14 2008/03/31 10:27:42 hewitson Exp $
0042 %
0043 % HISTORY:     10-05-2007 M Hewitson
0044 %                 Creation
0045 %
0046 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
0047 
0048 ALGONAME = mfilename;
0049 VERSION  = '$Id: ltpda_dbquery.html,v 1.14 2008/03/31 10:27:42 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

Generated on Mon 31-Mar-2008 12:20:24 by m2html © 2003