create function string

Adds a function string to a function-string class. Replication Server uses function strings to generate instructions for data servers.

Syntax

create function string
        [replication_definition.]function[;function_string]
        for function_class [with overwrite]
        [scan 'input_template']
        [output
        {language 'lang_output_template' |
        rpc 'execute procedure
        [@param_name=]{constant |?variable!mod?}
                [, [@param_name=]
                        {constant |?variable!mod?}]...' |
        writetext [use primary log | with log | no log] | 
        none}]

Parameters

replication_definition – The name of the replication definition the function operates on. Use only for functions with replication definition scope.
Functions have either function-string-class scope or replication definition scope. Functions that direct transaction control have function-string class scope. User-defined functions, and functions that modify data, have replication definition scope.
function – The name of the function. Names for system functions must be provided as documented in Replication Server System Functions. Names for user-defined functions must match an existing user-defined function.
function_string – A function string name is required when customizing rs_get_textptr, rs_textptr_init, and rs_writetext functions, and optional for others. For rs_get_textptr, rs_textptr_init, and rs_writetext, a function string is needed for each text, unitext, or image column in the replication definition. The function string name supplied must be:
- The text, unitext, or image column name for the replication definition.
- Able to conform to the rules for identifiers.
- Unique in the scope of the function.
Replication Server also uses the function string name in the generation of error messages.
function_class – The function-string class the new function string belongs to.
with overwrite – If the function string already exists, this option drops and re-creates the function string, as though you used alter function string instead.
scan – Precedes an input template.
input_template – A character string, enclosed in single quote characters, that Replication Server scans to associate an rs_select or rs_select_with_lock function string with the where clause in a create subscription command. An input template string is written as a SQL select statement, with user-defined variables instead of the literal values in the subscription’s where clause.
output – Precedes an output template.
language – Tells Replication Server to submit the output template commands to the data server using the Client/Server Interfaces language interface.
lang_output_template – A character string, enclosed in single quote characters, that contains instructions for a data server. A language output template string may contain embedded variables, which Replication Server replaces with run-time values before it sends the string to the data server.

rpc – An output template that tells Replication Server to use the Client/Server Interfaces remote procedure call (RPC) interface. Replication Server interprets the string and constructs a remote procedure call to send to the data server.

These keywords and options appear in RPC output templates:

procedure – the name of the remote procedure to execute. It could be an Adaptive Server stored procedure, a procedure processed by an Open Server gateway RPC handler, or a registered procedure in an Open Server gateway. Refer to the Open Server Server-Library/C Reference Manual for information about processing RPCs in a gateway program.

@param_name – the name of an argument to the procedure, as defined by the procedure. If the @param_name = value form is used, parameters can be supplied in any order. If parameter names are omitted, parameter values must be supplied in the order defined in the remote procedure.

constant – a literal value with the datatype of the parameter it is assigned to.

?variable!mod? – variable is the placeholder for a run-time value. It can be a column name, the name of a system-defined variable, the name of a parameter in a user-defined function, or the name of a variable defined in an input template. The variable must refer to a value with the same datatype as the parameter it is assigned to. For a list of system-defined variables, see System-Defined Variables.

The mod portion of a variable name identifies the type of data the variable represents. The variable modifier is required for all variables and must be one of the following:

Function String Variable Modifiers
Modifier	Description
`new`, `new_raw`	A reference to the new value of a column in a row you are inserting or updating
`old`, `old_raw`	A reference to the existing value of a column in a row you are updating or deleting
`user`, `user_raw`	A reference to a variable that is defined in the input template of an rs_select or rs_select_with_lock function string
`sys`, `sys_raw`	A reference to a system-defined variable
`param`, `param_raw`	A reference to a function parameter
`text_status`	A reference to the `text_status` value for `text`, `unitext`, or `image` data. Possible values are: 0x000 – Text field contains NULL value, and the text pointer has not been initialized. 0x0002 – Text pointer is initialized. 0x0004 – Real text data will follow. 0x0008 – No text data will follow because the text data is not replicated. 0x0010 – The text data is not replicated but it contains NULL values.

Note: Function strings for user-defined functions may not use the new or old modifiers.

writetext – instructs Replication Server to use the Client-Library™ function ct_send_data to update a text, unitext, or image column value. This option applies only to the rs_writetext function.
The following options appear in writetext output templates to specify the logging behavior of the text, unitext, or image column in the replicate database:

use primary log – logs the data in the replicate database, if the logging option was specified in the primary database.

with log – logs the data in the replicate database transaction log.

no log – does not log the data in the replicate database transaction log.
none – applies to all functions, and provides the flexibility to identify which function-strings Replication Server can avoid executing on replicate databases:
- For rs_writetext functions – instructs Replication Server not to replicate a text, unitext, or image column value.
- For non-rs_writetext functions – instructs Replication Server not to execute commands on the replicate database.

Examples

Example 1 – Creates a function string for the rs_begin function:

create function string rs_begin
 for sqlserver2_function_class
 output language
 'begin transaction'

Example 2 – Creates a function string for the rs_commit function that contains two commands separated with a semicolon. The function string executes an Adaptive Server stored procedure that updates the rs_lastcommit system table and then commits the transaction:
```
create function string rs_commit
 for sqlserver2_function_class
 output language
 'execute sqlrs_update_lastcommit
   @origin = ?rs_origin!sys?,
   @origin_qid = ?rs_origin_qid!sys?,
   @secondary_qid = ?rs_secondary_qid!sys?;
   commit transaction'
```

Example 3 – Examples 3 and 4 create a replication definition for the titles table and an rs_insert function string for the sqlserver2_function_class. The function string inserts data into the titles_rs table instead of into the titles table in the replicate database:

create replication definition titles_rep
 with primary at LDS.pubs2
 (title_id varchar(6), title varchar(80),
  type char(12), pub_id char(4), advance money,
  total_sales int, notes varchar(200),
  pubdate datetime, contract bit, price money)
 primary key (title_id)
 searchable columns (price)

Example 4 – Examples 3 and 4 create a replication definition for the titles table and an rs_insert function string for the sqlserver2_function_class. The function string inserts data into the titles_rs table instead of into the titles table in the replicate database:

create function string titles_rep.rs_insert
 for sqlserver2_function_class
 output language
 'insert titles_rs values (?title_id!new?,
   ?title!new?, ?type!new?, ?pub_id!new?,
   ?advance!new?, ?total_sales!new?, ?notes!new?,
   ?pubdate!new?, ?contract!new?, ?price!new?)'

Example 5 – Examples 5 and 6 create a user-defined function update_titles and a corresponding function string for the sqlserver2_function_class. The function string executes an Adaptive Server stored procedure named update_titles:
```
create function titles_rep.update_titles
 (@title_id varchar(6), title varchar(80),
  @price money)
```
Example 6 – Examples 5 and 6 create a user-defined function update_titles and a corresponding function string for the sqlserver2_function_class. The function string executes an Adaptive Server stored procedure named update_titles:
```
create function string titles_rep.update_titles
 for sqlserver2_function_class
 output rpc
 'execute update_titles
   @title_id = ?title_id!param?,
   @title = ?title!param?,
   @price = ?price!param?'
```
Example 7 – The rs_select function string in example 7 is used to materialize subscriptions that request rows with a specified value in the title_id column. Similar to example 8, the input templates given by the scan clauses differentiate the two function strings:
```
create function string
   titles_rep.rs_select;title_id_select
 for sqlserver2_function_class
 scan 'select * from titles
   where title_id = ?title_id!user?'
 output language
 'select * from titles
   where title_id = ?title_id!user?'
```

Example 8 – The rs_select function string in example 8 is an example of an RPC function string. It is used to materialize subscriptions that request rows where the value of the price column falls within a given range:

create function string
   titles_rep.rs_select;price_range_select
 for sqlserver2_function_class
 scan 'select * from titles
   where price > ?price_min!user?
   and price < ?price_max!user?'
 output rpc
   'execute titles_price_select
   ?price_min!user?, ?price_max!user?'

Usage

Use create function string to add a function string to a function-string class. Function strings contain the database-specific instructions needed by Replication Server to convert a function to a command for a database.
For an overview of functions, function strings, and function-string classes, see the Replication Server Administration Guide Volume 2.
Create or alter function strings for functions with class scope at the primary site for the function-string class. See create function string class for more information about the primary site for a function-string class.
Create or alter function strings for functions with replication definition scope, including user-defined functions, at the site where the replication definition was created. Each replication definition has its own set of function strings.
Replication Server distributes the new function string to qualifying sites through the replication system. The changes do not appear immediately at all such sites because of normal replication system lag time.
Some function strings are generated dynamically; they are not stored in the RSSD.

Function Strings and Function-String Classes

For each of the system-provided function-string classes in which a function will be used, and for each derived class that inherits from these classes, Replication Server generates a default function string for the function. This is true for both system functions and user-defined functions. (Default function strings for the rs_dumpdb and rs_dumptran functions are not provided. You only need to create them if you are using coordinated dumps.
Customize the function string in rs_sqlserver_function_class using alter function string. Customize the function string in user-created function-string classes using create function string.
For each user-created base function-string class in which the function will be used, and for each derived class in which you want to override the inherited function string, you must create a function string, using create function string.
Omitting the output clause instructs Replication Server to generate a function string in the same way that it generates function strings for the rs_sqlserver_function_class or rs_default_function_class function-string classes.
The default function string for a user-defined function is an invocation of a stored procedure where the name is the function name and the parameters are the function parameters. The stored procedure is executed as a language command, not as an RPC.

Function Strings and Replicate Minimal Columns

If you have specified replicate minimal columns for a replication definition, you cannot normally create non-default function strings for the rs_update, rs_delete, rs_get_textptr, rs_textptr_init, or rs_datarow_for_writetext system functions.

However, you can create non-default function strings for the rs_update and rs_delete functions if you use the rs_default_fs system variable within the function string. This variable represents the default function-string behavior. You can add additional commands to extend the function-string behavior.
See create replication definiton for more information about the replicate minimal columns option.

Input and Output Templates

Depending on the function, function strings can have input and output templates. Replication Servers substitute variable values into the templates and pass the result to data servers for processing.
Input and output templates have the following requirements:
- They are limited to 64K. The result of substituting run-time values for embedded variables in function-string input or templates must not exceed 64K.
- Input templates and language or RPC output templates are delimited with two single quote characters (').
- Variable names in input templates and output templates are delimited with question marks (?).
- A variable name and its modifier are separated with an exclamation mark (!).
When creating function strings:
- Use two consecutive single quote characters ('') to represent one literal single quote character within or enclosing data of character or date/ time datatypes, as shown for “Berkeley” in the following character string:
```
'insert authors
(city, au_id, au_lname, au_fname)
values (''Berkeley'', ?au_id!new?,
?au_lname!new?,
?au_fname!new?)'
```
- Use two consecutive question marks (??) to represent one single question mark within data of character datatypes.
- Use two consecutive semi-colons (;;) to represent one single semicolon within data of character datatypes.

Input Templates

Input templates are used only with the rs_select and rs_select_with_lock functions, which are used during non-bulk subscription materialization and with purge subscription dematerialization. Replication Server matches the subscription’s where clause with an input template to find the function string to use.
Input templates have the following requirements:
- They contain only user-defined variables, whose values come from the constants in the where clause. The user-defined variables can also be referenced in the function string’s output template.
- If the input_template is omitted, it can match any select command. This allows you to create a default function string that is executed when no other function string in the function-string class has an input_template matching the select command.

Output Templates

Output templates determine the format of the command sent to a replicate data server. Most output templates can use one of these formats: language RPC, or none. An output template for an rs_writetext function string can use the RPC format or the additional formats writetext or none. For a description of these formats, see the Replication Server Administration Guide Volume 2.
When Replication Server maps function string output templates to data server commands, it formats the variables using the format expected by Adaptive Server. It modifies datatypes for modifiers that do not end in _raw (the modifiers that are normally used), as follows:
- Adds an extra single quote character to single quote characters appearing in character and date/time values to escape the special meaning of the single quote character.
- Adds single quote characters around character and date/time values, if they are missing.
- Adds the appropriate monetary symbol (the dollar sign in U.S. English) to values of money datatypes.
- Adds the “0x” prefix to values of binary datatypes.
- Adds a combination of a backslash (\) and newline character between existing instances of a backslash and newline character in character values. Adaptive Server treats a backslash followed by a newline as a continuation character, and therefore deletes the added pair of characters, leaving the original characters intact.
  Replication Server does not modify datatypes in these ways for modifiers that end in _raw.
  
  Formatting for Function String Variables table summarizes how Replication Server formats each datatype for the modifiers that do not end in _raw:

Formatting for Function String Variables
Datatype	Formatting of Literals
`bigint`, `int`, `smallint`, `tinyint`, `rs_address`	Integer number
`unsigned bigint`, `unsigned int`, `unsigned smallint`, `unsigned tinyint`	Unsigned Integer number
`decimal`, `numeric`, `identity`	Exact decimal number
`float`, `real`	Decimal number
`char`, `varchar`	Enclosed in single quote character Adds single quote character to any instance of a single quote character Pads instances of backslash + newline characters
`unichar`, `univarchar`	Unicode
`money`, `smallmoney`	Adds the appropriate money symbol (dollar sign for U.S. English)
`date`, `time`, `datetime`, `smalldatetime`	Enclosed in single quote characters Adds single quote character to any instance of a single quote character
`binary`, `timestamp`, `varbinary`	Prefixed with 0x
`bit`	1 or 0

Output templates have the following requirements:
- The result of substituting run-time values for embedded variables in function-string output templates must not exceed 64K.
- You can put several commands in a language function-string output template, separating them with semicolons (;). If the database is configured to allow command batches, which is the default, Replication Server replaces the semicolons with that connection’s DSI command separator character before sending the function string in a single batch to the data server. The separator character is defined in the dsi_cmd_separator option of the alter connection command.
  To represent a semicolon that should not be interpreted as a command separator, use two consecutive semicolons (;;).
  
  If the connection to the database is not configured to allow batches, Replication Server sends the commands in the function string to the data server one at a time. To enable or disable batching for a database, use alter connection.

Replication Server System-Defined Variables table list the system-defined variables that can be used in function-string output templates. Use the sys or sys_raw modifier for these variables.

Replication Server System-Defined Variables
System Variable	Datatype	Description
`rs_default_fs`	`text`	The default generated function-string text for the function
`rs_deliver_as_name`	`varchar`(200)	For execution of a replicated function, name of the procedure to be invoked at the destination
`rs_destination_db`	`varchar`(30)	Name of the database where a transaction was sent
`rs_destination_ds`	`varchar`(30)	Name of the data server where a transaction was sent
`rs_destination_ldb`	`varchar`(30)	Name of the logical database where a transaction was sent
`rs_destination_lds`	`varchar`(30)	Name of the logical data server where a transaction was sent
`rs_destination_ptype`	`char`(1)	Physical connection type (“A” for active or “S” for standby) for the database where a transaction was sent
`rs_destination_user`	`varchar`(30)	User who will execute the transaction at the destination
`rs_dump_dbname`	`varchar`(30)	Name of the database where a database or transaction dump originated
`rs_dump_label`	`varchar`(30)	Label information for a database or transaction dump. For Adaptive Server, this variable holds a `datetime` value that is the time the dump originated.
`rs_dump_status`	`int`(4)	Dump status indicator: 0 – indicates that the dump transaction command does not contain the parameter with standby_access 1 – indicates that the dump transaction command contains the parameter with standby_access
`rs_dump_timestamp`	`varbinary`(16)	Timestamp of a database or transaction dump
`rs_lorigin`	`int`(4)	ID of the originating logical database for a transaction
`rs_isolation_level`	`varchar`(30)	Transaction isolation level of a database connection.
`rs_origin`	`int`(4)	ID of the originating database for a transaction
`rs_origin_begin_time`	`datetime`	The time that a command was applied at the origin Note: If you execute select getdate() while ASE is still processing user database recovery, the returned value of select getdate() may be different from the value of `rs_origin_begin_time`.
`rs_origin_commit_time`	`datetime`	The time that a transaction was committed at the origin Note: If you execute select getdate() while ASE is still processing user database recovery, the returned value of select getdate() may be different from the value of `rs_origin_begin_time`.
`rs_origin_db`	`varchar`(30)	Name of the origin database
`rs_origin_ds`	`varchar`(30)	Name of the origin data server
`rs_origin_ldb`	`varchar`(30)	Name of the logical database for a warm standby application
`rs_origin_lds`	`varchar`(30)	Name of the logical data server for a warm standby application
`rs_origin_qid`	`varbinary`(36)	Origin queue ID of the first command in a transaction
`rs_origin_user`	`varchar`(30)	User who executed the transaction at the origin
`rs_origin_xact_id`	`binary`(120)	The system-assigned unique ID of a transaction
`rs_origin_xact_name`	`varchar`(30)	User-assigned name of the transaction at origin
`rs_repl_objowner`	`varchar`	Owner of the replicate object
`rs_secondary_qid`	`varbinary`(36)	Queue ID of a transaction in a subscription materialization or dematerialization queue
`rs_last_text_chunk`	`int`(4)	If the value is 0, this is not the last chunk of `text` data. If the value is 1, this is the last chunk of `text` data.
`rs_writetext_log`	`int`(4)	If the value is 0, `rs_writetext` has not finished logging `text`, `unitext`, and `image` data at the primary database transaction log. If the value is 1, `rs_writetext` has finished logging `text`, `unitext`, and `image` data at the primary database transaction log.

If you are not using parallel DSI to process large transactions before their commit has been read from the DSI queue, the value of the rs_origin_commit_time system variable contains the time when the last transaction in the transaction group committed at the primary site.

If you are using parallel DSI to process large transactions before their commit has been read from the DSI queue, when the DSI threads start processing one of these transactions, the value of the rs_origin_commit_time system variable is set to the value of the rs_origin_begin_time system variable.

When the commit statement for the transaction is read, the value of rs_origin_commit_time is set to the actual commit time. Therefore, when the configuration parameter dsi_num_large_xact_threads is set to a value greater than zero, the value for rs_origin_commit_time is not reliable for any system function other than rs_commit.

System Variables and NULL Values

The following system variables may have NULL values:
- rs_origin_ds
- rs_origin_db
- rs_origin_user
- rs_origin_xact_name
- rs_destination_db
- rs_destination_user
- rs_dump_dbname
- rs_dump_label
When a system variable has no value, Replication Server maps the word “NULL” into function-string templates. This may cause syntax errors in some generated statements. For example, the following command would be generated if rs_origin_xact_name has a null value:
```
begin transaction NULL
```
To prevent this error, create a function string with an output template like the following:
```
'begin transaction t_?rs_origin_xact_name!sys_raw?'
```
If the rs_origin_xact_name system variable is null, the transaction name will be “t_NULL”.

Replacing Function Strings

To replace a function string, use alter function string or create function string with overwrite. Either approach executes drop function string and create function string in a single transaction, preventing errors that could result from temporarily missing function strings.

Permissions

create function string requires “create object” permission.