You can also define your own parameters. These are especially useful for RDBMSs that don't allow user-defined variables.

User-defined parameters are defined (and set to null) when first referenced. They must start with ui and a period (ui.). A user-defined parameter lasts for one synchronization—it is set to null at the start of every synchronization. User-defined parameters are in/out.

A typical use of user-defined parameters is to access state information without having to store it in a table (requiring potentially complex joins).

Example

For example, assume you create a stored procedure called MyCustomProc that sets a variable called var1 to custom_value:

CREATE PROCEDURE MyCustomProc(
  IN username (VARCHAR 128), INOUT var1 (VARCHAR 128)
)
begin
 SET var1 = 'custom_value';
end

The following begin_connection script defines the user-defined parameter var1 and sets the value to custom_value:

CALL ml_add_connection_script ( 
  'version1', 
  'begin_synchronization', 
  '{call MyCustomProc( {ml s.username}, {ml ui.var1} )}' );

The following begin_upload script references var1, whose value is custom_value:

CALL ml_add_connection_script ( 
  'version1', 
  'begin_upload', 
  'update SomeTable set some_column = 123 where some_other_column = {ml ui.var1}' );

Assume you have another stored procedure called MyPFDProc that defines its first parameter to in/out. The following prepare_for_download script changes the value of var1 to pfd_value:

CALL ml_add_connection_script ( 
  'version1', 
  'prepare_for_download', 
  '{call MyPFDProc( {ml ui.var1} )}' );

The following begin_download script references var1, whose value is now pfd_value:

CALL ml_add_connection_script ( 
  'version1', 
  'begin_download', 
  'insert into SomeTable values( {ml s.username}, {ml ui.var1} )' );