At 01:09 AM 29/07/2006, you wrote:

>I'm converting a database from Paradox to Firebird. This has been going
>fairly well: I've got a working database converter and am starting to debug
>the application using Firebird instead of BDE.
>
>However, now I'm getting to parts of the app which make heavy use of
>indexes. When any of the fields in an index require quoting, then I
>run into problems. For example, given this table:
>
>CREATE TABLE ARINV (
>"Acc ID" INTEGER NOT NULL,
>"Invoice #" VARCHAR(25) NOT NULL,
>"Date" DATE NOT NULL,
>"Sub Total" NUMERIC,
>"Tax" NUMERIC,
>"Freight" NUMERIC,
>"Total" NUMERIC,
>
>... and some more fields, clipped for brevity
>
>CONSTRAINT PK_ARINV PRIMARY KEY ("Acc ID", "Invoice #", "Date")
>);
>
>This line of code: tblInvoice.FindNearest( [ID, nil, nil] );
>throws an exception that says this: Cannot access index field 'ARINV."Acc
>ID" '
>from within TIBODataset.SetKeyFields[1].
>
>I have worked around this by using Locate instead of FindNearest, but that
>just
>means that later in the process, I get a different error: "Field 'Acc ID' is
>not
>indexed and cannot be modified."
>
>The FindNearest issue seemed to be fixed by changing the field names to
>things that didn't require quoting, e.g., ACCID, INVOICE_NO, ARINVDATE.
>
>The problem with this approach is that out of some 1200+ fields, I'm
>guessing
>that the names of at least 600 to 700 include spaces or other objectionable
>characters (or are just downright reserved words, like Date and Type). The
>amount of work involved in changing all of these fields is enormous, and
>greplace,
>while useful, will almost certainly not be appropriate for some of these
>changes
>since it may not know enough about the context of the string. The app would
>benefit thereby, but I'd like to do this at some point, but I'd guess it'd
>take another
>month, which I can't afford just now.
>
>On the other hand, if TIBODataset's never really going to support this, then
>I'll have
>to decide what to do next (fix the real problem or see if another
>DB/Framework combo
>will work better for me).
>
>So here are my questions:
>
>- Are there known issues in TIBODataset with indexes that involve quoted
>fields?
>- Will these be fixed?
>- Will they be fixed soon?
>
>I'm more than willing to help diagnose and fix this if someone can give me a
>little
>direction on where to look. I did try some stuff, with limited success [1].
>
>Michael D. Spence
>Mockingbird Data Systems, Inc.
>
>
>[1] I looked at the source for TIBODataset.SetKeyFields, and ISTM that the
>first
>FindField call in GetIndex would always fail, because the field name in
>tmpStr
>would always be the fully qualified field name (e.g., 'ARINV."Acc ID" '),
>whereas
>the FieldName members are always unqualified and without quoting (e.g., 'Acc
>ID').
>I added some code to strip the table name and any quotes, and that seemed to
>
>work, but then I got the other error ("Field 'Acc ID' is not indexed and
>cannot be
>modified.") so I removed the changes and worked around calling FindNearest
>by
>using Locate instead.
>
>If there isn't a deeper underlying problem and someone could tell me what
>"not Indexed and cannot be modified" really means here, I could look
>further.

It's possibly an unhandled exception filtering up...do you get an
error identifier?

First things first, though. please provide the following:

1. The EXACT FindNearest expression that fails. (Your example isn't
valid and it's inadequate to guess what is being offered up).

2. The KeyLinks EXACTLY as you have them defined.

3. The IndexFieldNames string EXACTLY as you have it defined.

4. Also, remind us what EXACT version of IBO you are using (you can
get this by looking at the Version property of your IBODatabase
object in the Object Inspector).

Helen