Would appreciate anyone's advice on how to solve the issue here regarding Nested Function Caller Scope. I expected changes made to dataStack within the nested function cbfRefreshTable to be reflected in ALL instances of dataStack throughout the file (since they share scope).
You should be able to save the function below and run it yourself (if you have the database toolbox and a SQL Server table to use). I've included the function ic_fetchSQLData.m too which you'll need to save seperately.
[fHandle,uiHandle] = ic_sqlTableGUIEditor('MySchema.MyTable',dbConnObj);
Note the line with comment "% changes made to dataStack in cbfRefreshTable are not present in this variable". Here dataStack will not reflect changes made to it with the fastinsert (assuming you have made changes to the table using the GUI).
Greatly appreciate anyone's help here.
function [fHandle,uiHandle] = ic_sqlTableGUIEditor(varargin) if nargin >= 1 tableName = varargin{:,1}; connCredit = varargin{:,2}; end %%RETRIEVE CREDIT TABLE DATA
sqlQuery = ['SELECT * FROM ' tableName]; [dataStack,dataHeaders] = ic_fetchSQLData(connCredit,sqlQuery); if size(dataStack(:,1)) ~= size(unique(dataStack(:,1))) error('The leftmost column of SQL table is not unique'); end if ~isempty(dataStack) numericFlag = all(cellfun(@isnumeric,dataStack),1); else numericFlag = false(size(dataHeaders,1)); % this default will cause problems
end [r,c] = size(dataStack); % save backup to current directory as precaution
save(fullfile(cd(),'dataStack.mat'),'dataStack'); save(fullfile(cd(),'dataHeaders.mat'),'dataHeaders'); %%INSTANTIATE FIGURE CONTROLS AND INITIALISE PROPERTIES
buff = 140; % Additional Table Width for each column more than 4
fHandle = figure('Position',[300, 500, 340+(c>=4)*buff*(c-4), 500],'name',tableName,'menubar','none','Resize','off'); columnFormat = repmat({'numeric'},1,c); columnFormat(~numericFlag) = {'char'}; uiHandle = uitable('Parent',fHandle,'Units','normalized','Position',[0.05 0.05 0.9 0.8],... 'Data',dataStack,'ColumnName',dataHeaders,... 'ColumnEditable',true(1,c),'ColumnFormat',columnFormat); searchTextHandle = uicontrol(fHandle,'Style','edit','Position',[30 440 125 20],... 'Callback',{@cbfSearchTable,dataStack},... 'TooltipString','Enter Text to Search - Searching will Purge any Changes not Committed '); insertRowHandle = uicontrol(fHandle,'Style','pushbutton','Position',[30 470 125 20],... 'String','Insert Row','Tag','InsertRow','Callback',@cbfInsertRow,... 'TooltipString','Insert New Row'); deleteRowHandle = uicontrol(fHandle,'Style','pushbutton','Position',[160, 470, 125, 20],... 'String','Delete Row','Tag','DeleteRow','Callback',@cbfDeleteRow,... 'TooltipString','Delete Row from Interface Table'); selectRowHandle = uicontrol(fHandle,'Style','popupmenu','Position',[290, 475, 45, 15],... 'String',1:size(get(uiHandle,'Data'),1),'Tag','SelectRow',... 'TooltipString','Re-Query SQL Table', 'Max', 1, 'Min', 1, 'Value', 1); refreshTableHandle = uicontrol(fHandle,'Style','pushbutton','Position',[160, 440, 80, 20],... 'String','Refresh','Tag','CommitTable','Callback',{@cbfRefreshTable,dataStack},... 'TooltipString','Re-Query SQL Table'); commitChangesHandle = uicontrol(fHandle,'Style','pushbutton','Position',[250, 440, 80, 20],... 'String','Commit','Tag','CommitChanges','Callback',{@cbfCommitChanges,dataStack,dataHeaders,numericFlag,tableName},... 'TooltipString','Commit Changes to SQL Table'); %%CALLBACK NESTED FUNCTIONS
% These functions must access variables in-scope
function cbfInsertRow(hObject,eventdata) dataStackinUIT = get(uiHandle,'Data'); dummyRow = cell(1,c); dummyRow(numericFlag) = {NaN}; dummyRow(~numericFlag) = {'-'}; % dataStackinUIT = [dataStackinUIT;dummyRow];
dataStackinUIT = [dummyRow; dataStackinUIT]; set(uiHandle,'Data',dataStackinUIT); end function cbfDeleteRow(hObject,eventdata) rowNumber = get(selectRowHandle,'Value'); dataStackinUIT = get(uiHandle,'Data'); dataStackinUIT_dummy = dataStackinUIT([1:rowNumber-1,rowNumber+1:end],:); set(uiHandle,'Data',dataStackinUIT_dummy); selectRowHandle = uicontrol(fHandle,'Style','popupmenu','Position',[275 360 50 20],... 'String',1:size(get(uiHandle,'Data'),1),'Tag','CommitChanges',... 'TooltipString','Re-Query SQL Table', 'Max', 1, 'Min', 1, 'Value', 1); end function dataStack = cbfCommitChanges(hObject,eventdata,dataStack,dataHeaders,numericFlag,tableName) dataStackinUIT = get(uiHandle,'Data'); % Insert New Rows to SQL Table
[mask,idx] = ismember(dataStackinUIT(:,1),dataStack(:,1)); additionsDataset = dataStackinUIT(~mask,:); % i.e. in dataStackinUIT but not dataStack
dataStackinUIT = dataStackinUIT(mask,:); if ~isempty(additionsDataset) fastinsert(connCredit,tableName,dataHeaders,additionsDataset); end % Replace Any Edited Rows in SQL Table
[mask,idx] = ismember(dataStackinUIT(:,1),dataStack(:,1)); validationVector = false(size(dataStackinUIT,1),1); % Vector of rows that have changed
for i = 2:size(dataStack,2) if numericFlag(i) % numeric column
validationVector = validationVector | ( (cell2mat(dataStackinUIT(:,i))~=cell2mat(dataStack(idx,i))) & ~isnan(cell2mat(dataStackinUIT(:,i)))); else % text column
validationVector = validationVector | ~strcmp(dataStackinUIT(:,i),dataStack(idx,i)); end end changesDataset = dataStackinUIT(validationVector,:); if ~isempty(changesDataset) keyColumnString = regexprep(strtrim(regexprep(evalc('disp(changesDataset(:,1))'),{' *Col[^\n]*\n','\n'},'')),''' +''',', '); sqlOrder = ['DELETE FROM ',tableName, ' WHERE ', dataHeaders{1,1}, ' IN ']; sqlOrder = [sqlOrder,'(',keyColumnString,')']; exec(connCredit,sqlOrder); fastinsert(connCredit,tableName,dataHeaders,changesDataset); end dataStack = cbfRefreshTable(hObject,eventdata); % changes made to dataStack in cbfRefreshTable are not present in this variable
end function dataStack = cbfRefreshTable(hObject,eventdata) set(fHandle,'Color',[0.0 0.3 0.6]); [dataStack,dataHeaders] = ic_fetchSQLData(connCredit,sqlQuery); % here I re-query dataStack and wish to pass it into caller scope
pause(0.5); [r,c] = size(dataStack); set(uiHandle,'Data',dataStack); set(fHandle,'Color',[0.8 0.8 0.8]); end function cbfSearchTable(hObject,eventdata,dataStack) userText = get(searchTextHandle,'String'); validationVector = strncmpi(dataStack(:,1),num2str(userText),length(num2str(userText))); set(uiHandle,'Data',dataStack(validationVector,:)); end end
*************************************************************************** ************ ANother function to save ********************************* ****************************************************************************
function [dataStack,dataHeaders] = ic_fetchSQLData(conn,sqlQuery)%
% [dataStack,dataHeaders] = ic_fetchSQLData(conn,sqlQuery)
%% Author: Mark Whirdy
% Date: 17/11/2010
% Last Edited: ---
%%%%FUNCTION MAIN
dataHeaders = [];curs = exec(conn, sqlQuery);dataStack = [];toRun = true; rowLimit = 50000;while toRun curs = fetch(curs, rowLimit); dbData = curs.Data; dataStack = [dataStack; dbData]; %#ok
numFetchedRows = size(dbData,1); if numFetchedRows < rowLimit toRun = false; end endif ~strcmpi(dataStack,'No Data') dataHeaders = columnnames(curs, 1)'; else dataStack = [];end
Anyone comfortable with nested callback scope should see the problem pretty quickly. Grateful for your assistance
Best Answer