Errorcode from SQL Server

[Jelly]

When a SQL command fails due to an error (key violoation, wrong syntac etc.), I get an exception under Delphi. The following code reads the errormessage from the server.

Code: Select all

try
   qry.post ;
except
   on e: exception do
       showmessage(e.message) ;
end ;

But I could not find a way, to get the SQL Server Errorcode.

When I try to use the following code, the except section never seems to be executed, allthogh EDBClient is derived from exception.

Code: Select all

try
   qry.post ;
except
   on e: EDBClient do
       showmessage(inttostr(e.errorcode)) ;
end ;

What can I do to get the errorcode ?

[Jackson]

Try to use EMSError class instead of EDBClient. Do not forget to include OLEDBAccess.pas unit.

Code: Select all

uses
  ...OLEDBAccess;
...
  try
    // your code
  except
    on E: EMSError do begin
      // E.ErrorCode
    end;
  end;

[Jelly]

Thanks for this. I will try this only on Monday at work and give you feedback.

[Jelly]

Code: Select all

uses
  ...OLEDBAccess;
...

This OLEDBAccess.pas file does not exist on my system. Where can I find it?

[Jackson]

Sorry for measleading. This solution is correct only for SDAC.
Please specify type of DataSet component which you use in your example.

[Jelly]

I'm using the components from the dbExpress pallet, namely SQLQuery, SimpleDataset and SQLStoredProc from unit SqlExpr.

[Jackson]

Use TCustomClientDataSet.OnReconcileError event handler to handle any update error.

[Jelly]

Use TCustomClientDataSet.OnReconcileError event handler to handle any update error.

TSQLQuery doesn't have this event.

I think I didn't explain clearly enough what I was lookign for. OnReconcile Error handles the errors only that are are described in the held like this:

Write an OnReconcileError event handler to respond to error conditions that arise when the Reconcile method cycles through records that could not be applied to the database. This event handles only those update errors that were not handled in an OnUpdateError event handler.

What I am looking for is the real SQL server Msg Number when an error occurs. Trying to use this SQL statement e.g., an violoation to a unique key constraint might occur, and the SQL Query Analyzer gives me the following:

Code: Select all

insert into personen (Name) values ('Test')

Code: Select all

Server: Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'IX_Personen'. Cannot insert duplicate key in object 'dbo.personen'.
The statement has been terminated.

I have to catch this server error N° 2627.

How can I do this?

[Jelly]

Sorry for pushing. Does anybody have an idea how to catch the errorcode?

[Jackson]

You can't get server error code when you use TSQLQuery or TSQLStoredProc.
This is restriction of dbExpress technology (see ISQLCursor interface).
If you need error code you can parse error message to get it.

[Jelly]

You can't get server error code when you use TSQLQuery or TSQLStoredProc.
This is restriction of dbExpress technology

I can't understand this, but it just seems to be fact I have to accept.

If you need error code you can parse error message to get it.

There is no number inside the error message. The message just contains the text, the same as in Query Analyzer, but no message Nr.

It looks like I just have to forget about this ides to catch the error Nr