>
> -----Original Message-----
> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On

Behalf Of Helen Borrie

> Sent: Friday, July 28, 2006 8:25 PM
> To: IBObjects@yahoogroups.com
> Subject: Re: [IBO] Quoted fields seem to be broken in TIBODataset when

they're involved in indexing

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

That isn't an example, it's the actual code. It was valid for
the BDE.

>
> 2. The KeyLinks EXACTLY as you have them defined.

Haven't defined any.

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

None, because I'm using the primary key.

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

4.6.B


There's an internal inconsitency in the way IBODataset treats field
names. When looking up indexed fields, it's doing FindField() on the
TIBOTable. The field names are stored as they were with the BDE, e.g.,
'Acc ID', 'Invoice #'. However, the string it's using for the search is
the fully qualified field name, e.g. 'ARINV."Acc ID"', 'ARINV."Invoice
#"', so of course FindField always fails.

I've changed the code to deal with this and the FindNearest works, but
there are multiple places which do this FindField, so I still get the
"Not Indexed..." error. This is caused by the same sort of apples and
oranges comparison at a different place, but I had to leave yesterday
before I could patch that up and try it out.

I can send you the code directly if you want to see what I mean
(I'm reluctant to post much about the source code since it's a
licensed product).




[Non-text portions of this message have been removed]