HXTT is not returning all records from FoxPro table after an insert |
Arif Khan |
2021-06-15 07:39:17 |
I am running a simple query to return records form a FoxPro table.
The rows added are not showing in my query unless I restart HXTT or wait a while.
I reindexed the table and still cannot get the results to show up immediately.
I downloaded the latest driver on June 8
Connection parameters are lockType=VFP;emptyStringAsNull=false; emptyDecimalAsZero=true
|
Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-15 18:18:12 |
Are that new record inserted by other FoxPro application?
What's your sql?
Please pasted your test code.
|
Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Arif A Khan |
2021-06-15 19:34:28 |
Here is the query I am running. If I manually or programmatically add another row to the table having the same rollno and lamtecid, it does not show up in the query result:
SELECT * FROM mmwiptran WHERE rollno = '0000030312-01' AND lamtecid = '816397'
Here is the table structure:
Structure for table: MMWIPTRAN.DBF
Number of data records: 276653
Date of last update: 06/15/2021
Code Page: 1252
Field Field Name Type Width Dec Index
1 ROLLNO Character 13 Asc
2 LAMNO Character 2 No
3 LOCATION Character 20 No
4 LAMTECID Character 6 Asc
5 ITEM Character 24 No
6 OVERRIDE Logical 1 No
7 APPROVAL Character 3 No
8 DEFECT Logical 1 No
9 ADDUSER Character 3 No
10 ADDDATE DateTime 8 No
11 LEAVE Logical 1 No
|
Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-15 21:50:06 |
First, makey sure that
select * from mmwiptran whre recno()=reccount();
can see the latest inserted row.
|
Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Arif A Khan |
2021-06-16 04:38:02 |
When I run this query, I see the new added record:
select * from mmwiptran where recno()=reccount()
Then I run this query and don't see record in the list. Restarted hxtt and it should up
select * from mmwiptran where rollno = '0000030312-01' and lamtecid = '816397'
|
Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-16 05:04:54 |
What's your FoxPro applcation?
Are you using CDX format index file? Because CDX should use buffer concurrency check, and it won't reload CDX if your application hasn't updated the concurrency check.
|
Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Arif Khan |
2021-06-16 05:44:07 |
I am inserting the record through a FoxPro application.
There are multiple CDX indexes in the table. I even reindexed the table.
Note - this issue is not in the older HXTT version we are using in our production environment. Lately we are getting too many timeout faults. So are testing the latest version.
Here is the test code
SET MULTILOCKS ON
USE mmwiptran
=CURSORSETPROP("BUFFERING", 3)
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
GATHER MEMO memvar
=tableupdate()
I then run the test query from SQuirreL SQL or Ignition Query Browser and don't see the added row
|
Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-16 08:09:23 |
|
Please download the latest package, which has disabled buffer concurrency check, and will reload simply CDX file. Please try it.
|
Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-16 08:10:47 |
BTW, your test code is using
APPEND BLANK
which the inserted blank record can not be fetched by
select * from mmwiptran where rollno = '0000030312-01' and lamtecid = '816397'
?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-16 12:42:51 |
We have tested with the new version dated 06-16-2021 and find that the problem persists. We were able to verify that appending/inserting records into tables that do not have an index show up immediately in our queries against hxtt, but any table which has an index is showing the problem.
So, to summarize:
- New rows are added to a table (that has indexes) via a VFP application or Foxpro directly.
- Running a query against hxtt running as a service (via jsl64) does not return the new rows.
- Adding new rows to a table with no index doesn't exhibit this behavior.
- Restarting the hxtt service causes the added rows to then be returned by the queries.
This is a critical requirement because we have applications that use the hxtt service as a database server, and those applications need to see inserts/updates to those rows as soon as they occur.
Thanks.
--- Kevin
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-16 12:44:10 |
Forgot to mention that we will be emailing the tables to you at support@hxtt.com.
--- Kevin
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-17 06:36:15 |
Supported. It's resulted by match cache, which should be disabled for XBase compatible mode.
Please download the latetst package.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Arif Khan |
2021-06-17 07:42:29 |
Downloaded the latest and still not seeing the new rows.
Is there a setting, parameter I need to add to refresh the cache?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-17 17:22:23 |
You needn't any new parameter. Just run "select * from mmwiptran where rollno = '0000030312-01' and lamtecid = '816397'" again.
For instance, run "select * from mmwiptran where rollno = '0000030312-01' and lamtecid = '816397'" in HXTT DBF, you will fetch nothing for your sample.
In VFP,
open database matmgt share //you need share
SET MULTILOCKS ON
USE mmwiptran shared //you need share
=CURSORSETPROP("BUFFERING", 3)
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
m.rollno = '0000030312-01' //you need that statement
m.lamtecid = '816397'//you need that statement
GATHER MEMO memvar
=tableupdate()
Now run "select * from mmwiptran where rollno = '0000030312-01' and lamtecid = '816397'" in the same connection of HXTT DBF, you will see the new row for your sample.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-18 05:02:13 |
As a side note - Arif Khan can no longer access this site. Can you please fix that?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-18 07:46:02 |
We have done more testing, and using your syntax, we find that the first insert is visible through HXTT, but subsequent inserts are not. Here is the code we're using:
CLOS ALL
SET EXCLUSIVE OFF
SET MULTILOCKS ON
OPEN DATABASE \\payara-stg\_groups\rawtest\data\matmgt.DBC SHARED
USE mmwiptran SHARED
=CURSORSETPROP("BUFFERING", 3)
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
m.rollno = '0000038033-07'
m.lamtecid = '565050'
m.adduser = 'X5'
GATHER MEMO memvar
=tableupdate()
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
m.rollno = '0000038033-07'
m.lamtecid = '565050'
m.adduser = 'X6'
=tableupdate()
The first insert with adduser = X5 is visible from HXTT clients, but the second insert with adduser = X6 is not visible to HXTT clients.
Of course, restarting the HXTT service results in all records being visible.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-18 16:37:01 |
First, maybe spam filter action wrongly. Remove it from spam IP list.
Secondly, you missed one "GATHER MEMO memvar" for your 2rd insert.
Use com.hxtt.sql.Admin
jdbc url : jdbc:dbf:/d:/test/dbf/issue5/LamtecData20210616?lockType=VFP;emptyStringAsNull=false; emptyDecimalAsZero=true
query：select * from mmwiptran where rollno = '0000038033-07' and lamtecid = '565050'
Result: 0 rows
CLOS ALL
cd D:\test\dbf\issue5\LamtecData20210616
SET EXCLUSIVE OFF
SET MULTILOCKS ON
OPEN DATABASE matmgt SHARED
USE mmwiptran SHARED
=CURSORSETPROP("BUFFERING", 3)
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
m.rollno = '0000038033-07'
m.lamtecid = '565050'
m.adduser = 'X5'
GATHER MEMO memvar
=tableupdate()
query：select recno(),* from mmwiptran where rollno = '0000038033-07' and lamtecid = '565050'
Result: 1 rows
SCATTER MEMO memvar
APPEND BLANK
m.adddate = DATETIME()
m.rollno = '0000038033-07'
m.lamtecid = '565050'
m.adduser = 'X6'
GATHER MEMO memvar /*NOTE: You need that statemen. Missed it, your Foxpro won't insert the insert row in fact */
=tableupdate()
query：select recno(),* from mmwiptran where rollno = '0000038033-07' and lamtecid = '565050'
Result:2 rows
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin Penrose |
2021-06-20 17:19:42 |
I have sent you an email to support@hxtt.com that contains a screenshot which shows the behavior will are still seeing.
Let me know if you have questions.
--- Kevin
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-20 17:52:11 |
|
But you forgot to attach the screenshot.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-20 18:07:35 |
|
The email I forwarded had the screenshots in it. Do you not see them?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-20 18:07:35 |
|
The email I forwarded had the screenshots in it. Do you not see them?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-21 01:58:18 |
|
Yeah. You haven't attached an iamge.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-21 07:41:25 |
Use SQuirrel SQL Client (I used it for new test)
jdbc url : jdbc:dbf:/d:/test/dbf/issue5/LamtecData20210616?lockType=VFP;emptyStringAsNull=false; emptyDecimalAsZero=true
SQL：select * from mmwiptran where rollno = '0000036606-05' and lamtecid = '565995'
Result: 0 rows
CLOS ALL
cd D:\test\dbf\issue5\LamtecData20210616
SET EXCLUSIVE OFF
SET MULTILOCKS ON
OPEN DATABASE matmgt SHARED
USE mmwiptran SHARED
=CURSORSETPROP("BUFFERING", 3)
SCATTER MEMO memvar
m.adddate = DATETIME()
m.rollno = '0000036606-05'
m.lamtecid = '565995'
m.adduser = 'X2'
APPEND BLANK
GATHER MEMO memvar
=tableupdate()
In SQuirrel SQL Client, run
SQL：select * from mmwiptran where rollno = '0000036606-05' and lamtecid = '565995'
Result: 1 rows
So what's your test tool for Java side?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-21 07:52:11 |
|
BTW, you should download the latest package to test.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-21 10:03:14 |
|
We have downloaded the latest version and installed it. We continue to see the same behavior. Would it be possible to set up some type of video call so that we can, hopefully, resolve this issue?
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-21 18:32:11 |
Use testCachedSql51.java (pasted code below)
package test.jdbc.dbf;
import java.sql.*;
public class testCachedSql51 {
public static void main(String[] args) throws Exception {
Class.forName("com.hxtt.sql.dbf.DBFDriver");
String url = "jdbc:dbf:/d:/test/dbf/issue5/LamtecData20210616?lockType=VFP;emptyStringAsNull=false; emptyDecimalAsZero=true";
java.util.Properties properties = new java.util.Properties();
java.sql.Connection connection = java.sql.DriverManager.getConnection(url, properties);
String sql = "select * from mmwiptran where rollno = '0000036606-05' and lamtecid = '565995'";
java.sql.PreparedStatement psSelect = null;
psSelect = connection.prepareStatement(sql);
System.out.print("First query: ");
execQuery(psSelect);
System.out.println();
System.out.println("Now insert a row with rollno = '0000036606-05' and lamtecid = '565995' via VFP and press ENTER!");
System.in.read();
System.out.print("Second query: ");
execQuery(psSelect); // NOTHING COMES HERE!!!
psSelect.close();
connection.close();
}
public static void execQuery(PreparedStatement ps) throws Exception {
// ps.setPoolable(false);
ResultSet rs = ps.executeQuery();
printMessage(rs);
// rs.close();
}
private static final void printMessage(ResultSet rs) throws SQLException {
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
/*
* for (int j = 1; j <= iNumCols; j++) {
* System.out.println(resultSetMetaData.getColumnLabel(j) + " " +
* resultSetMetaData.getColumnTypeName(j) + " " +
* resultSetMetaData.getColumnType(j) + " " +
* resultSetMetaData.getPrecision(j) + " " +
* resultSetMetaData.getScale(j) ); }
*/
Object colval;
long ncount = 0;
while (rs.next()) {
ncount++;
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
System.out.print(colval + " ");
}
System.out.println();
}
// rs.close(); // Let the CONCUR_UPDATABLE resultSet release its open files at once.
// rs = null;
System.out.println(ncount + " rows.");
}
}
Output:
First query:
0 rows.
Now insert a row with rollno = '0000036606-05' and lamtecid = '565995' via VFP and press ENTER!
Second query:
0000036606-05 09 ADH-L 565995 ADH-57-156 false false X2 2014-08-05 17:22:40.0 false
1 rows.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-06-22 09:50:16 |
Ran your java code against our latest version. here's the results:
1st attempt:
First query: 0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null null CHZ 2021-06-18 07:20:36.0 null
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X1 2021-06-18 21:33:00.0 false
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X2 2021-06-18 21:33:19.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X3 2021-06-21 11:20:54.0 false
4 rows.
Now insert a row with rollno = '0000036606-05' and lamtecid = '565995' via VFP and press ENTER!
Second query: 0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null null CHZ 2021-06-18 07:20:36.0 null
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X1 2021-06-18 21:33:00.0 false
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X2 2021-06-18 21:33:19.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X3 2021-06-21 11:20:54.0 false
4 rows.
Second attempt:
First query: 0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null null CHZ 2021-06-18 07:20:36.0 null
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X1 2021-06-18 21:33:00.0 false
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X2 2021-06-18 21:33:19.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X3 2021-06-21 11:20:54.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X4 2021-06-22 12:30:55.0 false
5 rows.
Now insert a row with rollno = '0000036606-05' and lamtecid = '565995' via VFP and press ENTER!
Second query: 0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null null CHZ 2021-06-18 07:20:36.0 null
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X1 2021-06-18 21:33:00.0 false
0000036606-05 03 ADH-L 565995 ADH-12-2C HV false null false X2 2021-06-18 21:33:19.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X3 2021-06-21 11:20:54.0 false
0000036606-05 09 ADH-L 565995 ADH-57-156 false null false X4 2021-06-22 12:30:55.0 false
5 rows.
Still not seeing inserted rows.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
HXTT Support |
2021-06-22 20:32:11 |
|
Sorry. The released package used a wrong compiled switch. Please redownload it.
|
Re:Re:Re:Re:Re:Re:Re:Re:HXTT is not returning all records from FoxPro table after an insert |
Kevin M Penrose |
2021-07-08 06:53:18 |
Happy to report that the package dated 2021-06-22 has been downloaded and installed and has fixed this issue. Thanks for the rapid response.
--- Kevin
|
Main
webmaster@hxtt.com