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> |
        [<owner>.] table | 
        <stored_procedure>} .<function>[;<function_string>]
        for { [<function_class>] function_class |
        [<database>] <data_server.database>}
        [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 function-string-class scope, replication definition scope, or target scope.

Functions that direct transaction control have function-string-class scope. User-defined functions, and functions that modify data and created against replication definitions have replication definition scope.

Function strings created against standby or replicate tables or stored procedures are target scope function strings.

[owner.]table

Specifies the target table and the table owner of the function string.

stored procedure

Specifies the target store procedure of the function string

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

Specifies the function class the function string is associated with for replication definition-scope function strings.

data_server.database

Specifies the standby or replicate database where you want to create a target-scope function string for target table or stored procedures.

See Create Function Strings in the Replication Server Administration Guide Volume 2.

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. You can use the with overwrite option only with create function string.

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:

Table 1: 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

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

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?'

Example 9

Creates a target-scope function string for the upd_datetime stored procedure for database NY_DS.rdb1:

create function string upd_datetime.upd_datetime
     for database NY_DS.rdb1
     with overwrite
          output language
          'update datetime set
          row_num = ?row_num!param?,
          datecol = ?datecol!param?,
          timecol = ?timecol!param?,
          ndatecol = ?ndatecol!param?,
          ntimecol = ?ntimecol!param?,
          comment = ?comment!param?
          where
          row_num = ?row_num!param?'

Example 10

Creates a target-scope function string for the dbo.datetime table for NY_DS.rdb1:

create function string dbo.datetime.rs_insert
     for database NY_DS.rdb1
     with overwrite
     output language
     'insert datetime values (
             ?row_num!new? ,
             ?datecol!new? ,
             ?timecol!new? ,
             ?ndatecol!new? ,
             ?ntimecol!new? ,
             ?comment!new?)
       update fn_monitor set insert_count = insert_count + 1'

Example 11

Creates the rs_writetext customized function string for the dbo.tbl1.unitext_fd1 column:

create function string dbo.tbl1.rs_writetext; unitext_fld1 for NY_DS.rdb1
         output RPC
        'exec update_repl_unitext
                @p_key       = ?p_key!new?,
                @unitext_fld       = ?unitext_fld1!new?,
                @last_chunk         = ?rs_last_text_chunk!sys?'

Example 12

Creates a target-scope function string for the dbo.tbl1 table:

create function string dbo.tbl1.rs_datarow_for_writetext
for NY_DS.rdb1
        output RPC
        'exec update_txtimg_stat
                @p_key       = ?p_key!new?,
                @txtfld_stat = ?unitext_fld1!text_status?'

Example 13

Creates the rs_insert customized function string for the dbo.authors table at the rdb1 target database in the NY_DS data server:

create function string dbo.authors.rs_insert
     for database NY_DS.rdb1
     output language
     'insert authors values (
           ?au_id!new? ,
           ?au_lname!new? ,
           ?au_fname!new? ,
           ?phone!new? , 
           ?address!new? , 
           ?city!new? , 
           ?state!new? ,
           "00000" , 
           ?contract!new?)
      update fn_monitor set insert_count = insert_count + 1'

Example 14

Creates a customized function string for the upd_bits stored procedure at the rdb1 target database in the NY_DS data server. Functions for a stored procedure have the same name as the stored procedure:

create function string upd_bits.upd_bits
     for database NY_DS.rdb1
     with overwrite
     output language
     'exec upd_bits
           @firstbit = ?firstbit!param?,
           @secondbit = ?secondbit!param?, 
     @commit = ?comment!param?'

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.
Execute create function string for target-scope function strings at the Replication Server that controls the target database, which is either the standby or replicate database.
You can use the with overwrite option only with create function string.
A replication definition-scope function string is associated with a function class, while a target-scope function string is associated with a target database.
When a target table does not have owner information, and if you do not specify the function class and database option in the function string in the command, Replication Server can only know whether a function string is for a replication definition or a table by checking if the format of the string after the for keyword is for a function class or a database. For a:
- Replication definition-scope function string – such as rs_sqlserver_function_class, the string format after the for keyword does not have a database name
- Target-scope function string – such as NY_DS.rdb1, the string format after the for keyword contains the names of a data server and database
You can only create target scope function strings against a standby or replicate database, not against a connection that you may have configured for multiple replication paths.
In a warm standby environment, the database that is affected is the physical database. If you want to define target-scope function string for a logical database, you must issue the function-string command against both the active and standby databases.
The function names are the same as the stored procedure names for target-scope function strings for standby or replicate stored procedures.
For target-scope function strings for standby or replicate tables, the valid functions are: rs_insert, rs_update, rs_delete, rs_truncate, rs_writetext, rs_datarow_for_writetext, rs_textptr_init, and rs_get_textptr.
Replication Server only uses target-scope function strings when there is no replication definition for the object, or when all the replication definitions for the object are not used by the object.
See "Warm Standby and Multisite Availability Environments" in the Replication Server Administration Guide Volume 1
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.

Note ExpressConnect for Oracle does not support the use of custom function strings for text and image processing.

See "ExpressConnect Settings" and "Function Strings, Error Classes, and User Defined Datatypes" in the Replication Server Heterogeneous Replication Guide.

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.
- If you are using quoted identifiers with a custom function string that includes a quoted constant, create subscription without a quoted constant or without materialization clause. Otherwise, during subscription materialization the quoted constant causes a query failure. The replicate data server identifies the quoted constant as a column instead of a constant.

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>:

Table 2: 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.

Table 3: 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.