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