Is it possible to implement MobiLink's upload_insert as a SQL function

[Former Member]

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)

[JasonHinsperger · Accepted solution]

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

[Former Member · Accepted solution]

Ah. I see -- so my original idea might will work

As some of the event scripts can be implemented as function itself (e.g. resolve_conflict​) is there any hint on documentation were I can figure that out? Some kind of a flag maybe?

However, thanks for the help so far

[JasonHinsperger · Accepted solution]

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