ASA SQL Reference
SQL Statements
Use this statement in an Adaptive Server Anywhere remote database to create a synchronization user.
CREATE SYNCHRONIZATION USER ml_username
[ TYPE sync-type ]
[ ADDRESS network-parameters ]
[ OPTION option=value, ... ]
ml_username: identifier
sync-type: tcpip | http | https | ActiveSync
network-parameters: string
value: string | integer
ml_username A name identifying a remote database. This name must be unique.
For more information about synchronization user names, see About MobiLink users.
TYPE clause This clause specifies the communication protocol to use for synchronization. The options are tcpip, http, https, and ActiveSync. The default protocol is tcpip.
ADDRESS clause This clause specifies network-parameters in the form keyword=value, separated by semi-colons. Which settings you supply depends on the communication protocol you are using (TCP/IP, HTTP, HTTPS, or ActiveSync).
TCP/IP parameters If you specify the tcpip protocol, you can optionally specify the following network-parameters:
client_port=nnnnn or client_port=nnnnn-mmmmm A range of client ports for communication. If only one value is specified, the end of the range is 100 greater than the initial value, for a total of 101 ports.
The option can be useful for clients inside a firewall communicating with a MobiLink synchronization server outside the firewall
host=hostname The host name or IP number for the machine on which the MobiLink synchronization server is running. The default value is localhost. For Windows CE, the default value is the value of ipaddr in the registry folder Comm\Tcpip\Hosts\ppp_peer. This allows a Windows CE device to connect to a MobiLink synchronization server executing on the desktop machine where the Windows CE device's cradle is connected.
For the Palm Computing Platform, the default value of localhost refers to the device. It is recommended that an explicit host name or IP address be specified.
liveness_timeout=n The amount of time, in seconds, after a client stops communicating before MobiLink recovers the connection. A value of 0 means that there is no timeout. This option is only effective if download acknowledgement on the client if set to off (the default). The default is 120 seconds.
port=portnumber The socket port number. The port number must be a decimal number that matches the port the MobiLink synchronization server is setup to monitor. The default is 2439, which is the IANA registered port number for the MobiLink synchronization server.
network_name=name
Specify the network name so that you can use MobiLink's auto-dial feature. This allows you to connect from a Pocket PC 2002 or Windows desktop computer without manually dialing. Used with scheduling, your remote can synchronize unattended. Used without scheduling, this allows you to run dbmlsync without manually dialing a connection. The name should be the network name that you have specified in the dropdown list in Settings
For more information about scheduling, see Scheduling synchronization.
network_connect_timeout=seconds When you specify network_name, you can optionally specify a timeout after which the dial-up fails. This feature applies to Pocket PC 2002 only. (On Windows, you control this feature by configuring the connection profile.) The default is 120 seconds.
network_leave_open={0|1} When you specify network_name, you can optionally specify that the connection should be left open after the synchronization finishes (1). The default behavior is to close the connection (0).
security=cipher(keyword=value;...) All communication through this connection is to be encrypted using the cipher suite specified. The cipher can be one of ecc_tls or rsa_tls. These refer to elliptic-curve and RSA certification. For backwards compatibility, ecc_tls can also be specified as certicom_tls.
Separately licensable option requiredUse of Certicom technology requires that you obtain the separately-licensable SQL Anywhere Studio security option and is subject to export regulations.For more information about security, see Transport-Layer Security. |
The following security keywords are supported.
certificate_company If you specify this parameter, the MobiLink client only accepts server certificates when the organization field on the certificate matches this value.
certificate_unit If you specify this parameter, the MobiLink client only accepts server certificates when the organization unit field on the certificate matches this value.
certificate_name If you specify this parameter, the MobiLink client only accepts server certificates when the common name field on the certificate matches this value.
trusted_certificates When synchronization occurs through a Certicom TLS synchronization stream, the MobiLink synchronization server sends its certificate to the client, as well as the certificate of the entity that signed it, and so on up to a self-signed root.
The client checks that the chain is valid and that it trusts the root certificate in the chain. This feature allows you to specify which root certificates to trust. By default, the Sybase certificate is the trusted root.
HTTP parameters If you specify the http protocol, you can optionally specify the following network-parameters:
buffer_size=number The maximum body size for a fixed content length message, in bytes. Changing the option will decrease or increase the amount of memory allocated for sending content. The default is 65 535, except on UltraLite and Pocket PC, in which case it is 1 024.
client_port=nnnnn or client_port=nnnnn-mmmmm A range of client ports for communication. If only one value is specified, the end of the range is 100 greater than the initial value, for a total of 101 ports.
The option can be useful for clients inside a firewall communicating with a MobiLink synchronization server outside the firewall.
host=hostname The host name or IP number for the machine on which the MobiLink synchronization server is running. The default value is localhost. For Windows CE, the default value is the value of ipaddr in the registry folder Comm\Tcpip\Hosts\ppp_peer. This allows a Windows CE device to connect to a MobiLink synchronization server executing on the desktop machine where the Windows CE device's cradle is connected.
For the Palm Computing Platform, the default value of localhost refers to the device. It is recommended that an explicit host name or IP address be specified.
network_name=name
Specify the network name so that you can use MobiLink's auto-dial feature. This allows you to connect from a Pocket PC 2002 or Windows desktop computer without manually dialing. Used with scheduling, your remote can synchronize unattended. Used without scheduling, this allows you to run dbmlsync without manually dialing a connection. The name should be the network name that you have specified in the dropdown list in Settings
For more information about scheduling, see Scheduling synchronization.
network_connect_timeout=seconds When you specify network_name, you can optionally specify a timeout after which the dial-up fails. This feature applies to Pocket PC 2002 only. (On Windows, you control this feature by configuring the connection profile.) The default is 120 seconds.
network_leave_open={0|1} When you specify network_name, you can optionally specify that the connection should be left open after the synchronization finishes (1). The default behavior is to close the connection (0).
persistent={0|1} 1 means that the client will attempt to use the same TCP/IP connection for all HTTP requests in a synchronization. A setting of 0 is more compatible with intermediate agents. The default is 1, except on Palm devices it is 0.
Note: Except on Palm devices, you should only set persistent to 1 if you are connecting directly to MobiLink. If you are connecting through an intermediate agent such as a proxy or redirector, a persistent connection may cause problems.
port=portnumber The socket port number. The port number must be a decimal number that matches the port the MobiLink synchronization server is set up to monitor. The default value for the port number is 80, which is the IANA registered port number for the MobiLink synchronization server.
proxy_host=proxy_hostname The host name or IP address of the proxy server. The default value is localhost.
proxy_port=proxy_portnumber The port number of the proxy server. The default value is 80.
security=cipher(keyword=value;...) All communication through this connection is to be encrypted using the cipher suite specified. The cipher can be one of ecc_tls or rsa_tls. These refer to elliptic-curve and RSA certification. For backwards compatibility, ecc_tls can also be specified as certicom_tls.
Separately licensable option requiredUse of Certicom technology requires that you obtain the separately-licensable SQL Anywhere Studio security option and is subject to export regulations.For more information about security, see Transport-Layer Security. |
The following security keywords are supported.
certificate_company If you specify this parameter, the MobiLink client only accepts server certificates when the organization field on the certificate matches this value.
certificate_unit If you specify this parameter, the MobiLink client only accepts server certificates when the organization unit field on the certificate matches this value.
certificate_name If you specify this parameter, the MobiLink client only accepts server certificates when the common name field on the certificate matches this value.
trusted_certificates When synchronization occurs through a Certicom TLS synchronization stream, the MobiLink synchronization server sends its certificate to the client, as well as the certificate of the entity that signed it, and so on up to a self-signed root.
The client checks that the chain is valid and that it trusts the root certificate in the chain. This feature allows you to specify which root certificates to trust. By default, the Sybase certificate is the trusted root.
url_suffix=suffix The suffix to add to the URL on the first line of each HTTP request. When synchronizing through a proxy server, the suffix may be necessary in order to find the MobiLink synchronization server. The default value is MobiLink.
version=versionnumber A string specifying the version of HTTP to use. You have a choice of 1.0 or 1.1. The default value is 1.1.
HTTPS parameters The HTTPS communication stream uses Certicom RSA security.
Separately licensable option requiredUse of Certicom technology requires that you obtain the separately-licensable SQL Anywhere Studio security option and is subject to export regulations.For more information about security, see Transport-Layer Security. |
If you specify the HTTPS protocol, you can optionally specify the following network-parameters:
buffer_size=number The maximum body size for a fixed content length message, in bytes. Changing the option will decrease or increase the amount of memory allocated for sending content. The default is 65 535, except on UltraLite and Pocket PC, in which case it is 1 024.
client_port=nnnnn or client_port=nnnnn-mmmmm A range of client ports for communication. If only one value is specified, the end of the range is 100 greater than the initial value, for a total of 101 ports.
The option can be useful for clients inside a firewall communicating with a MobiLink synchronization server outside the firewall.
host=hostname The host name or IP number for the machine on which the MobiLink synchronization server is running. The default value is localhost. For Windows CE, the default value is the value of ipaddr in the registry folder Comm\Tcpip\Hosts\ppp_peer. This allows a Windows CE device to connect to a MobiLink synchronization server executing on the desktop machine where the Windows CE device's cradle is connected.
For the Palm Computing Platform, the default value of localhost refers to the device. It is recommended that an explicit host name or IP address be specified.
network_name=name
Specify the network name so that you can use MobiLink's auto-dial feature. This allows you to connect from a Pocket PC 2002 or Windows desktop computer without manually dialing. Used with scheduling, your remote can synchronize unattended. Used without scheduling, this allows you to run dbmlsync without manually dialing a connection. The name should be the network name that you have specified in the dropdown list in Settings
For more information about scheduling, see Scheduling synchronization.
network_connect_timeout=seconds When you specify network_name, you can optionally specify a timeout after which the dial-up fails. This feature applies to Pocket PC 2002 only. (On Windows, you control this feature by configuring the connection profile.) The default is 120 seconds.
network_leave_open={0|1} When you specify network_name, you can optionally specify that the connection should be left open after the synchronization finishes (1). The default behavior is to close the connection (0).
persistent={0|1} 1 means that the client will attempt to use the same TCP/IP connection for all HTTP requests in a synchronization. A setting of 0 is more compatible with intermediate agents. The default is 1, except on Palm devices it is 0.
Note: Except on Palm devices, you should only set persistent to 1 if you are connecting directly to MobiLink. If you are connecting through an intermediate agent such as a proxy or redirector, a persistent connection may cause problems.
port=portnumber The socket port number. The port number must be a decimal number that matches the port the MobiLink synchronization server is set up to monitor. The default value for the port parameter is 443, which is the IANA registered port number for the MobiLink synchronization server.
proxy_host=proxy_hostname The host name or IP address of the proxy server. The default value is localhost.
proxy_port=proxy_portnumber The port number of the proxy server. The default value is 443.
certificate_company If you specify this parameter, the MobiLink client only accepts server certificates when the organization field on the certificate matches this value.
certificate_unit If you specify this parameter, the MobiLink client only accepts server certificates when the organization unit field on the certificate matches this value.
certificate_name If you specify this parameter, the MobiLink client only accepts server certificates when the common name field on the certificate matches this value.
trusted_certificates When synchronization occurs through a Certicom TLS synchronization stream, the MobiLink synchronization server sends its certificate to the client, as well as the certificate of the entity that signed it, and so on up to a self-signed root.
The client checks that the chain is valid and that it trusts the root certificate in the chain. This feature allows you to specify which root certificates to trust. By default, the Sybase certificate is the trusted root.
For more information about security, see Transport-Layer Security.
url_suffix=suffix The suffix to add to the URL on the first line of each HTTPS request. When synchronizing through a proxy server, the suffix may be necessary in order to find the MobiLink synchronization server. The default value is MobiLink.
version=versionnumber A string specifying the version of HTTP to use. You have a choice of 1.0 or 1.1. The default value is 1.1.
ActiveSync parameters During ActiveSync synchronization, ActiveSync is used to exchange data with the MobiLink provider for ActiveSync, which resides on the desktop machine. The ActiveSync parameters describe the communications between the MobiLink provider for ActiveSync and the MobiLink synchronization server.
The address string for ActiveSync takes the following form:
stream=desktop-stream;[desktop-stream-params]
where:
desktop-stream is the synchronization stream to use between the MobiLink provider for ActiveSync and the MobiLink synchronization server. It can be http, https, or tcpip. The default setting is tcpip.
desktop-stream-params are TCP/IP, HTTP, or HTTPS parameters, as described in the lists above.
For more information, see ActiveSync provider installation utility.
OPTION clause The OPTION clause allows you to set options using option=value in a comma-separated list.
The values for each option cannot contain equal signs or semicolons. The database server accepts any option that you enter without checking for its validity. Therefore, if you misspell an option or enter an invalid value, no error message appears until you run the dbmlsync command to perform synchronization.
Options set for a synchronization user can be overridden in individual subscriptions or on the dbmlsync command line.
For complete information about extended options, see -e extended options.
The sync-type, network-parameters, and options can be set in several places:
on the dbmlsync command line using the -e or -eu options
in Sybase Central
using the following SQL statements:
CREATE SYNCHRONIZATION SUBSCRIPTION
ALTER SYNCHRONIZATION SUBSCRIPTION
CREATE SYNCHRONIZATION USER
ALTER SYNCHRONIZATION USER
CREATE SYNCHRONIZATION SUBSCRIPTION without specifying a synchronization user (this associates the values with a publication)
When you store extended options and connection parameters in the database, dbmlsync reads the information from the database. If values are specified in both the database and the command line, the value strings are combined. If conflicting values are specified, dbmlsync resolves them as follows, where values occurring earlier in the list take precedence over those occurring later in the list.
dbmlsync extended option -eu
dbmlsync extended option -e
specified on the subscription (whether by a SQL statement or in Sybase Central)
specified on the MobiLink user (whether by a SQL statement or in Sybase Central)
specified on the publication (whether by a SQL statement or in Sybase Central)
Must have DBA authority.
Automatic commit.
ALTER SYNCHRONIZATION USER statement [MobiLink]
CREATE SYNCHRONIZATION SUBSCRIPTION statement [MobiLink]
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Supported by Open Client/Open Server.
The following example creates a user named SSinger, who synchronizes over TCP/IP with a server machine named mlserver.mycompany.com using the password Sam. The use of a password in the user definition is not secure.
CREATE SYNCHRONIZATION USER SSinger TYPE http ADDRESS 'host=mlserver.mycompany.com' OPTION MobiLinkPwd='Sam'
The following creates a synchronization user called factory014 that will cause dbmlsync to hover and synchronize via Certicom-encrypted TCP/IP at a random time in every 8-hour interval. The randomness helps prevent performance degradation at the MobiLink server due to multiple simultaneous synchronizations:
CREATE SYNCHRONIZATION USER factory014 TYPE tcpip ADDRESS 'host=mycompany.manufacturing.mobilink1;security=certicom_tls(certificate=mycompany_mobilink.crt;certificate_password=thepassword)' OPTION Schedule='EVERY:08:00'
The following creates a synchronization user called sales5322 that will be used to synchronize with HTTP. In this example, the MobiLink synchronization server runs behind the corporate firewall, and synchronization requests are redirected to it using the Redirector (a reverse proxy to an NSAPI Web server).
CREATE SYNCHRONIZATION USER sales5322 TYPE https ADDRESS 'host=www.mycompany.com;port=80;url_suffix=mlredirect/ml/'