Hi Helen,

> >SQL> CONNECT D:\FIREBIRD\CORRUPT.FDB USER SYSDBA PASSWORD masterkey;
> >Database: D:\FIREBIRD\CORRUPT.FDB, User: SYSDBA
> >
> >SQL> SHOW TABLE CCP_ARBOLPRECIOS;
> >IDCATPRO NUMERIC(18, 0) Not Null
> >CLAVE VARCHAR(100) Not Null
> >FECHASALIDA DATE Not Null
> >PRECIOS BLOB segment 80, subtype TEXT Nullable
> >CONSTRAINT INTEG_9:
> > Primary key (IDCATPRO, CLAVE, FECHASALIDA)
> >
> >SQL> SHOW INDICES;
> >
> >RDB$PRIMARY3 UNIQUE INDEX ON CCP_ARBOLPRECIOS(IDCATPRO, CLAVE,
> >FECHASALIDA)
> >
> >RDB$PRIMARY2 UNIQUE INDEX ON CCP_ARBOLPRODUCTOS(IDCATPRO)
>
> This looks quite weird. Did you create that index named RDBPRIMARY2
> ? Are you able to drop that index? (I suspect that someone has been
> meddling with your system tables...Firebird won't allow you to have
> two primary keys.)

The RDB$PRIMARY2 is the primary key of another table
(CCP_ARBOLPRODUCTOS) which was not affected by this problem. I believe
SHOW INDICES shows all indices on the database.

I can assure you that no premeditate attempt to write on the system
tables has been made. We use FlameRobin, but create our database using a
script.

> As for the primary key, the most obvious guess is that the database
> is ODS 9, in which case your DATE field is a timestamp, your
> NUMERIC(18,0) field is stored as DOUBLE PRECISION, the database is
> dialect 1 and isql will therefore be connecting to it as a dialect 1
> client. Depending on where the data came from, there might be
> thousands of records having identical date-parts but distinct
> time-parts; and unreliable display of these pseudo-integers at these
> extremities of scale.

The database is dialect 3. I checked it with FlameRobin (database
properties option):
ODS Version: 11
Page size: 4096
Pages: 198669
Size on disk: 776.05MB
Page buffers: 2048
Read only: false
Dialect: 3
Default character set: NONE
Sweep interval: 20000
Forced writes [ticked]

The Numeric(18,0) field was created as a BIGINT. Then I saw it appear as
NUMERIC(18,0)

The DATE field is supposed to contain only the date-part. We use the
.NET standard date objects.

So far, after we rebuilt the database from scratch (it contains cached
information) everything is working properly.

> For a reality check, find out the ODS version of your database and
> report here. For this, use gstat -h.

U:\DI_PUIGSEGUR>gstat -h d:\firebird\corrupt.fdb

Database "d:\firebird\corrupt.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 977867
Page size 4096
ODS version 11.0
Oldest transaction 977832
Oldest active 977861
Oldest snapshot 977861
Next transaction 977865
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Mar 30, 2007 13:11:52
Attributes force write

Variable header data:
*END*

BTW, What does ODS stands for?

> The other is the VARCHAR(100) that is used in the key. There is
> plenty of room for error there, involving non-printable characters,
> mismatched character sets, with/without case-insensitive collations
> and so on. You haven't provided any information about the default
> character set of the database or what character set you are using for
> the client (SET NAMES ...)

How can I check that?

The VARCHAR(100) field is created in a unique point of our .NET program
from different fields coming from another database and should not
contain non-printable characters. As I said before, after we rebuilt the
database from scratch everything is working properly. We didn't modify
our .NET program.

Jordi.