Delphi - Why am I getting this Access Violation? Is there a limit to ADOQuery parameteres?

Question

I have this code that is returning an Access Violation ('Access violation at address 74417E44 in module 'sqloledb.dll'. Read of address 786E3552') and I can't identify where is the problem. My only guess is that ADOQuery has a limit for the number of parameters we can pass. The code is as follows:

With qryInsert do
  begin
    Active := False;
    Close;
    Sql.Clear;
    Sql.Add('Insert Into MyTable(ColumnOne, ');
    Sql.Add('             ColumnTwo,           ');
    Sql.Add('             ColumnThree,         ');
    Sql.Add('             ColumnFour,           ');
    Sql.Add('             ColumnFive,          ');
    Sql.Add('             ColumnSix,        ');
    Sql.Add('             ColumnSeven,        ');
    Sql.Add('             ColumnEight,     ');
    Sql.Add('             ColumnNine,       ');
    Sql.Add('             ColumnTen,       ');
    Sql.Add('             ColumnEleven,     ');
    Sql.Add('             ColumnTwelve,   ');
    if qrySelect.FieldByName('ColumnTwelve').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnThirteen,   ');
      Sql.Add('           ColumnFourteen,   ');
      Sql.Add('           ColumnFifteen,   ');
    end;
    Sql.Add('             ColumnSixteen,   ');
    if qrySelect.FieldByName('ColumnSixteen').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnSeventeen,         ');
      Sql.Add('           ColumnEighteen,         ');
      Sql.Add('           ColumnNineteen,         ');
    end;
    if qrySelect.FieldByName('ColumnTwenty').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnTwenty,  ');
      Sql.Add('           ColumnTwentyOne,        ');
      Sql.Add('           ColumnTwentyTwo,        ');
      Sql.Add('           ColumnTwentyThree,        ');
    end
    else
      Sql.Add('           ColumnTwenty,  ');
    Sql.Add('             ColumnTwentyFour) ');
    Sql.Add('Values(:ColumnOne, :ColumnTwo, :ColumnThree, :ColumnFour, ');
    Sql.Add('       :ColumnFive, ' + dateDB + ', :ColumnSeven,          ');
    Sql.Add('       :ColumnEight, :ColumnNine, :ColumnTen, ');
    Sql.Add('       :ColumnEleven,                                    ');
    Sql.Add('       :ColumnTwelve,                                    ');
    if qrySelect.FieldByName('ColumnTwelve').AsSTring = 'Y' then
      Sql.Add('     :ColumnThirteen, :ColumnFourteen, :ColumnFifteen,              ');
    Sql.Add('       :ColumnSixteen,                                      ');
    if qrySelect.FieldByName('ColumnSixteen').AsSTring = 'Y' then
      Sql.Add('     :ColumnSeventeen, :ColumnEighteen, :ColumnNineteen,                 ');
    if qrySelect.FieldByName('ColumnTwenty').AsSTring = 'S' then
    begin
      Sql.Add('   :ColumnTwenty,                                      ');
      Sql.Add('   :ColumnTwentyOne, :ColumnTwentyTwo, :ColumnTwentyThree,                ');
    end
    else
      Sql.Add('   :ColumnTwenty,                                      ');
    Sql.Add('     :ColumnTwentyFour)                                  ');
    {And then for all the parameteres, pass the value}
    Parameters.ParamByName('ColumnOne').Value := varColumnOne;
    ...
    Parameters.ParamByName('ColumnTwentyFour').Value := varColumnTwentyFour;
    ExecSQL;
  end;

I get the error on this line:

Sql.Add('       :ColumnTwelve,                                    ');

which is the 11th parameter in my insert statement. If I comment this line I get the error in the next parameter. If I put the value directly like this:

Sql.Add('     ' + varColumnTwelve + ',                            ');

It works fine, but I get the error in the next parameter.

So it makes me wonder: does ADOQuery has a limit of how many parameters it can handle? Or if this isn't the real issue, does anyone has a clue of how I can fix this?

---

Notes:

• I'm using Delphi 7 and Windows 8.1.

• The AV only (and always) appears when debugging, it does never appear if I execute the application directly through its ".exe".

• If I keep pressing "Run" after the error appears, it shows more and more AVs (I think that the number of AVs is the same as the number of parameteres that are added after the 10th), until the application continues running normally.

• The insert works after all the AVs appeared on the screen. I just want to understand why am I getting this error when everything looks fine.

Answer 1

Changing the SQL property of a TADOQuery causes the TADOQuery to respond to that change, re-applying the modified SQL to the internal ADO component objects as well as re-parsing the SQL to identify any parameters.

For this reason, it is not advisable to modify SQL incrementally in this way. Quite apart from anything else, it is highly inefficient to have the SQL applied and parsed over and over again, before it is completely assembled.

In this case, by the time you get to the point of adding your 11th parameter, the SQL has been applied and parsed 28 times!

The fact that the AV that then results is occurring within SQLOLEDB.DLL suggests that whatever problem is occurring is a result of the changes to the SQL being applied to the internal ADO objects rather than in the VCL processing to identify parameters etc. As such, there is not much you are going to be able to do to fix the problem. The best you can do is avoid it.

You can eliminate some of this processing by setting ParamCheck := FALSE whilst modifying the SQL. This will prevent the VCL from attempting to re-parse the modified SQL to identify parameters. However, it will not prevent the SQL from being re-applied to the underlying ADO components in response to each change.

As a diagnostic exercise you could try setting ParamCheck := FALSE while modifying your SQL. When done, call the Parameters.Refresh method to ensure that the parameters collection is updated to reflect the finished SQL:

qryInsert.ParamCheck := FALSE;
qryInsert.SQL.Add(..);
qryInsert.SQL.Add(..);
qryInsert.SQL.Add(..);
qryInsert.SQL.Add(..);

qryInsert.Parameters.Refresh;

NOTE: With ParamCheck set to FALSE, you must call Parameters.Refresh before attempting to set any parameter values, otherwise the parameters won't yet exist in the Parameters collection!

If the AV still occurs after this change then this even more strongly indicates some issue with the internal ADO components not behaving well in response to repeated changes to the SQL, perhaps due to a failure to properly deal with incomplete (syntactically incorrect) SQL.

However, you can avoid triggering the change mechanism entirely by one of two ways.

Perhaps the simplest is to use BeginUpdate/EndUpdate on the TADOQuery SQL stringlist around your code that builds the SQL:

qryInsert.SQL.BeginUpdate;
try
  qryInsert.SQL.Add(..);
  qryInsert.SQL.Add(..);
  qryInsert.SQL.Add(..);

finally
  qryInsert.SQL.EndUpdate;
end;

This has the effect of suppressing the internal OnChange event inside the ADO query object until the call to EndUpdate, at which point the SQL will be applied to the internal ADO objects and the Parameters of the query object updated.

Alternatively you could assemble your SQL in an entirely separate string list, and then apply that to the TADOQuery SQL property as a single, direct change to the SQL.Text property:

sql := TStringList.Create;
try
  sql.Add(..);
  sql.Add(..);
  sql.Add(..);
  sql.Add(..);
  sql.Add(..);
  sql.Add(..);

  qryInsert.SQL.Text := sql.Text;

finally
  sql.Free;
end;

Either way, the result will be that the VCL will parse for parameters and the internal ADO objects will be updated only once, with a complete and (hopefully) syntactically correct SQL statement.

This second approach can involve a little less "boilerplate" - the try..finally here is purely to manage the temporary stringlist. If you were re-using an object in wider scope for this purpose, or using a SQL builder helper class which yields a simple string (as I do) then there is no need for this particular try..finally, making this a little more convenient and cleaner to apply:

SQLBuilder.Insert('MyTable');
SQLBuilder.AddColumn('ColumnOne');
SQLBuilder.AddColumn('ColumnTwo');

qryInsert.SQL.Text := SQLBuilder.SQL;

// qryInsert.SQL == INSERT INTO MyTable (ColumnOne, ColumnTwo)
//                  VALUES (:ColumnOne, :ColumnTwo)    

For example.

String vs TStringList

If your preferred technique for building the SQL yields a stringlist rather than a simple string, you may be tempted to assign the stringlist directly:

  qryInsert.SQL := sql;

But note that this performs an Assign() of the sql stringlist, effectively performing a 'deep copy'. You still need to ensure the stringlist assigned (sql in the above code) is freed appropriately.

Note also that this is also less efficient since it also copies other properties of the stringlist, including any objects associated with each string in the list. In this case where you are only interested in copying across the Text content of the stringlist there is no need to incur that (slight) and unnecessary overhead.