Computing: Free Pascal Programming

Using parameterized SQL queries.


In my Using MySQL databases with Lazarus/Free Pascal tutorial I showed how TSQLQuery.SQL.Text may be used to define the SQL statement to be executed: Just assign the SQL command as a text string to this property. Assigning the string as such causes two issues:

These issues may be solved by using parameterized queries.

In parameterized queries, constant values are replaced by special parameter variables. You can name these parameters as you like, but you have to prefix them with a colon (:). To set the parameters' values, use the TSQLQuery.Params.ParamByName property.

Here's the code, constructing the SQL statement in my MySQL tutorial (SQL to read all cities for a given country – country that may be entered by its 3-letter code or its name – that have a population of over 1 million:

    Query := 'SELECT country.Name AS CountryName, city.Name AS CityName, District, city.Population AS CityPopulation FROM country, city ';
    Query += 'WHERE Code = CountryCode ';
    if Length(edCountry.Text) = 3 then begin
        // Considering 3 letters as country code
        Query += 'AND CountryCode = "' + edCountry.Text + '" ';
    end
    else begin
        // Considering other entries as country name
        Query += 'AND country.Name = "' + edCountry.Text + '" ';
    end;
    Query += 'AND city.Population > 1000000 ';
    Query += 'ORDER BY city.Population DESC';
    // Query the database
    dbSQLQuery.SQL.Text := Query;

And the same with a parameterized query:

    Query := 'SELECT country.Name AS CountryName, city.Name AS CityName, District, city.Population AS CityPopulation FROM country, city ';
    Query += 'WHERE Code = CountryCode ';
    if Length(edCountry.Text) = 3 then begin
        // Considering 3 letters as country code
        Query += 'AND CountryCode = :CountryCode ';
    end
    else begin
        // Considering other entries as country name
        Query += 'AND country.Name = :CountryName ';
    end;
    Query += 'AND city.Population > :Population ';
    Query += 'ORDER BY city.Population DESC';
    dbSQLQuery.SQL.Text := Query; Count := 0;
    if Length(edCountry.Text) = 3 then
        dbSQLQuery.Params.ParamByName('CountryCode').AsString := edCountry.Text
    else
        dbSQLQuery.Params.ParamByName('CountryName').AsString := edCountry.Text;
    dbSQLQuery.Params.ParamByName('Population').AsInteger := 1000000;

Note that the parameter name, used in the TSQLQuery.Params.ParamByName assignment must be stated in the value of TSQLQuery.SQL.Text. If, in the code above, you omit the "if/else" clause for the parameter assignment and query the database for the USA cities, you'll get the following error message:

Error message when trying to assign a value to a parameter not stated in TSQLQuery.SQL.Text

With UPDATE and DELETE statements, parameterized queries may have another benefit: If you change several records, instead of running a new query for each update or deletion, you can run one single query, only with different parameters. This is lots more efficient, the query being in most cases much faster. With the parameterized TSQLQuery.SQL.Text assigned, the query is prepared, by calling the TSQLQuery.Prepare method, then the actual values are assigned to TSQLQuery.Params.ParamByName and the query is run, by calling the TSQLQuery.ExecSQL method.

Here's the code that I use in my MySQL tutorial to update the population of a given city (identified by its ID or its name):

    procedure MySQLUpdate(Transaction: TSQLTransaction; Query: TSQLQuery; CountryCode, CityID, CityName: string; NewPopulation: Integer; out MySQLError: string);
    var
        Sql: string;
    begin
        Sql := 'UPDATE city ';
        Sql += 'SET Population = ' + IntToStr(NewPopulation) + ' ';
        Sql += 'WHERE CountryCode = "' + CountryCode + '" ';
        if CityID <> '' then
            Sql += 'AND ID = ' + CityID + ' '
        else
            Sql += 'AND city.Name = "' + CityName + '"';
        try
            Query.SQL.Text := Sql;
            Query.ExecSQL;
            Transaction.Commit;
        except
            on E: ESQLDatabaseError do
                MySQLError := E.Message;
        end;
    end;

And the same, using a parameterized query:

    procedure MySQLUpdate(Transaction: TSQLTransaction; Query: TSQLQuery; CountryCode, CityID, CityName: string; NewPopulation: Integer; out MySQLError: string);
    var
        Sql: string;
    begin
        Sql := 'UPDATE city ';
        Sql += 'SET Population = :Population ';
        Sql += 'WHERE CountryCode = :CountryCode ';
        if CityID <> '' then
            Sql += 'AND ID = :CityID '
        else
            Sql += 'AND city.Name = :CityName ';
        try
            Query.SQL.Text := Sql;
            Query.Prepare;
            Query.Params.ParamByName('CountryCode').AsString := CountryCode;
            if CityID <> '' then
                Query.Params.ParamByName('CityID').AsString := CityID
            else
                Query.Params.ParamByName('CityName').AsString := CityName;
            Query.Params.ParamByName('Population').AsInteger := NewPopulation;
            Query.ExecSQL;
            Transaction.Commit;
        except
            on E: ESQLDatabaseError do
                MySQLError := E.Message;
        end;
    end;

In the case of a multiple update (the table values being passed to the procedure as arrays), the code would be something like this:

   ...
    try
        Query.SQL.Text := Sql;
        Query.Prepare;
        for I := 0 to Length(CityIDs) - 1 do begin
          ...
            Query.ExecSQL;
            Transaction.Commit;
        end;
        except
          ...
        end;
    end;


If you find this text helpful, please, support me and this website by signing my guestbook.