warndlg SQL statement for iscellstr

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

[Guillaume]

What is supposed to happen after the warning is displayed. It's all very well to warn the user, but once they've clicked OK, at the moment, your script will continue on its merry way with the invalid input. Would it not be better to just error out, so that they can correct the problem?

Note: it's very odd to see round brackets in

SQL=('select Table.ref from Table');

The brackets do nothing at all. The line is identical to:

SQL='select Table.ref from Table';

Perhaps, you meant to write:

SQL={'select Table.ref from Table'};

which creates a cell array.

[Guillaume]

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]

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]

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.

[Dan Howard]

I was looking to see how this can be closed using the else statement but i guess its not a simple..

if true
  % if ~iscellstr(SQL)
 warndlg('Check SQL Statement')
else close.warndlg
end
end

[Dan Howard]

Apologies, I mis-read your response...quite lost on where to go with this one.

So..if i use..

if true
  % if ~iscellstr(SQL)   %
warndlg('Check SQL Statemnet')
end

The way i understand this is..

If the string inside the variable (SQL) is not correct display the warning message.

[Guillaume]

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

The simplest thing is to use error. It won't put a dialog, instead simply write a red error message at the command line, the same way any other matlab function errors when inputs are invalid. It will also terminate the script.

if ~iscellstr(SQL)
   error('Check SQL statement'); %ends execution
end

[Dan Howard]

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]

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]

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]

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