Hi Bob,
in order to do this, the best way would be to use a PostgreSQL sequence
https://www.postgresql.org/docs/8.1/sta ... uence.htmlThis way PostgreSQL will handle the unique values for you, and if you like it's possible to use these sequences with regular Servoy foundsets as well.
PostgreSQL sequences have a couple of functions that can be used as documented here:
https://www.postgresql.org/docs/9.1/sta ... uence.htmlLet's say you've created a sequence with name 'pk_seq_diagnosis_procedures'
The insert statement you've given would be:
- Code: Select all
INSERT INTO diagnosis_procedures
(diagnosis_procedure_id, flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)
SELECT nextval('pk_seq_diagnosis_procedures'), flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14733
Although all of this is perfectly possible using the rawSQL plugin from within Servoy, please be aware that any Servoy client running at the time of executing this statement, won't receive any broadcast event of the insert and therefor doesn't know about the existence of the new record(s). (There's some exception when foundset based on the table has not been cached yet, but generally you shouldn't count on that)
This could be resolved by using the plugin.rawSQL.flushClientCache() function, but it's an expensive operation not worth when only inserting single rows.
Please also note in case you use a servoy-sequence on the PK of the diagnosis_procedures table, this won't be updated which will result in a violation of unique value whenever you insert records 'the Servoy way'
I recently had to do the exact same thing in an application, but handling 10.000 records at a time which took Servoy almost 3 minutes to insert and could be done using rawSQL within 1 second!
I used a database sequence for this and have Servoy use this as well, so there will never be a violation of unique value.
The Servoy way would be something like having to foundsets, 1 source, 1 destination.
On the source foundset, perform a find/search operation.
Iterate over the records you've found and create records in the destination foundset, while setting the dataproviders with the correct values.
Having written all of the above:
I'd say keep things simple and use the power of Servoy to accomplish what you want, unless you really need to handle high volume data.
Although doing it the Servoy way will stall your development at first, but you will gain speed in building your apps/code once your Servoy skills improve.
Also using rawSQL and mix this with the way Servoy works and behaves is something you shouldn't start with.
Only do this when you have experience in Servoy and fully understand how Servoy handles foundsets, pk, sequences, etc., etc.
Hope this helps.
