Unique index restriction issue |
Andrew |
2015-03-25 20:31:59 |
We are evaluating this driver for use with creating middleware to an active custom DOS Foxpro app. We are using Lucee (which is an open source CFML engine on the JVM)
We only need to use SELECTS, no date inserts or modifications, etc. the current multiuser Foxpro app will be in use simultaneously and is the only one allowed write/update/delete access, etc.
The issue we are running into seems clearly illustrated by this segment in the hxtt doc:
2. I receive 1 record back, however there should be 8 records returned.My SQL is "SELECT * FROM Schshift@brian WHERE PSCHED='0001092478'"
When doing a join, sometimes we get 1 result when we clearly should get many. Using an ODBC driver provides the correct results. When I remove the IDX file from the directory temporarily, I get the multiple results I am looking for. So this proves it is the index as your doc seems to indicate.
So... If we cannot modify the Foxpro app, how do we get the proper result using this driver? Is there a setting? A workaround?
Also, these indexes are compiled so I cannot read them, so I have no clue which of them have these unique indices except via trial and error.
What options do we have?
|
Evaluation limitations |
Andrew |
2015-03-25 20:38:18 |
We are evaluating this driver and see that there are 3 limitation:
1000 max records on select
30 days trial
50 queries?
I understand the first 2 and that is fine but I just need some clarity on what the 50 query limit is. It's not very well spelled out exactly what the limit is and how/when it kicks in? And I just want to account for it in our tests so we know when were coming up against a real limitation or an imposed one by virtue of it being a trial!
|
Re:Re:Unique index restriction issue |
HXTT Support |
2015-03-26 00:36:40 |
>"SELECT * FROM Schshift@brian WHERE PSCHED='0001092478'"
That simple select sql should returns 8 rows for your test, but your join sql fetches only one sql for trial limitation?
>50 queries?
It's a trial limitation. And we will email you a version without that limitation.
|
Re:Re:Re:Unique index restriction issue |
Andrew |
2015-03-26 16:44:08 |
Thank you for your reply (and email). For clarification on both topics:
re: the 50 query limitation - mostly it is not clear to us what that actually "means" (e.g. we can only test a "total" of 50 queries before the driver times out? Or we can only test 50 queries "per day" during the trial period? Or???) Perhaps this point is now moot with the emailed driver anyways, but I just wanted to understand the limitation so that we'd understand when we were hitting it in our trial!
re: the Unique Index issue - yes. An example would be where I have a table ORDER with a REF number field and an ORDDET table that has 8 records with that same REF number. When I do a JOIN on those tables to get the order and its details I sometimes receive only a single row, instead of 8 rows. If I remove the IDX file, I then get the correct 8 rows. With a MSFT or Advantage SQL ODBC driver, I get the correct 8 rows too. So it is just the Hxtt DBF driver that causes this to happen.
It appears to be just like the UNIQUE index issue in your documents that I quoted before. So my question is if there is a way for us to work around that issue in the WHERE cluase using the Hxtt driver? Also, it would be nice to understand the issue more.
Thank you again!
|
Re:Re:Re:Re:Unique index restriction issue |
HXTT Support |
2015-03-27 07:10:50 |
>the Unique Index issue
Tested and supported. The latest package won't utilize obsoleted unique index for query match.
|
Re:Re:Re:Re:Re:Unique index restriction issue |
Andrew |
2015-03-27 07:34:53 |
OK, great! Is that fix in the one you just emailed to me 2 days ago with the query trial query restrictions removed for our testing? If not, could you please resend?
Thanks for the great support!
|
Re:Re:Re:Re:Re:Re:Unique index restriction issue |
Andrew |
2015-03-27 20:25:49 |
|
OK, I tried the latest download (2015-03-27 07:21) and it has the same issue?
|
Re:Re:Re:Re:Re:Re:Re:Unique index restriction issue |
Andrew |
2015-03-28 12:07:00 |
|
So, this is NOT an issue where the index command was used with UNIQUE, but it does exhibit that same behavior. I have emailed you a copy of the file for analysis.
|
Re:Re:Re:Re:Re:Re:Re:Re:Unique index restriction issue |
HXTT Support |
2015-03-28 23:05:43 |
Checked. What program create that IDX file? Because HXTT DBF has detected it as a primary key index according to an idx tag, so that it won't match more after found the first matched row according to your IDX file. The latest package will be released in 24 hours to support your sample.
BTW, search on web, and I can find the similar issue:
FoxBase+ index files (IDX) and VFP 5.0 CANDIDATE tag Andrew Jennin Sat, 24 Jul 1999 03:00:00 GMT
I am trying to write a new system in VFP 5.0 which integrates with an
existing SBT application running under multiuser FoxBase+.
I am having the following problem: when I open the existing standalone
index file to insert new records, VFP bombs out telling me that I have
violated the integrity of the index. Suspending the program and taking
a look at the structure of the old FoxBase file and index, I find that
VFP has decided that the index key is a CANDIDATE, ie: must have
unique values.
In this case, the index is not unique, nor does it need to be unique.
Has anybody else encountered this?
|
Re:Re:Re:Re:Re:Re:Re:Re:Unique index restriction issue |
Andrew |
2015-03-28 23:41:54 |
|
it is "Multi-User FoxBASE+ 2.10" running on DOS.
|
Main
webmaster@hxtt.com