At 09:32 PM 1/06/2007, you wrote:

> > >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.

ah, ok.

>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)

Indeed. As an ODS 10 or higher database, its NUMERIC(18,0) fields
are stored as 64-bit integers (BIGINT is an alias for NUMERIC(18,0)).

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

Defined as DATE in ODS 10 or higher, it can only be the date
part....all in all, the possibility of ODS 9 data structures causing
the undetected storage differences is more or less eliminated. If
the database was actually created as ODS 10 or higher, it is totally
eliminated.

>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?

On-disk structure, the way data are stored in the database. ODS 9
and below had significant differences from later version in the way
several data types were stored. But it seems not to be a factor with
this database.

> > 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?

You already did - gstat -h told you the default character set is
NONE. That means data are stored in straight ascii format. The
database won't know how to locate strings containing accented
characters. Your sample data, at least, doesn't appear to contain
accented characters.

>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.

If you are storing strings with Spanish accented characters, using
character set NONE is going to bite you one way or another, since it
recognises only the US ASCII set of characters as characters.

I suggest you treat today's database rebuild as a practice exercise
but rebuild the for-production database with a Spanish-aware
character set as the default character set and a suitable collation
on indexed fields to allow searching and matching to make
sense. Then make sure that your client programs connect with that
character set.

./heLen