How to use a SQL where statement in delphi properly?

Question

I have a problem with issuing a SQL statement. I know that the English value should be a string on its own and I've tried that but it keeps throwing me one of these errors

 procedure TfrmPetersonGroup.btnEnglishClick(Sender: TObject);
    var
    sSqlQuery:string;
    begin
    //2.4
    dmoBandB.qryQuery.SQL.Clear;
    sSqlQuery:='DELETE FROM tblClients WHERE Nationality =' + ' English';
    dmoBandB.qryQuery.SQL.Text := sSqlQuery;
    dmoBandB.qryQuery.active := true;
    end;

Answer 1

I suggest you get a safe query. As below:

procedure SafeDeleteReq(SQLQuery: TSQLQuery; del: string);
begin
  SQLQuery.SQL.Text := 'DELETE FROM tblClients WHERE Nationality=:Nationality';
  SQLQuery.ParamByName('Nationality').AsString := del;
  SQLQuery.ExecSQL();
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  SafeDeleteReq(SQLQuery1, 'English');
end;

Answer 2

You could change your sSql to

sSqlQuery:='DELETE FROM tblClients WHERE Nationality = ' + QuotedStr('English');

just to get it working, but that's not the best idea, see below.

Your version of it caused the error because, without quotes around it, the Sql parser thought that English was an identifier, e.g. another column name like Nationality.

Using QuotedStr around column values ensures that single-quote characters embedded in the value, like

O'Brien

are escaped correctly.

The other thing is that you should replace

  dmoBandB.qryQuery.active := true;

by

  dmoBandB.qryQuery.ExecSql;

The reason is that setting Active to True is equivalent to calling .Open, which is invalid in this context because .Open only works if the Sql query returns a result set and DELETE does not (sorry, I should have noticed this problem first time around). Once you've called ExecSql, you can reopen the table by setting qryQuery's Sql.Text to a valid SELECT statement and then calling .Open.

However, a betteer way to avoid your initial problem would be to get into the habit of using parameterised Sql statements - see http://docwiki.embarcadero.com/RADStudio/Rio/en/Using_Parameters_in_Queries, which is applicable to all Sql DML statements (Insert, Delete, Update, Select, etc). Apart from anything else, this may help you avoid Sql Injection exploits (https://en.wikipedia.org/wiki/SQL_injection).