Harry Catharell:
Servoy Developer
Version 3.0.1-build 372
Java version 1.5.0_06-64 (Mac OS X)
Looking for a straightforward way of storing a foundset as a record in a table and then using that record to be able to restore the foundset
Can do this using getFoundSetDataProviderAsArray() and then store this array in a media field.
This works during a session BUT fails after reloading the solution even though the record of the foundset is still in the table.
It will not load the records using the array in the media field and I cannot fathom why not
Anyone care to share a methodology for doing this
You can’t store arrays in media field (I aked the Dev Team some time ago and they told me it would need object serialization for that).
The solution is to convert the array in a string and store it in a column.
When you’ll need to load the data, convert the string to an array and use it in your loadRecords step.
That’s what I meant. You either store the PKs or the query. The difference is, however, that the query may lead to different results because in the meantime there might have been data added, changed or deleted. So storing the query is not the same as storing the pks. It depends on what you want to achieve. If you need to produce the exact same records, you need to store the pks. If you need to produce the records that have the same criteria, you need to store the query.
Patrick & Harjo - Using the storage of the SQL parameters was considered but rejected in this case for exactly the reason which Patrick cites - that the results of the query may change as you re-run it and we want to store the exact foundset as it was at that point in time.
Ric - It sounds as though this is the answer that I was looking for but do not know how to convert the array to text and back again
Any chance that you could identify what functions I need to use to do this and point me a bit closer to the finsih line ?
var vMyIds = databaseManager.getFoundSetDataProviderAsArray(foundset, 'pkColumn'); // Create an Array of IDs
var vMyIdsString = vMyIds.join(', '); // Convert to a comma seperated String
You can then store that vMyIdsString in a DB field. Of course you have to make sure that the data fits!
To restore you can do
var vMyIdsString = myIdColumn; // Get the comma seperated string
var vMyIds = vMyIdsString.split(', '); // Convert it to an array
var vDataset = databaseManager.convertToDataSet(test); // Convert the Array to a dataset
controller.loadRecords(vDataset); // Load the dataset
My opinion is that it is “nicer” to create a table that looks like this
pk
userId
tableName
tablePk
In such a table you can store per user and table any Ids and restore them by joining into it.
Ric - It sounds as though this is the answer that I was looking for but do not know how to convert the array to text and back again
Any chance that you could identify what functions I need to use to do this and point me a bit closer to the finsih line ?
See if this code points you close enough :
//to store the params array in a text field
paramsarray.join(",")
//to restore the array from the text field
var params = forms.formname.your_param_text_field.split(",")
I really would consider strongly though what Patrick and Riccardino are suggesting, namely storing the IDs in a table, one ID per row with whatever related information is useful (search/foundset name, Create User, Allowed User(s), etc.). It might sound as though that is more work (either for you or the database!) but really I don’t think it is and it offers a LOT more flexibility in terms of size and features.
To start with size, if a foundset is larger than say a couple of hundred you can run into trouble depending on the database. With an array, Servoy will actually be converting that into an ‘IN’ statement and the number of PKs that you can have in such a statement varies from database to database, how much memory you have allocated in the database (each vendor calls this something different), etc. With the related table on the other hand Servoy is just sending a simple query (select A.pk from A, B where A.pk = B.pk) and that is by far the fastest and most efficient way to do it.
In terms of work for you I’d be happy to share any thoughts, work I’ve done. I don’t have it in a module - although I think I’m moving that way now - but maybe Riccardino would share his with you. Actually that would make a very nice submission to Servoy Magazine if you felt like it Riccardino! I think that is something that more and more people come up against (saving searches and allowing other users, groups, etc. to access them) and is pretty generic. Once posted I bet others would also have their own comments about other features that they incorporate for their particular environment. I actually do mine slightly differently: one table with two columns of a search_id and the actual target table id. Then a different table with the search_id, search_name, when done, owner etc. And finally another table detailing the users who have access to that foundset. In our case everything relates back to one table (patient) so every foundset has a unique ID from that table thus we don’t have to have another column specifying the table.
We actually do have a separate table storing a campaign/list which allows users to collect together reusable groups of people for a variety of purposes.
It is in the form of:
campaign -< campaign_person >- person
The ‘campaign_person’ table essentially stores the ‘fk_campaign’ and ‘fk_person’ and acts as a join table between the two key entities.
Thus a campaign can have many people and people can belong to many campaigns.
What we wanted to achieve using an array was a short term storage of users’ general day-to-day queries without having to go through the ‘campaign’ table.
The idea being to just store the pk’s in an array and allow the user to replay their last, say, 5 previous queries - these queries would only be stored if they were, for example, <= 500 rows.
We are just exploring using this quick set up as a sort of scratchpad for finds.
More than happy to take advice here and if it is an inefficient way of accomplishing what we want then more than willing to rethink the process.
I will initiate this solution and then at least give it a test to assess performance and limitations in the context of how we would have it used and then come crawling back once it fails and appeal for mercy
john.allen:
In terms of work for you I’d be happy to share any thoughts, work I’ve done. I don’t have it in a module - although I think I’m moving that way now - but maybe Riccardino would share his with you. Actually that would make a very nice submission to Servoy Magazine if you felt like it Riccardino!
No problems, but I only set up - very quickly, indeed - a basic module that receives the SQL e SQL params (+ other useful infos) in a record.
Then, when the user needs them, those data are moved to a couple of global fields and used to restore the search.
I’ll prepare it and ask David to add it to my article on this topic, on Servoy Magazine.
I have been working with Harry on this problem and think your phrase ‘… those data are moved to a couple of global fields …’ might be the missing key.
We have been trying to use the stored data in a var array as part of the Method - maybe Globals work differently in this context. Had similar problem when trying to extend functionality of your excellent Tip on Servoy Magazine couple of months ago.
I have been working with Harry on this problem and think your phrase ‘… those data are moved to a couple of global fields …’ might be the missing key.
We have been trying to use the stored data in a var array as part of the Method - maybe Globals work differently in this context. Had similar problem when trying to extend functionality of your excellent Tip on Servoy Magazine couple of months ago.
Yes: it has to do with object serialization. You can store an array on a media global field, but you cannot do the same on an actual media field. That’s why, in the suggested method, Patrick and me talked about “join” and “split” functions.
In my case, since I put all the stuff in a module, the fastest way to make the SQL code and the params available to a solution using the module itself is to park the contents of those elements in two global fields.