I’m developing a web-client for a Foxpro application using dbf’s as my database. I’ve added a sequence number column ‘unq_id’ to my tables and made that the primary key in Servoy and I’ve made this column a servoy_seq.
Clearly since I need both the web-client and my existing Foxpro app to create records, I need to maintain the sequence number from both sides. What is the best method of doing this? Should I stick with the Servoy sequence or use a DB seqeunce? In either case, how do I ensure I have the next unique sequence number?
This leads onto a 2nd question, which is that I need to validate that I have a unique value for a record - in my app I have a list of extension numbers for a phone system and need to be sure each only exists once in my table. Having had bit of help with find(), I’ve used the following but again I’m not sure if this is the best method. It seems that without the 2nd find()/search() I can reduce the records in the current table view, which I do not want to do.
var zfind=fvExtnFrom.toString();
controller.find();
extn=zfind;
var nResults = controller.search();
controller.find();
controller.search(true);
From Foxpro I’ve no problem but I seem to spend ages looking for stuff either in the documentation or here in the forum.
Kind regards, Tony
If you use a db sequence, Servoy will get the next value from it when a new record is created.
The db sequence will increase its next value, so that should never clash.
If you use a db sequence, Servoy will get the next value from it when a new record is created <<
This approach works with “real active” databases (which have their own database-service running), but the FoxPro database is a passive model, i.e. those keys are typically generated by a FoxPro-based process (either the client itself or the ODBC / OLEDB driver) when accessing the database. Since the JDBC DBF-driver doesn’t run the FoxPro code in the StoredProcedures or Defaultvalue-Assignments, this will not work.
It could work, if the DBF-driver would support the VFP datatype “INTEGER AUTOINC”, because that fieldtype is the normal thing we use in VFPs world for sequence-values. Since the values for incrementing as well as the next assigned number are stored in the DBF header, that information would be available to the JDBC-driver. Will do some tests…
I just did a simple test with a fresh created VFP-DBF, which had a ID-field with type “INTEGER AUTOINC”, and guess what: this is already working! In Servoy I only selected “row_ident” in that field, as well as set Sequence Type to “db identity”.
In that mode you can append new records from Servoy and the JDBC-driver will happily assign new IDs in the AutoInc column. It even respects the stepsize for incrementing.
You have to be careful if you are doing new records from both sides: VFP is seeing the updates (and the increased reccount) immediately, but in Servoy you need to do a
databaseManager.refreshRecordFromDatabase(foundset, -1);
before issuing the
foundset.newRecord();
so that it picks up the current recordcount.
For the find, I would create a separate foundset from the table using databaseManager.getFoundSet() and do the find against it instead of the controller.
My .dbf are from an older version of Foxpro, v5 to be exact, and this did not have the integer autoinc field type. My id field is a straight integer.
I’ve tried a couple of brief tests, with db seq and db identity to no avail, will try again tomorrow but wondered if you or anyone else had any ideas on how I get unique id’s when working along side older Foxpro apps.
I guess I could but I’m not sure if I could do it in an efficient manner.
In Foxpro, I have an index on the id field, so I sort the records by the id, jump to the last record, get the number, increment it, locate the new record and update it with the id.
I’ve recently come across a method for locating the last record in Servoy, which I had struggled with, and it may well work for me in most areas, but three of my data tables are likely to grow quite large .ie. millions of records, and I gather that this;
that last way is not only very inefficient but also not really save (just as select max(pk) from table)
Because all the time you do that and then add one and then insert it into the database, what happens if 2 users do that +/- at the same time?
Best is to use database sequences or database identities for that if you want the database to control it a bit more (and maybe 3th party software that also uses that db) or just servoy sequences.
The only way I can see of doing this sucessfully is to let my Foxpro app use one range of id’s and Servoy another, my largest databases cannot exceed 30Million records anyway because the .dbf files can’t exceed 2Gb. So if I start a Servoy sequence at 30M, it’ll work fine.
Is there a way that I can set the Servoy sequence through script? I’m thinking the first time the Servoy app is started or the first time it adds a record, I will need to set the starting point. I don’t really want to have to manually set-up the sequence numbers each time I install the app. at a site.