Hi
I’m still very new to this so please bear with me ! I have a situation where I wish to add records to a table based on the existence of a value in another table.
In SQL terms it is this
INSERT INTO attendance (memberID)
SELECT memberID
FROM members
WHERE venue = :aparameter
My question(s) is/are do I define this SQL somewhere (where? and how do I set the ‘parameter’)
or
do I cycle through the members table finding matching values and append records manually to attendance as they are found ?
(I wouldn’t now how to do this either !)
You can use the INSERT SELECT statement in Servoy using the rawSQL plugin like so:
var sQuery = INSERT INTO attendance (memberID) SELECT memberID FROM members WHERE venue = ?",
sArg = 'some argument';
if ( !plugins.rawSQL.executeSQL( 'yourServerConnectionName', 'attendance', sQuery, [sArg]) ) {
// something went wrong
} else {
// need to flush any cached data of this table or else your and other clients won't see it.
plugins.rawSQL.flushAllClientsCache('yourServerConnectionName', 'attendance');
}
Or you can query the data via databaseManager.getDataSetByQuery() and loop through the resulting dataset and insert them via a Servoy form (that is linked to the attendance table.
Using a form for the insert will ensure that any other connected clients see the new data without having to resort to flushing their cache.
There are other options to do what you want but I think these 2 are the most efficient ones.
The table I wish to insert into has a primary key (not Null) that is a servoy maintained sequence so I guess the single SQL query route is a non starter ?
harryg:
The table I wish to insert into has a primary key (not Null) that is a servoy maintained sequence so I guess the single SQL query route is a non starter ?
That is correct. Unless you start using database managed sequences.
Otherwise use the dataset-to-form approuch I described.
I switched the database to maintaing the sequence so I could still use a single SQl statement to get where I wanted to be and that works fine.
If this were to be a multi-tenant situation is there a method to flush the caches of only the appropriate clients ?
From the little I know (always dangerous) I would have thought that, as a general principle, getting the database server to do the work directly rather than individually requesting multiple read and writes is more effiecient - particularly over the web with many active clients ?
harryg:
If this were to be a multi-tenant situation is there a method to flush the caches of only the appropriate clients ?
I don’t think you can at this time. Right now you can flush the cache to 1 specific table for all clients.
The question is how many times will you flush this cache ? Once or twice a day, the impact would be minimal.
harryg:
From the little I know (always dangerous) I would have thought that, as a general principle, getting the database server to do the work directly rather than individually requesting multiple read and writes is more effiecient - particularly over the web with many active clients ?
That is correct. And there are of course always exceptions to this rule.
For example if you need to flush the clients cache every minute or so then this might have a greater impact then hitting the database server with a few more queries and have the benefit of the data-broadcasting to the appropriate clients.
I can get my client updated without flushing the caches.
but a call to
relookup()
does not seem to, I just wondered what the difference is ?
In the context of this app each client will always be looking at a different set of data (famous last words !) so refreshing the local realtion should do.