0
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
1 % DBQUERY query an AO repository database.
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
2 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
3 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
4 % DESCRIPTION: DBQUERY query an AO repository database.
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
5 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
6 % CALL: info = dbquery(conn);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
7 % info = dbquery(conn, query);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
8 % info = dbquery(conn, table, query);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
9 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
10 % INPUTS:
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
11 % conn - a database connection object such as that returned by
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
12 % utils.jmysql.connect().
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
13 % query - a valid MySQL query string
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
14 % table - a table name
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
15 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
16 % OUTPUTS:
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
17 % info - the returned 'info' structure contains the fields from
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
18 % each matching record.
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
19 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
20 % EXAMPLES:
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
21 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
22 % >> info = utils.jmysql.dbquery(conn, 'select * from objmeta where id>1000 and id<2000');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
23 % >> info = utils.jmysql.dbquery(conn, 'ao', 'id>1000 and id<2000');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
24 % >> info = utils.jmysql.dbquery(conn, 'objmeta', 'name like "x12"');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
25 % >> info = utils.jmysql.dbquery(conn, 'users', 'username="aouser"');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
26 % >> info = utils.jmysql.dbquery(conn, 'collections', 'id=3');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
27 % >> info = utils.jmysql.dbquery(conn, 'collections', 'obj_ids="1,2"');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
28 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'user_id=3');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
29 % >> info = utils.jmysql.dbquery(conn, 'transactions', 'obj_id=56');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
30 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
31 % >> tables = utils.jmysql.dbquery(conn)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
32 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
33 % The 'tables' cell-array will contain a list of the tables in the database.
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
34 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
35 % >> info = utils.jmysql.dbquery(conn, query)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
36 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
37 % The 'info' cell-array will contain the results from the SQL query.
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
38 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
39 % VERSION: $Id: dbquery.m,v 1.2 2010/01/19 20:55:30 ingo Exp $
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
40 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
41 % HISTORY: 10-05-2007 M Hewitson
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
42 % Creation
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
43 %
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
44 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
45
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
46 function varargout = dbquery(varargin)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
47
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
48 conn = varargin{1};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
49 if ~isa(conn, 'mpipeline.repository.RepositoryConnection')
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
50 error('### First input must be a database connection object.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
51 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
52
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
53 if nargin == 1
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
54 % get table list
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
55 info = getTableList(conn);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
56
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
57 elseif nargin == 2
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
58 % execute query
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
59 info = simpleQuery(conn, varargin{2});
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
60
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
61 elseif nargin == 3
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
62 % query a table
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
63 table = varargin{2};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
64 query = varargin{3};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
65 info = runQuery(conn, table, query);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
66 else
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
67 error('### Incorrect inputs.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
68 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
69
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
70 varargout{1} = info;
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
71 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
72
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
73 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
74 % Get table list
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
75 function info = getTableList(conn)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
76
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
77 info = {};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
78
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
79 % open a connection
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
80 try
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
81 q = 'show tables';
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
82 results = conn.query(q);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
83 while results.next
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
84 info = [info {char(results.getString(1))}];
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
85 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
86 catch
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
87 error('### Failed to get table list.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
88 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
89 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
90
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
91 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
92 % Get field list
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
93 function info = getFieldList(conn, table)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
94
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
95 info = {};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
96 try
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
97 q = ['describe ' table];
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
98 results = conn.query(q);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
99 while results.next
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
100 info = [info {char(results.getObject(1))}];
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
101 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
102 catch
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
103 error('### Failed to get field list.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
104 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
105 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
106
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
107 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
108 % Get field list
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
109 function info = simpleQuery(conn, q)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
110
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
111 % open a connection
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
112 info = {};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
113 try
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
114 results = conn.query(q);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
115 mt = results.getMetaData;
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
116 Ncols = mt.getColumnCount;
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
117 row = 1;
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
118 while results.next
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
119 for kk=1:Ncols
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
120 info{row,kk} = convertValue(results.getObject(kk));
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
121 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
122 row = row + 1;
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
123 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
124
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
125 catch ME
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
126 disp(ME.message)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
127 error('### Failed to execute query.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
128 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
129 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
130
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
131 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
132 % Run a query
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
133 function info = runQuery(conn, table, query)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
134
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
135 % Run query
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
136 info = {};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
137
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
138 fields = getFieldList(conn, table);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
139 f = '';
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
140 fs = {};
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
141 for j=1:length(fields)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
142 % special cases
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
143 f = [f fields{j} ',' ];
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
144 fs = [fs fields(j)];
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
145 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
146 q = sprintf('select %s from %s where %s', f(1:end-1), table, query);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
147 disp(['** QUERY: ' q]);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
148 try
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
149 info = simpleQuery(conn, q);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
150 catch
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
151 error('### Failed to query table.');
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
152 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
153 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
154
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
155 function val = convertValue(val)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
156
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
157 switch class(val)
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
158 case 'java.sql.Timestamp'
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
159 val = char(val);
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
160 otherwise
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
161 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
162 end
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
163
|
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff
changeset
|
164
|