Record deletion and indexes |
Kevin Penrose |
2020-04-30 16:31:55 |
We are using HXTT in a client/server environment, with a connection string that uses lockType=VFP. The tables we're access have multiple indexes, stored in CDX files, and all index names are less than 10 characters.
We are seeing a behavior when deleting records through HXTT that suggests that the indexes are not being updated correctly. We have a table that we delete 4 records from. HXTT does a soft delete, and when we query the table without an index, we can see the deleted rows. It we use an index on one column in the table, then the deleted records are not returned in the query. If we then re-index the table, the deleted records are again visible when we query with the same index.
Indexing seems to work fine on inserts, but deletions seem to do something to the table header but don't actually update the indexes associated with the table. According to our Foxpro engineer: Looks like records deleted via HXTT are losing their index and are not searchable from VFP using the seek command which looks in the index file. When deleting records from VFP, they are searchable.
We have a CDX file with a lot of indexes, all with names less than 10 characters in length. Is there anything there we should be concerned with?
How does HXTT select which index to use when querying a table?
Thanks for any help you can provide.
|
Re:Record deletion and indexes |
HXTT Support |
2020-04-30 19:07:39 |
You can use deletesAreVisible=true connection property, then your index file will contains those deleted rows.
deletesAreVisible: Indicates whether DBF's resultSet includes deleted records. Default: false
Remember to use deleted() function in sql or ResultSet.isDeleted() method if you used deletesAreVisible=true
|
Re:Re:Record deletion and indexes |
Kevin Penrose |
2020-05-06 04:24:55 |
We also have multiple visual foxpro and hxtt connections reading and writing the foxpro tables. Is there a problem with indexes in that type of configuration?
I also downloaded and installed a version dated 4-30-2020 and there is a problem with all of the column names being returned in upper case. Is this new, and is it a configurable option? None of our queries work with that because of column not found errors.
|
Re:Re:Re:Record deletion and indexes |
HXTT Support |
2020-05-06 05:42:02 |
> We also have multiple visual foxpro and hxtt connections reading and writing the foxpro tables. Is there a problem with indexes in that type of configuration?
If you use deletesAreVisible=true, then HXTT DBF will index deleted rows lick VFP.
> there is a problem with all of the column names being returned in upper case.
HXTT will return upper or lower case according to the stored column name. For instance,
select * from abc; /* Return char1 or CHAR1 according the stored column name*/
select char1 from abc;select Char1 from abc;select CHAR1 from abc;/* All are correct*/
> Is this new, and is it a configurable option? None of our queries work with that because of column not found errors.
What happened? "select top 1 * from yourTable" can show all column names in your table.
|
Re:Re:Re:Re:Record deletion and indexes |
Kevin Penrose |
2020-05-06 06:15:32 |
All of our column names were created as lower case names, however a select * from abc returns all of the column names as upper case. Without exception.
|
Re:Re:Re:Re:Re:Record deletion and indexes |
HXTT Support |
2020-05-06 07:39:15 |
It's strange since HXTT DBF allows lower case default.
For intance,
create table abc1 (char1 varchar(12),INT1 int);
select * from abc1;
show:
char1 char1 VARCHAR 12 12 0
INT1 INT1 INTEGER 4 10 0
If use allowLowerCaseFieldName=false connection property,
will create a table with only upper case field name:
show
CHAR1 CHAR1 VARCHAR 12 12 0
INT1 INT1 INTEGER 4 10 0
|
Re:Re:Re:Re:Re:Re:Record deletion and indexes |
Arif Khan |
2020-05-20 08:33:03 |
The uppercase fields names are being returned in uppercase even when setting allowLowerCaseFieldName=true
Shown below is the code having the select and I am referencing the fields names in uppercase. If I use lowercase it errors.
Our entire code is using lowercase, so its failing everywhere
shiftsData = system.db.runPrepQuery("select top 1 * from mmshifts where lamno = ? order by adddate desc", [rootContainer.machine], project.foxProUtils.getFoxProDatabaseName())
if len(shiftsData) <> 0 and (shiftsData[0]['ENDDATE'] == None or shiftsData[0]['ENDDATE'] == ""):
oldShift = True
else:
# Shift is not started, bring up login box
loginWindow = system.nav.openWindow("Laminator Windows/Login")
system.nav.centerWindow(loginWindow)
lamholdData = system.db.runPrepQuery("select coatwt, approver from mmlamhold where lamno = ?", [rootContainer.machine], project.foxProUtils.getFoxProDatabaseName())
if lamholdData <> 0:
coatWt = lamholdData[0]['COATWT']
approver = lamholdData[0]['APPROVER']
|
Re:Re:Re:Re:Re:Re:Re:Record deletion and indexes |
HXTT Support |
2020-05-20 22:02:14 |
|
What's your HXTT DBF version? Please login to download the latest package and check whether your issue disappear.
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion and indexes |
Arif Khan |
2020-05-21 09:28:35 |
Got the latest version and installed it. Still getting the field names returned from Select SQL in uppercase.
Here is the version I installed:
Implementation-Title: com.hxtt.sql.dbf
Copyright: Yonghong Zhao
Implementation-Version: 7.0.096 on May 06, 2020
Specification-Vendor: Heng Xing Tian Tai Lab
Specification-Title: HXTT DBF JDBC 4.2 Package
Comment: JDBC 4.2 Package for Xbase database
Specification-Version: 7.0.096 on May 06, 2020
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion and indexes |
HXTT Support |
2020-05-21 17:53:41 |
Please read here, or use SquirrelSQL Client to build a JDBC connection,
then run sql
select top 1 * from mmshifts where lamno = 123 order by adddate desc;
to see what happen.
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion and indexes |
Arif Khan |
2020-05-22 12:29:23 |
All the column names are shown in uppercase
When I run the same query in the previous version driver, they are in lowercase
Do you have an email where I can send the screen shot?
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion and indexes |
HXTT Support |
2020-05-22 18:53:19 |
> None of our queries work with that because of column not found errors.
You can email support@hxtt.com . Anytime, HXTT DBF's SQL syntax is not case sensitive, so that what's your column not found message? Whether your table is not free table?
|
Main
webmaster@hxtt.com