Mercurial > hg > ltpda

comparison m-toolbox/classes/+utils/@mysql/execute.m @ 6:2b57573b11c7 database-connection-manager

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