mysql

 jmysql.m

 Implementation of mysql() via JDBC

0001 function varargout = jmysql(varargin)
0002 % jmysql.m
0003 %
0004 % Implementation of mysql() via JDBC
0005 %
0006 
0007 % 01Nov2019 Petr Janata
0008 
0009 % Maintain our driver and connection objects
0010 persistent driver connections
0011 
0012 % Get our driver if necessary
0013 if isempty(driver)
0014     driver = javaObjectEDT('com.mysql.jdbc.Driver');
0015 end
0016 
0017 % Initialize our connections array
0018 MAX_CONNECTIONS = 15;
0019 if isempty(connections)
0020     connections = cell(1,MAX_CONNECTIONS);
0021 end
0022 
0023 %
0024 % Our first argument defines our action
0025 %
0026 
0027 if nargin < 1
0028    action = 'status'; 
0029 else
0030    action = varargin{1};
0031 end
0032 
0033 % Check whether our first argument is a connection ID
0034 if ~ischar(action)
0035     % The action is actually a connection ID
0036     conn_id = action;
0037     
0038     % Our second argument is therefore the action
0039     action = varargin{2};
0040     extra_arg_start = 3;
0041 else
0042     % Default connection ID is zero
0043     conn_id = 0;
0044     extra_arg_start = 2;
0045 end
0046 
0047 % Get the index of our connection in the array
0048 conn_idx = conn_id+1;
0049 
0050 % Determine whether the action is actually a query
0051 possible_actions = {'open','close','status','use','query'};
0052 
0053 if ~ismember(action, possible_actions)
0054     query = action;
0055     action = 'query';
0056 end
0057 
0058 % Execute the appropriate method on our class as a function of the action
0059 switch action
0060     case 'open'
0061         % Get the remaining parameters. These have to be sent in a specific
0062         % order
0063         host = varargin{extra_arg_start};
0064         user = varargin{extra_arg_start+1};
0065         password = varargin{extra_arg_start+2};
0066         
0067         % Check whether this connection is already open
0068         conn = connections{conn_idx};
0069         if isempty(conn) || conn.isClosed()
0070             try
0071                 % Set the URL
0072                 url = ['jdbc:mysql://' host '/' ];
0073                 
0074                 % Set the properties
0075                 properties = java.util.Properties();
0076                 properties.setProperty('user', user);
0077                 properties.setProperty('password', password);
0078 
0079                 % Create the connection
0080                 conn = driver.connect(url, properties);
0081                 
0082                 % Cache the connection for later use
0083                 connections{conn_idx} = conn;
0084                 
0085                 % Set the output value to be equal to the connection ID
0086                 varargout{1} = conn_id;
0087                 
0088             catch exception
0089                 throw(MException('jmysql:ConnectionError', char(exception.message)));
0090             end
0091         end
0092         
0093     case 'close'
0094         % Fetch the connection from our connections array
0095         conn = connections{conn_idx};
0096         
0097         % Close the connection
0098         conn.close();
0099         
0100         % Remove it from our connections array
0101         connections{conn_idx} = [];
0102         
0103     case 'status'
0104         conn = connections{conn_idx};
0105         if ~isempty(conn) && ~conn.isClosed()
0106             varargout{1} = 0;
0107         else
0108             varargout{1} = -1;  % This needs to be changed to reflect actual status codes
0109         end
0110         
0111     case {'use', 'query'}
0112         if strcmp(action,'use')
0113             db = varargin{extra_arg_start};
0114             query = ['use ' db];
0115         end
0116        
0117         % Get our connection
0118         conn = connections{conn_idx};
0119         if isempty(conn)
0120             error('Connection not open')
0121         end
0122         
0123         % Prepare to make a query by opening a statement
0124         statement = conn.createStatement();
0125         
0126         % Execute the query
0127         if statement.execute(query)
0128             % Get the result set
0129             resultset = statement.getResultSet();
0130         
0131             % Process the result set
0132             [outdata, varnames] = process_result_set(resultset);
0133         
0134             % Close the result set
0135             resultset.close()
0136         else
0137             outdata = {};
0138             varnames = {};
0139         end
0140         
0141         % Close the statement
0142         statement.close();
0143         
0144         % Assign the output variables
0145         if strcmp(action, 'query')
0146             % In order to be compatible with the C++ version of the mysql
0147             % connector, we only return the data, not the variable names
0148             varargout = outdata;
0149         end
0150 end % switch action
0151 
0152 end
0153 
0154 function [outdata, varnames] = process_result_set(resultset)
0155 % Get the metaData
0156 metadata = resultset.getMetaData();
0157 ncols = metadata.getColumnCount();
0158 
0159 % Count the number of rows
0160 resultset.last(); % scroll to end of data
0161 nrows = resultset.getRow();
0162 
0163 % Initialize outputs
0164 varnames = cell(1,ncols);
0165 datatypes = cell(1,ncols);
0166 outdata = cell(1,ncols);
0167 
0168 % Get field names and data types
0169 for icol = 1:ncols
0170    varnames{icol} = char(metadata.getColumnLabel(icol)); 
0171    datatypes{icol} = char(metadata.getColumnTypeName(icol));
0172 end
0173 
0174 % Now get and convert the data
0175 numeric_types = {'DECIMAL','BIGINT UNSIGNED', 'INT UNSIGNED'};
0176 datetypes = {'DATETIME','DATE','TIME'};
0177 
0178 % Initialize output data
0179 for icol = 1:ncols
0180     switch datatypes{icol}
0181         case numeric_types
0182             outdata{icol} = nan(nrows,1);
0183         otherwise
0184             outdata{icol} = cell(nrows,1);
0185     end
0186 end
0187 
0188 % Scroll back to beginning
0189 if resultset.first()
0190     have_more_data = true;
0191     irow = 0;
0192     
0193     while have_more_data
0194         irow = irow+1;
0195         
0196         % Loop over fields
0197         for icol = 1:ncols
0198             value = resultset.getObject(icol);
0199             
0200             if ~resultset.wasNull()
0201                 % Convert the data based on datatype
0202                 switch datatypes{icol}
0203                     case numeric_types
0204                         outdata{icol}(irow,1) = double(value);
0205                     case datetypes
0206                         outdata{icol}{irow,1} = char(value);
0207                     otherwise
0208                         outdata{icol}{irow,1} = value;
0209 
0210                 end % switch datatypes{icol}
0211             end % ~resultset.wasNull()
0212         end % for icol
0213         
0214         % Scroll to next row
0215         if ~resultset.next()
0216             have_more_data = false;
0217         end
0218     end
0219 end % [outdata, varnames] = process_result_set(resultset)
0220 end
0221