+
−function varargout = execute(conn, query, varargin)
+
−% EXECUTE Execute the given QUERY with optional parameters VARARGIN
+
−% substituted for the '?' placeholders through connection CONN. In the
+
−% case of data manupulation queries returns the update count. In case
+
−% of data retrival queries returns a 2D cell array with the query
+
−% results and optionally a cell array with column names.
+
−%
+
−% CALL:
+
−%
+
−%          [rows] = utils.mysql.execute(conn, query, varargin)
+
−%   [rows, names] = utils.mysql.execute(conn, query, varargin)
+
−%
+
−% PARAMETERS:
+
−%
+
−%      conn -  an object implementing the java.sql.Connection interface
+
−%     query -  SQL query string. ? are substituted with PARAMS values
+
−%  varargin -  query parameters
+
−%
+
−% RETURNS:
+
−%
+
−%      rows -  update count in the case of data manipulation queries
+
−%              or 2D cell array with query resutls in the case of
+
−%              data retrival queries
+
−%     names -  names of the columns in the result set
+
−%
+
−
+
−  % check parameters
+
−  if nargin < 2
+
−    error('### incorrect usage');
+
−  end
+
−  if ~isa(conn, 'java.sql.Connection')
+
−    error('### invalid connection');
+
−  end
+
−
+
−  % build query
+
−  stmt = conn.prepareStatement(query);
+
−  for kk = 1:numel(varargin)
+
−    stmt.setObject(kk, matlab2sql(varargin{kk}));
+
−  end
+
−
+
−  % execute query
+
−  rv = stmt.execute();
+
−
+
−  switch rv
+
−    case 0
+
−      % we have an update count
+
−      varargout{1} = stmt.getUpdateCount();
+
−    case 1
+
−      % we have a result set
+
−      rs = stmt.getResultSet();
+
−
+
−      % get results into a cell array
+
−      md = rs.getMetaData();
+
−      nc = md.getColumnCount();
+
−      row = 1;
+
−      rows = {};
+
−      while rs.next()
+
−        for kk = 1:nc
+
−          % convert to matlab objects
+
−          rows{row, kk} = java2matlab(rs.getObject(kk));
+
−        end
+
−        row = row + 1;
+
−      end
+
−
+
−      % get column names
+
−      names = cell(1, nc);
+
−      for kk = 1:nc
+
−        names{kk} = char(md.getColumnName(kk));
+
−      end
+
−
+
−      % assign output
+
−      switch nargout
+
−        case 0
+
−          varargout{1} = rows;
+
−        case 1
+
−          varargout{1} = rows;
+
−        case 2
+
−          varargout{1} = names;
+
−          varargout{2} = rows;
+
−        otherwise
+
−          error('### too many output arguments');
+
−      end
+
−
+
−    otherwise
+
−      erorr('### error');
+
−  end
+
−end
+
−
+
−
+
−function val = matlab2sql(val)
+
−  switch class(val)
+
−    case 'char'
+
−      % matlab converts length one strings to the wrong java type
+
−      val = java.lang.String(val);
+
−    case 'time'
+
−      % convert time objects to strings
+
−      val = val.format('yyyy-mm-dd HH:MM:SS', 'UTC');
+
−  end
+
−end
+
−
+
−
+
−function val = java2matlab(val)
+
−  % matlab converts all base types. just add a conversion for datetime columns
+
−  switch class(val)
+
−    case 'java.sql.Timestamp'
+
−      val = time(plist('time', char(val), 'timezone', 'UTC'));
+
−  end
+
−end