We have a solution that work with foxpro tables. These tables are updated by the servoy solution and both by the original visual foxpro application. When we have a table with autointeger fields we have serious problem when record are added by the foxpro application while our foundset is already open…I’ll try to better explain the situation:
I open a form that uses table TEST; in this table the primary key is an autointeger fields; the next value of the field is 15; i add a new record in servoy solution and i have 16; anther new record from servoy and we habe 17; now in the foxpro application another users add a new record and receive as last id 18 (correct) and then another record with id = 19 (always correct); now in my servoy solution (where my form has always been opened i add a new record, but instead of 20 i have an id with 18; since this moment the autointeger starts from this value both from servoy that from foxpro and we had duplicated primary keys.
I have uset databaseManager.refreshRecordFromDatabase(foundset,-1) ro refresh the fundset before to call the save command, unsuccesfully. If i add a button to the form that refresh the foundset i can see the new records addede from foxpro, but when i add a new record the new id is not correct.
Hi, maybe the data in FoxPro is not yet written to disk when the Servoy user adds a record. With FLUSH() you can explicitly tell VFP to write the data to disk after a save. The problem is that VFP auto integer fields are not raised by the database but by the VFP executable. An alternative could be to store the PK values in a table instead and raise it after each insert or is this not an option for you?
The problem is that the foxpro application is a stable application and we are developing servoy solution to add functions to this application and if i have not to modifiy the foxpro application it is better…Also because to change how pk have been used and created is not a simple work…and some tables are used (and modified) in many places of our ERP (in foxpro)…Too work to do…I’ll try with flush() that is not so complicated…
What i don’t understand is way in my servoy solution i can see the new records added from the foxpro application, but the id it take is still that i have read the last time i have written this table from servoy…If i see the records it seems that foxpro hase flushed these records and the autointeger is updated…
The autoincrement information in Visual FoxPro is held in certain byte ranges in the DBF file header.
When you activate automatically incrementing values for any field in a table, Visual FoxPro sets byte 0 to 0x31 for the file type “Visual FoxPro, Autoincrement enabled” in the table (.dbf) header record structure. Visual FoxPro sets byte 18, bytes 19 to 22, and byte 23 in the field subrecords structure to the following values, respectively:
0x0C for the autoincrementing column.
Next value.
Step value.
The HXTT JDBC driver which I presume you’re using in Servoy supports this as far as I can tell.
However it’s also possible that the VFP side is using stored procedure code in the database container to do its own incrementing, in which case you’ve got a problem because the HXTT driver can’t execute this VFP code.
I’m using free tables, not linked to a database container (dbc), so it is not using stored procedure that are available only when tables (dbf) are stored in DBC.
The version of foxpro is 9.0
I repeat the problem is that the value that Servoy use is not correct.
I attach my simple test solution
I reapeat there no problem if only the servoy solution has accss to the tables…the problem is when the access to the tables is both from foxpro and servoy at the same time…
I’m using the HXTT JDBC driver…and i i’ve tried also the last demo version found on the producer site (40)…but i have the same problem…
It is a driver probem. It seems to be solved in future…
For the moment Servoy’s support suggest to set
Max Prepared Statements Idle = 0
in the database server.
In this configuration every SQL statements is immediately executed and not maintened in the cache. Performance can be low, but no errors…While waiting the new driver this can be a valid solution…
I am sorry, I thought I already responded but see I didn’t. I did some tests and was able to reproduce the same problem. I am not sure if waiting for a driver update that may never come is the way to go. Have you considered upscaling your database to SQL Server and retrofitting your application to use remote views? This has an impact but is certainly doable. I am afraid that otherways you will keep all kinds of inconsistencies in the area of locking and collission handling besides the performance penalty.
I have already received a driver update and it works fine…
For me upscaling our ERP to SQL is not doable. Too much work…(more than 1500 forms) and the application is stable and we have more than 400 installations…No, i need to develop a parallel application for the most used functions to access data with iPad or web…