Mercurial > hg > ltpda

annotate m-toolbox/classes/+utils/@mysql/execute.m @ 51:9d5c88356247 database-connection-manager

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make unit tests database connection parameters configurable
author Daniele Nicolodi <nicolodi@science.unitn.it>
date Wed, 07 Dec 2011 17:24:37 +0100
parents 2b57573b11c7
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
1 function varargout = execute(conn, query, varargin)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
2 % EXECUTE Execute the given QUERY with optional parameters VARARGIN
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
3 % substituted for the '?' placeholders through connection CONN. In the
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
4 % case of data manupulation queries returns the update count. In case
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
5 % of data retrival queries returns a 2D cell array with the query
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
6 % results and optionally a cell array with column names.
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
7 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
8 % CALL:
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
9 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
10 % [rows] = utils.mysql.execute(conn, query, varargin)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
11 % [rows, names] = utils.mysql.execute(conn, query, varargin)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
12 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
13 % PARAMETERS:
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
14 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
15 % conn - an object implementing the java.sql.Connection interface
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
16 % query - SQL query string. ? are substituted with PARAMS values
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
17 % varargin - query parameters
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
18 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
19 % RETURNS:
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
20 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
21 % rows - update count in the case of data manipulation queries
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
22 % or 2D cell array with query resutls in the case of
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
23 % data retrival queries
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
24 % names - names of the columns in the result set
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
25 %
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
26
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
27 % check parameters
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
28 if nargin < 2
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
29 error('### incorrect usage');
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
30 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
31 if ~isa(conn, 'java.sql.Connection')
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
32 error('### invalid connection');
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
33 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
34
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
35 % build query
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
36 stmt = conn.prepareStatement(query);
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
37 for kk = 1:numel(varargin)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
38 stmt.setObject(kk, matlab2sql(varargin{kk}));
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
39 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
40
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
41 % execute query
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
42 rv = stmt.execute();
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
43
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
44 switch rv
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
45 case 0
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
46 % we have an update count
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
47 varargout{1} = stmt.getUpdateCount();
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
48 case 1
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
49 % we have a result set
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
50 rs = stmt.getResultSet();
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
51
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
52 % get results into a cell array
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
53 md = rs.getMetaData();
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
54 nc = md.getColumnCount();
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
55 row = 1;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
56 rows = {};
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
57 while rs.next()
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
58 for kk = 1:nc
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
59 % convert to matlab objects
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
60 rows{row, kk} = java2matlab(rs.getObject(kk));
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
61 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
62 row = row + 1;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
63 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
64
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
65 % get column names
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
66 names = cell(1, nc);
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
67 for kk = 1:nc
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
68 names{kk} = char(md.getColumnName(kk));
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
69 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
70
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
71 % assign output
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
72 switch nargout
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
73 case 0
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
74 varargout{1} = rows;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
75 case 1
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
76 varargout{1} = rows;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
77 case 2
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
78 varargout{1} = names;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
79 varargout{2} = rows;
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
80 otherwise
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
81 error('### too many output arguments');
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
82 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
83
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
84 otherwise
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
85 erorr('### error');
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
86 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
87 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
88
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
89
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
90 function val = matlab2sql(val)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
91 switch class(val)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
92 case 'char'
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
93 % matlab converts length one strings to the wrong java type
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
94 val = java.lang.String(val);
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
95 case 'time'
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
96 % convert time objects to strings
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
97 val = val.format('yyyy-mm-dd HH:MM:SS', 'UTC');
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
98 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
99 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
100
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
101
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
102 function val = java2matlab(val)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
103 % matlab converts all base types. just add a conversion for datetime columns
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
104 switch class(val)
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
105 case 'java.sql.Timestamp'
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
106 val = time(plist('time', char(val), 'timezone', 'UTC'));
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
107 end
2b57573b11c7 Add utils.mysql.execute
Daniele Nicolodi <nicolodi@science.unitn.it>
parents:
diff changeset
108 end