Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: How to display Informix ISAM error code


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Jan 11, 2016 12:53 AM Last Post By: Dmitry Arefiev
Paulo Mariano

Posts: 29
Registered: 11/25/12
How to display Informix ISAM error code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2016 2:59 PM
Hello.

In an application built with Delphi XE6 and Informix, the following exceptions have been raised in two distinct parts of the application:
1) SQL state: HY000. Native code: -245.
[Informix][Informix ODBC Driver][Informix] Could not position within a file via an index. sqlerrm(<table name>)

2) SQL state: HY000. Native error: -271.
[Informix][Informix ODBC Driver][Informix] Could not insert new row into the table. sqlerrm(<table name>)

These messages were displayed in try/except blocks:

except
on E: Exception do
.
.
.
MessageDlg('Database error: ' + (e as EInfxNativeException).Errors[0].Message);

These are the corresponding Informix descriptions of the errors mentioned above:
1) -245 Could not position within a file via an index.
The database server encountered an error when it attempted to look up a row through an index. Check the accompanying ISAM error code for more information. The table file or the index file might have been corrupted. Unless the ISAM error code or an operating-system message points to another cause, run the oncheck or bcheck utility to verify file integrity.

2) -271 Could not insert new row into the table.
This problem has many possible causes, including a locked table or a full disk. Check the accompanying ISAM error code for more information.

In both cases, the ISAM error code should be evaluated in order to accurately identify the problems.
How should the try/except block be modified so that the ISAM error is displayed?

Thank you in advance.

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: How to display Informix ISAM error code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2016 10:06 PM   in response to: Paulo Mariano in response to: Paulo Mariano
http://docwiki.embarcadero.com/RADStudio/Seattle/en/Handling_Errors_%28FireDAC%29
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Error.TFDDBError

try
  ....
except
  on E: EFDDBEngineException do
    E[0].ErrorCode --> native DB error code


--
With best regards,
Dmitry
Paulo Mariano

Posts: 29
Registered: 11/25/12
Re: How to display Informix ISAM error code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 6, 2016 11:16 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
http://docwiki.embarcadero.com/RADStudio/Seattle/en/Handling_Errors_%28FireDAC%29
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Error.TFDDBError

try
  ....
except
  on E: EFDDBEngineException do
    E[0].ErrorCode --> native DB error code


--
With best regards,
Dmitry

Hello, Dmitry.

Thank you for your reply.

In order to test your suggestion, I have created two tables:
1)
create table test1 
(
    id SMALLINT,
    description VARCHAR(30)
);
alter table test1 add constraint primary key (id);


2)
create table test2 
(
    id SMALLINT,
    test1_id SMALLINT,
    description VARCHAR(30)
)
alter table test2 add constraint primary key (id, test1_id);
alter table test2 add constraint foreign key (test1_id) references test1 (id);


Then, I inserted one row into each one of the tables:
3)
insert into test1 (id, description) values (1, 'test 1');


4)
insert into test2 (id, test1_id, description) values (1, 1, 'test 1 - 1');


When I tried to run statement #4 a second time, the following error message was displayed in Server Studio:
SQL Error (-268): Unique constraint (informix.u364_896) violated.
ISAM error: duplicate value for a record with unique key.

In a Delphi application, the same statement #4 was executed and, as suggested, the exception was evaluated using EFDDBEngineException:
testQuery.SQL.Text := 'insert into test2 (id, test1_id, description) values (1, 1, ''test 1 - 1'')';
try
	testQuery.ExecSQL;
except
	on E: EFDDBEngineException  do
	begin
	  ShowMessage('Count: ' + inttostr(E.ErrorCount) + chr(13) + chr(10) +
				  'Error code: ' + inttostr (E.ErrorCode) + chr(13) + chr(10) +
				  'FDCode: ' + inttostr(E.FDCode) + chr(13) + chr(10) +
				  'Message: ' + E.Message + chr(13) + chr(10) +
				  'Error code[0]: ' + inttostr(E.Errors[0].ErrorCode) + chr(13) + chr(10) +
				  'Error message[0]: ' + E.Errors[0].Message);
	end;
end;


The values displayed were:
Count: 1
Error code: -268
FDCode: 1200
Message: [FireDAC][Phys][ODBC][Informix][Informix ODBC Driver][Informix] Unique constraint (informix.u364_896) violated.
Error code[0]: -268
Error message[0]: SQL state: 23000. Native error: -268 Error message: [Informix][Informix ODBC Driver][Informix] Unique constraint (informix.u364_896) violated.

The ISAM error code and/or the equivalent error message were not displayed.

Is there an EFDDBEngineException property that would store this information?
Thank you again.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: How to display Informix ISAM error code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 11, 2016 12:53 AM   in response to: Paulo Mariano in response to: Paulo Mariano
Thank you for providing this info. At moment there is no simple possibility to
get ISAM error code. But we implemented support for returning ISAM error code
for next RAD Studio version. There it will be simple:
  try
    FDQuery1.ExecSQL;
  except
    on E: EInfxNativeException do
      E[0].ISAMError // returns Informix ISAM error code


--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02