classdef SQLstatement_dev < handle
properties
conn;
tbls = {};
result = {};
issuedStatementError = false;
issuedAvailableError = false;
devVer;
end
properties (Dependent = true)
DBname;
statement;
availableTables;
availableColumns;
end
methods
function SQLobject = SQLstatement_dev(DBname, un, pwd, devVerOverride)
if nargin < 4
SQLobject.devVer = is_devVer(SQLobject);
else
SQLobject.devVer = devVerOverride;
end
if ~SQLobject.devVer
SQLobject.conn = database(DBname, un, pwd);
if ~isempty(SQLobject.conn.Message)
disp(SQLobject.conn.Message)
else
disp(['Connection to database ' SQLobject.DBname ' opened successfully!'])
end
end
end
function delete(obj)
try
close(obj.conn);
catch me
end
end
function name = get.DBname(obj)
if ~obj.devVer
name = obj.conn.Instance;
else
name = 'Development Version: database connection not established on purpose.';
end
end
function S = get.statement(obj)
try
S = obj.assembleStatement;
catch me
if obj.issuedStatementError
S = getReport(me);
obj.issuedStatementError = false;
else
disp('Sorry, attempt to assemble statement failed!')
disp('More info is in this object''s "statement" field.')
obj.issuedStatementError = true;
end
end
end
function aT = get.availableTables(obj)
try
if ~obj.devVer
tmp = fetch(exec(obj.conn, 'select Name from sys.Tables'));
aT = tmp.Data;
else
aT = {};
end
catch me
aT = getReport(me);
if obj.issuedAvailableError
obj.issuedAvailableError = false;
else
disp('Sorry, attempt to get available tables failed!')
disp('More info is in this object''s "availableTables" field.')
obj.issuedAvailableError = true;
end
end
end
function aC = get.availableColumns(obj)
aC = struct.empty;
if ~isempty(obj.tbls)
cat = get(obj.conn, 'Catalog');
sch = schemas(obj.conn, cat);
for t = obj.tbls
for s = sch
if ismember(t, tables(obj.conn, cat, s))
aC.(t) = columns(obj.conn, cat, s, t);
break;
end
end
end
end
end
function addPropVals(obj, prop, newElements)
if ismember(prop, fieldnames(obj))
obj.(prop) = extendArray(obj.(prop), newElements);
end
end
end
methods (Abstract)
S = assembleStatement(obj);
submitStatement(obj);
setPropVals(obj, prop, elements, extend);
getPropVals(obj, prop);
end
end
classdef query_dev < SQLstatement_dev
properties
columns = {};
orderBy = {};
where = '';
end
methods
function qObject = query_dev(DBname, un, pword, parameters)
if nargin < 4
parameters = struct.empty;
end
if nargin < 3
pword = '';
end
if nargin < 2
un = '';
end
if nargin < 1
DBname = '';
end
qObject = qObject@SQLstatement_dev(DBname, un, pword);
paraFields = fieldnames(parameters);
for prop={'columns', 'tables', 'orderBy', 'where'}
prop = prop{:};
if ismember(prop, paraFields)
qObject.(prop) = extendArray(qObject.(prop), parameters.(prop));
end
end
end
function errCode = setPropVals(obj, prop, elements, extend)
if nargin < 4
extend = false;
end
errCode = '';
if ~(isequal(prop, 'where') || iscell(elements))
elements = {elements};
end
switch prop
case 'where'
if ~ischar(elements)
errCode = 'query.setPropVals error: wrong type for where clause';
end
case 'tables'
if numel(elements)~=1
errCode = 'query.setPropVals error: received more than one value for tables';
elseif numel(elements)==1 && ~extend
errCode = ['query.setPropVals error: asked to make tables contain more than '...
'one element; this is not allowed in a "non-join" query'];
elseif ~ischar(elements{1})
errCode = 'query.setPropVals error: wrong type for tables';
end
case 'columns'
for i=1:length(elements)
if ~ischar(elements{i})
errCode = ['query.setPropVals error: at least one value for columns is '...
'the wrong type'];
end
end
otherwise
end
if ismember(prop, fieldnames(obj))
obj.(prop) = elements;
end
end
end
end
