BorlandTalk.com

Avert · Guest

Hi--

This one sounds odd, I know. BDE 5. Paradox tables. D6.

The table in question has 210,000 records. Each maybe 1 K long.

Anyway, here's what's happening.

If I add a record, it's not visible with my standard SQL. Now, as you can
imagine, the previous 209,999 records were added with exaclty the same
program and SQL and could all be displayed.

There are 2 cases where the record is visible:

1) i exit and run Chimneysweep on the table in question rebuilding indexes
or
2) use a slightly different SQL.

Here is the sql that worked for 209,999: (to display)

Select P.* from Parts P
where P.RepairKey = "12345"

As you can guess, the "12345" is the key for a certain repair (in a repair
table) AND I'm trying to get all the parts in the parts table for that
repair. Like I said, this always worked. Until today.

If I run Chimneysweep, it works again fine BUT if I add another part to the
repair, they don't show up again.

However, this SQL does work (ie. show the parts without running
chimneysweep)

Select P.* from Parts P
inner join on Repairs E
on (P.RepairKey = E.UniqueKey)

This runs fine.

E.UniqueKey is the repairKey for any given repair. In this case, the repair
in question for the parts. That is, it was in both cases.

In both cases the "same" query is being run since the ONLY E.UniqueKey
available is the "12345".

Like I said...odd....it's run perfectly until now, for this user. All my
other users are fine. It's not that users setup as I've had him ship me his
data and I've verified it here, on my development machine.

The table does have an index built on P.RepairKey BUT it's NOT the primary
index. There is also one on E.UniqueKey and it's the Primary Key.

Chimneysweep doesn't report any errors.

Any thoughts?

TIA

--
Kelly

Bill Todd (TeamB) · Guest

How big is the .DB file?
What is the block size of the Paradox table?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Avert · Guest

Bill--

2048 block size (i never change the settings in the BDE Admin program) and
the table is about 198Megs.

The main user is on a LAN with a server holding the database.

Some other users are on a WAN (Citrix) and it occurred on my PC too, with
the DB hosted on the local drive.

Oddly, if you add a couple and run Chimneysweep they show up using the
sinple SQL.

The "complicated" SQL always shows them.

It's almost like the Index is not being updated or something.

Thanks.
Kelly

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote

Bill Todd (TeamB) · Guest

The maximum size of a Paradox .DB file is 64k blocks. With a 2k block
size that is 128 mb. Change the block size to 4k and see if that
solves your problem. There is a utility at
www.borland.comdevsupportbdeutilities.html that will change the
block size of an existing table.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Avert · Guest

I grabbed the table interrogator and the Parts table does have a block size
of 2048

I was wrong when I related the size of the table, it's 107,321,344 bytes.
Sorry for the confusion.

This is very odd as again, the "simple" sql returns what is expected and has
been returned for all my users for all these years properly AFTER
Chimneysweep is run (and no errors are found in chimneysweep).

The "complicated" SQL returns that proper result before and after
Chimneysweep as it has for the life of the app.

Hmmm.....

DO you think it could be the blocksize?

thanks again!
kelly

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote

Bill Todd (TeamB) · Guest

On Mon, 24 May 2004 17:15:53 -0600, "Avert" <avert (AT) telusplanet (DOT) net>
wrote:

Avert · Guest

My user said he wants to wait a while before we mess with his table.

I will try it and, depending on the outcome, start another thread.

Thanks for all your help Bill, I really appreciate it on this and all my
questions.

(PS. I'm working on a Oracle and Firebird version so I can drop the BDE.)

Regards,
Kelly

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote