Delphi AdoDB Connection.Execute() exception handling

Question

I have this code

try
  ADOconnection1.connected:=true;
  ADOconnection1.Execute(memo1.Lines.text);
  if AdoConnection1.Connected then  AdoConnection1.Close;
  ShowMessage('Floorplan imported to DB');
except
  ShowMessage('DB error');
end;

Works fine but exception handling is not sufficient.

DB error message shows query has syntax error. But how to handle exception like this?

Msg 2628, Level 16, State 1, Line 2
String or binary data would be truncated in table 'sites.dbo.sites_plans', column 'building'.
Truncated value: 'ISI_Head'.
The statement has been terminated.

Nothing is inserted and SQL Server throws error if I run the query manually in SQL Server Management Studio, but my application shows 'Floorplan imported to DB' and this is wrong. Because nothing is really inserted.

Is possible get some return value from

ADOconnection1.Execute(memo1.Lines.text);

how many rows have been inserted? Or some return code to handle error?

Thanks for any help

Holy Thirteen · Answer 1 · 2023-04-28T09:01:41.790

You must handle exceptions to know what exception exactly raised. You can change the exception block in your code in this way to catch exception message exactly.

except
  on e: exception do
  begin
    ShowMessage('DB error'#13#10 + e.Message + #13#10 + e.ClassName);
  end;
end;

You can behave related to what the e.Message value is.

This will be useful too.

In Delphi you will receive SQL Server 'query execution response' as text. If you want to parse and extract details form it, you have to do it manually on e.Message in Exception.

If you want more details like how many rows affected, you have to return in your query manually too, and handle in Delphi side.

By using Stored Procedures in SQL Server, you can prepare detailed information in output parameters and handle them in Delphi side in addition to your main query.

For example you can find the count of inserted rows after your query by selecting SQL Server @@ROWCOUNT parameter.

No this not helps, because in type of exception as I writen in my post exception block not applicated. — milenjao, Apr 28 '23 at 08:23

score 1 · Answer 2 · answered May 03 '23 at 14:48

TADOConnection.Execute() has an overload which has a var RowsAffected output parameter.

var
  RowsAffected: Integer;

try
  ADOconnection1.Connected := True;
  try
    ADOconnection1.Execute(Memo1.Lines.Text, RowsAffected);
  finally
    AdoConnection1.Close;
  end;
  if RowsAffected > 0 then
    ShowMessage('Floorplan imported to DB')
  else
    SbowMessage('Floorplan not imported to DB'); 
except
  ShowMessage('DB error');
end;

Delphi AdoDB Connection.Execute() exception handling

2 Answers2