on 07-24-2014 1:50 PM
Is it possible, to implement functionality of upload_insert as a function? From documentation, this is an example how to add an functionality to that event:
CALL ml_add_table_script(
'ver1',
'Customer',
'upload_insert',
'INSERT INTO Customer(
cust_id,
name,
rep_id,
active )
VALUES (
{ml r.cust_id},
{ml r.name},
{ml r.rep_id},
1 )' );
But I'm wondering whether I've the chance to put also something like this into it
-- ....
declare @my_name varchar(50)
set @my_name = 'Teddy Test'
INSERT INTO Customer(
cust_id, name, rep_id, active )
VALUES ( {ml r.cust_id}, @name, {ml r.rep_id}, 1 )' )
-- ....
(this is just a random example)
I was thinking about a way defining this as a procedure and adding the call of the function with ml_add_table_script. But wondering, whether this is the best way on doing this.
Background: I've got about 500 tables for which I like to add some magic to recognize constrain violations during upload of new rows e.g. caused by inserting a new row on remote side and changing another line on consolidated database ending upon constraint violation when doing the merge)
For most consolidated databases, you can call a stored procedure as your script entry. Inside that procedure you can basically do whatever you want, except execute commit/rollback or any statement that causes a commit/rollback.
eg.
CALL ml_add_table_script(
'ver1',
'Customer',
'upload_insert',
'call myproc( {ml r.cust_id}, {ml r.name}, {ml r.rep_id}, 1 )'
);
Hope this helps,
--Jason
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is no difference to flag. All of the script options are basically the same - you can execute a statement. Whether that statement is a procedure call or some other SQL statement doesn't really matter, its just that in some contexts it makes more sense to call a procedure to do a bunch of things and in others, it makes sense (at least in the simple case), and may be slightly faster to execute a single statement directly (eg. insert) rather than call a procedure that then executes that single statement.
hth,
--Jason
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.