Home

	Expand Search

Access Service User's Guide DirectConnect Anywhere

Chapter 11 Using Destination-Template Transfer

Chapter 11

Using Destination-Template Transfer

This chapter describes the destination-template transfer process. it covers the following topics:

• General description

• Syntax

• Datatype qualifiers

• Special Date and Time qualifiers

• Destination-Template processing

• Destination-Template Transfer errors

• Creating a Transfer RPC

General description

The destination-template transfer statement allows you to perform operations that do more than transfer data. You can feed a result set into a template and force the template to perform operations such as the following:

• Create a full-image copy (insert)

• Create an incremental copy (insert)

• Modify column values (update or delete)

• Perform structural modifications (create or alter)

• Change database permissions (grant or revoke)

• Execute remote stored procedures (execute or use)

• Execute other arbitrary SQL statements

During a destination-template transfer, the access service inserts the data values it retrieves with the sourceselectstatement from the source database into the destination-template SQL clause. This clause contains one question mark for each column in the result set of the sourceselectstatement.

Each value from the result set is substituted for the corresponding question mark in the template on a row-by-row basis. The access service executes the resulting statement against the target database.

Syntax

The syntax for a destination-template transfer statement is as follows:

transfer [with report]

{to | from} 'secondaryname

userid password';

sourceselectstatement;

destinationtemplatestatement

where:

• transfer must begin all transfer statements.

• with report is an optional phrase specified in the first line of the transfer statement that instructs the access service to return processing information to the client application.

  This information is returned as a result set that consists of one VARCHAR column and one row. The row contains the number of rows transferred, rejected, or modified during processing.

• {to | from} indicates the direction of the transfer:

  • to specifies that the data is transferred from the primary database to the secondary database.

  • from specifies that the data is transferred from the secondary database to the primary database.

• 'secondaryname userid password' is a character string that provides the information needed to connect to the secondary database:

  • secondaryname is the name used to identify the secondary database.

  • userid and password must be valid on the secondary database. You can substitute an asterisk for password, if a password is not specified.

  All of the elements of the character string must be enclosed in single or double quotes in the order shown.

• sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set that will be used in the transfer. This statement can be of any complexity acceptable to the source database, including stored procedures. SQL transformation is not performed on the sourceselectstatement. The transformation must be in the source database SQL syntax.

• destinationtemplatestatement is a SQL statement or any statement that is valid for the target database environment where it executes. SQL transformation is not performed on the destinationtemplatestatement. The transformation must be in the destination database SQL syntax.

  This statement can include question marks as placeholders for the data values that will be inserted. It can also include qualifiers to specify datatypes for the question mark placeholders in the destinationtemplatestatement.

To increase processing efficiency, you can batch destination-templates together for processing. Use the TransferBatch configuration property or a set statement to specify how many templates to batch.

Datatype qualifiers

Qualifiers tell the access service how to format data that is inserted for a placeholder. If you do not supply a qualifier, the access service applies default transformations.

Qualification is required for date and time values. You can use the ?T, ?t, ?D, and ?d qualifiers for dates, or you can create a custom qualifier using special qualifiers. For information about special qualifiers, see "Special Date and Time qualifiers" .

The following table defines valid datatype qualifiers.

The following three tables show the effects of qualifiers on datatypes.

For each table, special circumstances are detailed in the text following.

The first table shows the effects of the ?C, ?N, ?D, and ?T qualifiers.

For CS_CHAR, CS_VARCHAR, AND CS_TEXT used with the ?D qualifier:

• If the source is an ISO TIMESTAMP, it is converted to 'Mon dd yyyy hh:mm:ss:nnn'.

• If it is an ISO DATE, it is converted to 'Mon dd yy'.

• If it is an ISO TIME, it is converted to 'Mon dd yy hh:mm:ss' using the value from the DefaultDate property as the date portion of the value.

For CS_CHAR, CS_VARCHAR, AND CS_TEXT used with the ?T qualifier:

• If the source is an ISO DATE or TIME, the DefaultDate and DefaultTime property values are used to fill in missing information.

The following table shows the effects of the ?y, ?d, ?t, and ?x qualifiers.

For CS_CHAR, CS_VARCHAR, AND CS_TEXT used with the ?y qualifier:

• If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to 'yy/mm/dd'.

For CS_CHAR, CS_VARCHAR, AND CS_TEXT used with the ?d qualifier:

• If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to 'mm/dd/yy'.

For CS_CHAR, CS_VARCHAR, AND CS_TEXT used with the ?t qualifier:

• If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to 'hh:mm:ss'.

For all datatypes used with the ?x qualifier:

• If the target database is ODBC , ?x converts the data to the standard ODBC hexadecimal format (a quoted hexadecimal number with a leading X).

The following table shows the effects of the ?X and ?O qualifiers.

For CS_BINARY, CS_VARBINARY, AND CS_IMAGE datatypes used with the ?X qualifier:

• If the target database is ODBC, ?x converts the data to the standard ODBC hexadecimal format (a quoted hexadecimal number with a leading X).

Special Date and Time qualifiers

You can combine special date and time qualifiers and construct the date or time format that the target database requires, using the following rules:

• Enter special characters in either uppercase or lowercase.

• Separate special characters by any arbitrary character, such as a hyphen, slash, or space. Any unrecognized character is copied to the target as-is.

• Enclose special characters in single or double quotes because the resulting value is passed to the target as a character string.

• Allow qualifiers to contain a null terminated string. The string is limited only by the buffer size (1k).

The following table shows qualifier definitions.

Destination-Template processing

The following sections describe destination-template processing using transfer from and transfer to statements.

transfer from statements

The following steps describe how a service library processes a destination-template transfer from statement from Adaptive Server to ODBC.

• The access service executes the sourceselectstatement against the primary database and retrieves the schema of the result set.

• The access service inserts the first n rows of data (where n is the setting of the TransferBatch property) resulting from the sourceselectstatement into the destination-template. The access service formats the data according to the specified qualifiers and groups the statements into a request,

• The access service executes the request against the primary database.

• The access service substitutes the next n rows and executes another request. It repeats this process until all the rows finish processing.

• If conversion errors occur as rows are inserted, the invalid rows are handled according to the values set in the CharConvertError, NumConvertError, DatetimeConvertError, DefaultDate, DefaultNum, and DefaultTime properties, and the transfer continues. If SendWarningMessages is set to yes, a warning message is sent to the client.

transfer to statements

The following steps describe how a service library processes a destination-template transfer to statement to a target from ODBC.

• The access service issues the sourceselectstatement against the primary database and receives the schema of the result set.

• The access service receives the results of the sourceselectstatement and converts them to the predefined Adaptive Server datatypes. These datatypes do not necessarily match the datatypes of the columns in the destination table.

• The access service substitutes the first n rows of the result set (where n is the number of rows specified in the TransferBatch configuration property). The access service formats the data according to the specified datatype qualifiers and batches the statements into a request.

• The access service issues the request against the secondary database.

• The access service substitutes the next n rows into the destinationtemplatestatement and issues another request against the secondary database. It repeats this process until all the rows finish processing.

• If conversion errors occur as rows are inserted, the invalid rows are handled according to the values set in the CharConvertError, NumConvertError, DatetimeConvertError, DefaultDate, DefaultNum, and DefaultTime properties, and the transfer continues. If SendWarningMessages is set to yes, a warning message is sent to the client.

Datatype conversion for transfer to statements

Datatype conversion takes place in two steps. The following table shows how incoming target ODBC datatypes are initially converted to Open Server datatypes.

These datatypes are converted into the datatypes specified by the destinationtemplatestatement datatype qualifiers.

Destination-Template Transfer errors

Value errors occur during transfer processing when the value being inserted is out of range for the column's datatype. The access service handles these errors using the following properties:

• CharConvertError

• NumConvertError

• DateTimeConvertError

• DefaultDate

• DefaultTime

• DefaultNum

If the SendWarningMessages property is set to yes, the access service sends a message to the client application when it encounters value errors. In addition, these properties can be used to fill in default values when datatype conversion fails during both types of transfer.

Obtaining error information

You can obtain destination-template transfer error information in the following ways:

• If you include with report in the transfer statement, you receive a result set containing one VARCHAR column and one row indicating the number of rows transferred, rejected, and modified during processing.

• You can execute @@RejectedRowCount or @@DefaultedRowCount immediately after a successful transfer. These global variables return the number of rejected or defaulted rows.

• If you set SendWarningMessages to yes, the access service returns data conversion errors to the client application.

During processing, the access service sets the StopCondition property to none. It uses the value in the TransferErrorCount property to determine the number of error rows it will allow before it stops processing.

You can set this value with the following statement:

set  TransferErrorCount nnn

The default setting is 0, which causes the access service to ignore errors.

Creating a Transfer RPC

The client application can create a stored procedure within Adaptive Server that invokes the DirectConnect Server library transfer function. The service library receives the transfer command as an RPC event with the following arguments:

• Name of the RPC: "transfer"

• Argument 1: The secondary connection information ({to | from} "server userid pw")

• Argument 2: Either the bulk copy target command or the destination-template sourceselect statement

• Argument 3: Either the bulk copy sourceselect statement or the destination-template sourceselect statement.

Transfer RPC example

The following example outlines how a stored procedure shows a bulk copy transfer statement to be received as an RPC.

create procedure  replauth as

begin

execute servername. . .transfer

"to 'servername2 userid password';",
 "with  replace into authors;",
 "select * from  authors;"

end

where:

• servername specifies the access service to handle the transfer.

  • The "..." following the servername are required.

  • The service library recognizes anything other than "transfer" in the next position as the name of an ODBC stored procedure.

• servername2 specifies the secondary database for the transfer command.

Executing a Transfer RPC

A client may log in to the Adaptive Server on which this procedure is defined and invoke it as follows:

execute replauth

When Adaptive Server executes replauth, it passes an RPC to the access service. The access service returns any result rows or messages to the client application, not to Adaptive Server.