Trapping errors in TClientDataSet.CommandText

Question

I have a TClientDataSet connected to a TDataSetProvider, which in turn is connected to a TAdsQuery. I set the SQL command and then open the ClientDataset something like this:

try
  CDS.CommandText := 'SELECT * FROM tablename WHERE fieldname = 1';
  CDS.Open
except
  // trap exception here - this never gets executed!
end;

If the SQL statement in CommandText fails, however (syntax error or whatever) I get an exception within the Advantage code, but it never gets caught in my own exception handling code.

Is there any way for me to trap this error and report it nicely to the user. Alternatively is there a way to verify the syntax of an SQL query before executing it?

I'm using Delphi Pro 2009, and Advantage Local Server 9.

Answer 1

1. To execute a SQL command, you have to use TAdsQuery instead of TAdsTable.
2. It is strange, that TAdsTable does not return a error. As it should sayd something like "Unknown table".
3. To trap an error and report it to user:

   
       try 
         CDS.CommandText := 'SELECT * FROM tablename WHERE fieldname = 1'; 
         CDS.Open 
       except 
         on E: Exception do begin
           Application.HandleException(Self);
           // optionally Exit, Abort or what else, to stop execution of the next statements
         end;
       end;
   

4. There is not simple way to verify the syntax of a SQL query. Because, then you will need to reproduce a DBMS parser behaviour, what is a complex programming task. More simple is to submit a command to the DBMS, in the hope, that it is correct (optimistic approach).

Answer 2

Are you getting the exception code (not the trapped exception, which you want) when you are running this from within the IDE, or also when you are running from your executable directly? The reason I ask is that the IDE will report the error/exception first and if you do not continue, you will never see the actual exeception trap.

It is possible to turn off IDE trapping for certain error types, although I don't like to do that, on average. You can check to see if this is the problem by running your application on its own, outside of the Delphi IDE.

Also, the code that you originally wrote should keep all errors from bubbling to the surface, which is probably not what you want. By the same token, if you want to make it look better, you can display the message, as below, then handle it.

try 
  CDS.CommandText := 'SELECT * FROM tablename WHERE fieldname = 1'; 
  CDS.Open 
except 
  on E: Exception do begin
    ShowMessage(E.Message);
    // optionally Exit, Abort or what else, to stop execution of the next statements
  end;
end;

Answer 3

It's unclear to me why you are setting the command text on the TClientDataSet. I believe if you set the TAdsQuery.SQL property, then open the TClientDataSet, you will get the behavior you are looking for.

When I set this up and ran your code I got the exception "CommandText changes are not allowed".

procedure TForm57.Button1Click(Sender: TObject);
begin
  try
   CDS.CommandText := 'SELECT * FROM tablename WHERE fieldname = 1';
   CDS.Open;
  except
    on E : Exception do
      ShowMessage( 'got it:' + E.message );
  end;
end;

Answer 4

Advantage includes a EADSDatabaseError which will provide more information about the exception that was raised.

try
  CDS.CommandText := 'SELECT * FROM tablename WHERE fieldname = 1';
  CDS.Open
except
on E: EDatabaseError do
begin
  if ( E is EADSDatabaseError ) then
  begin
    ErrorString := (E as EADSDatabaseError).SQLErrorCode + E.Message;
    application.messagebox ( pchar(ErrorString), 'Advantage Database Error', 0 )
  end
  else
    application.messagebox (pchar(E.message), 'Native Database Error', 0 );
  end;
end;

You can check the syntax of the SQL statement before executing it by using the VerifySQL method of the TAdsQuery component. This will raise an EADSDatabaseError exception if the SQL syntax is incorrect.