warndlg SQL statement for iscellstr

Question

Dan Howard 2018-7-26

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412225-warndlg-sql-statement-for-iscellstr

编辑： Guillaume 2018-7-26

Hi,

I am having a few problems on how to work around implementing on using the warndlg function to warn users that either a spelling mistake or an sql statement has been incorrectly input. At the moment I have a variable named SQL that is executed using the actxserver function ( 'ADODB.Connection ) that is dependant on the SQL string that the user selects.

Trying to use the if else decision to use the wardlg box to warn the user if the SQL statement is incorrect else if not proceed with the data extraction without displaying the warndlg box.

if true
  % connection_string = 'Provider=SQLOLEDB;...etc ( Holds database connection information )
    conn = actxserver( 'ADODB.Connection');
    conn.CursorLocation='adUseClient';
      % Open the connection string
      conn.Open(connection-string);
     % store sql string into SQL
     SQL=('select Table.ref from Table');
     % warn users to check the SQL statement
     if ~iscellstr(SQL)
     warndlg('Check SQL Statement')
     else close.warndlg;
     end
  end

Really appreciate some help with this.

Please note: Im using Matlab version 2015b

3 个评论
显示 1更早的评论隐藏 1更早的评论

Guillaume 2018-7-26

Dan Howard's comment posted as an answer moved here:

Ahhh..thank you Guillaume.

I had it in my mind that if after the warning would pop up when the user clicks on ok, the user would then go back and check the SQL statement is correct, change what needs to be done then re-run the script.

Did not realise that the brackets wouldn't do anything..the information array is set to extract all the data by running a for loop. The data is stored in an array and output into a table.

Is there a function or a way to get the warning function to error out?

Many thanks again for replying :-)

Guillaume 2018-7-26

在 MATLAB Online 中打开

Dan Howard's comment posted as an answer moved here:

By adding in a try catch error i.e

if true
  % try
SQL='table.ref from table'
if ~iscellstr(SQL)
warndlg('Check SQL Statement')
end
catch
end

Although running this still displays the error warning message regardless if the slq statement is correct or not.

Think I may be over thinking this process!

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2018-7-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412225-warndlg-sql-statement-for-iscellstr#answer_330433

在 MATLAB Online 中打开

In your if... else statement, if the if branch is not taken, then no warning dialog is created. Thus it's puzzling why you'd try to close it in the else branch.

Your statement should simply be:

if ~iscellstr(SQL)
   warndlg('Check SQL Statement')
end

But see my comment to your question.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Dan Howard 2018-7-26

编辑：Guillaume 2018-7-26

在 MATLAB Online 中打开

Thanks again for your help.

This works just fine, although when testing out the SQL statement to show an error this displays the message and without any SQL errors it still displays the same error.

   % %%Connection to MS SQL SERVER DATABASE using the Microsoft ActiveX Data Open DataBase ADODB Control%%
% Create connection to Database
connection_string = 'Provider =SQLOLEDB;Integrated Security=SSPI;Persist Security Info = False;Initial Catalog=TESTDB;Data Source=XSERVER;';
conn = actxserver('ADODB.Connection');
conn.CursorLocation='adUseClient';
% Connection timeoout 
conn.CommandTimeout=230;
%  myrecordset = actxserver('ADODB.RecordSet');
%Open the connection string- connect to Database
conn.Open(connection_string);
SQL=('select Table.ref from Table');  
if ~iscellstr(SQL)
    error('Check SQL Statement');
end
myrecordset=conn.Execute(SQL); % execute sql statement
iSet=1;                        % initialize space for output
Table{iSet}=[];
Struct{iSet}=[];
while (~isempty(myrecordset))  %loop through all myrecordsets
if(myrecordset.State && myrecordset.RecordCount >0)
   table=myrecordset.GetRows';% retrieve data from recordset and transpose myrecordset
   result=[];
   Columns=[];               % Create space for Column array 
   Fields=myrecordset.Fields;   % retrieve all Fields with column names
   for col=1:Fields.Count     % loop through all columns
      ColumnName =Fields.Item(col-1).Name; % get column name
      name=genvarname(lower(ColumnName)); % convert to a valid Matlab field name
      if(isempty(ColumnName)),            
          name=char('A'-1+col);           % if column without name then use A, B, C...as column names
      end             
      Columns=[Columns ' '];              % seperate column char array with a space               
      Columns=[Columns, ColumnName];
      if(size(table,1)==1)       % is table a vector
          Res=table{col};        % 
          if(numel(Res==0)||strcmpi(Res,'N/A')|| isnan(all(Res))),Res=[];
          end
      else                       % is table a matrix?
          Res=table(:,col);
      end
      result.(name)=Res;
   end
   Struct{iSet}=result;
   Table{iSet}=table;
   iSet=iSet+1;
   TableResults = cell2table(table);             %change 'table' cell array to table
   [Columns]=strsplit(Columns,{' '}); % splits text headers into cell array  
   Columns(: ,2:end);                 % change variable Columns to remove the first Column i.e Match Matrix Column results to Table Matrix
   TableResults.Properties.VariableNames = (ans); % add table headers into table Class
   % Export table results to Excel
end
try
    myrecordset=myrecordset.NextRecordset();
catch
end 
end
end

That's the entire bit of code that catches the SQL data, loops through and dumps what has been executed into a table, with headers.

I was reading on using the error function that you mentioned and using it as a try catch exception...unsure on how this can be implemented.

Is iscellstr the right function to use?

Guillaume 2018-7-26

在 MATLAB Online 中打开

Dan Howard's comment posted as an answer moved here:

Tried using the try catch expression clause with your if statement..originally I had put this in the wrong place of the code.

    % try
      myrecordset-=conn.Execute(SQL);
      catch exception
      if ~iscellstr(SQL)
           warndlg('Error');
      end

Originally this try catch was placed after the variable SQL was declared.

It seems to work ok

Guillaume 2018-7-26

编辑：Guillaume 2018-7-26

在 MATLAB Online 中打开

Is iscellstr the right function to use?

Depends on what you want to check. If you want your SQL variable to be a cell array of char vectors, then it is the right function, otherwise no. In your case, I'd say no.

Certainly, when you write:

SQL=('select Table.ref from Table');  %Again, () brackets are unnecessary clutter!

You're not creating a cell array of char vector, but simply a char vector. In addition, the Execute method would not accept an array of strings as input (which is what your cell array would be translated to by the COM interface).

Now that I understand what you're doing, I would just wrap the Execute call in a try catch as you've done and if it fails not do anything more than just report the eror. No need to check what type SQL is. Execute failed either because the sql statement is incorrect, the variable is the wrong type, or there's a problem with the connection. If there was a problem with the connection, most likely the code would have failed earlier, so I would just tell the user to check the sql. Possibly, give them the original cause of the failure, so:

try 
   connection.Execute(SQL);
catch exception
   error('Failed to execute SQL. Check it. Failure was: %s', exception.Message);  %this will stop the code
end
%if we reach here, the execute was succesful
%... rest of the code

By the way, you seem to be fond of unnecessary brackets. All these lines have unnecessary brackets:

SQL=('select Table.ref from Table');  
if(myrecordset.State && myrecordset.RecordCount >0)
if(isempty(ColumnName)),           %and unnecessary comma
if(size(table,1)==1)      
if(numel(Res==0)||strcmpi(Res,'N/A')|| isnan(all(Res))),Res=[];
TableResults.Properties.VariableNames = (ans);

More disturbing in that last line is the use of ans.

TableResults.Properties.VariableNames = Columns(: ,2:end);

Finally, note that naming your variables Struct and Table is asking for trouble. The name is too close to the matlab functions struct and table. For that last one, you also have a variable called table which would prevent you from using the table function.

请先登录，再进行评论。

Answer 2

Dan Howard 2018-7-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412225-warndlg-sql-statement-for-iscellstr#answer_330492

在 MATLAB Online 中打开

Tried using the try catch expression clause with your if statement..originally I had put this in the wrong place of the code.

if true
  % try
    myrecordset-=conn.Execute(SQL);
    catch exception
    if ~iscellstr(SQL)
         warndlg('Error');
    end
end

Originally this try catch was placed after the variable SQL was declared.

It seems to work ok

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

warndlg SQL statement for iscellstr

3 个评论
显示 1更早的评论隐藏 1更早的评论

回答（2 个）

6 个评论
显示 4更早的评论隐藏 4更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

另请参阅

类别

标签

Community Treasure Hunt

warndlg SQL statement for iscellstr

3 个评论 显示 1更早的评论隐藏 1更早的评论

回答（2 个）

6 个评论 显示 4更早的评论隐藏 4更早的评论

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

另请参阅

类别

标签

Community Treasure Hunt

3 个评论
显示 1更早的评论隐藏 1更早的评论

6 个评论
显示 4更早的评论隐藏 4更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论