This topic includes the following sections:
- Connect string syntax
- Db_driver and database_info
- Network string (opennet_info) syntax
- Port settings
- MySQL notes and examples
- ODBC-compliant (VTX11) notes and examples
- Oracle notes and examples
- SQL Server notes and examples
- Using the SQL Server shared memory protocol
Connect string syntax
A connect string contains the information needed to access a database and determines whether SQL OpenNet will be used for the network layer. See Understanding connect strings for more information.
There are two forms of connect string:
- The first starts with the db_driver argument and connects directly (without using SQL OpenNet) to a local database or database client:
db_driver:database_info
- The second form starts with net: and uses SQL OpenNet for access over a network:
net:database_info@opennet_info
db_driver
The name of the database driver to be used. See Db_driver and database_info below.
database_info
Information used for database access. See Db_driver and database_info below.
opennet_info
The network string. This is information needed to access an SQL OpenNet service on the machine with the database or database client. See Network string (opennet_info) syntax below.
Db_driver and database_info
The table below lists supported databases and the database drivers (db_driver) and the database_info syntax for them.
Note the following OS-speciffor db_driver:
- On Windows, a database driver consists of a DLL and an executable, which are located in the synergyde\connect directory. (The db_driver argument represents both.) Do not include a path for db_driver — just the driver name itself.
- On UNIX, db_driver is a shared object (.so file) that is installed in the synergyde/connect directory. Db_driver can include a path on UNIX, but there is no need to specify one unless you move the shared object to a directory other than synergyde/connect.
- On OpenVMS, db_driver is a database driver (.exe shared image file) that is located in the SYS$COMMON:[SYSLIB] directory. Do not include a path for db_driver — just the driver name itself.
|
Database_info syntax |
||
|---|---|---|
|
IBM DB2a |
VTX7 |
database_name or userid/password/database_name |
|
MySQL 5 MySQL 8 (64-bit) (Windows) |
VTX14_5 VTX14_8 |
userid/[password]/[database]/[server] See MySQL notes and examples below. |
|
MySQL (UNIX) |
VTX14 |
|
|
ODBC-complianta |
VTX11 |
userid/[password]/dsn See ODBC-compliant (VTX11) notes and examples below. |
|
Oracle 12 and higher (Windows) |
VTX0_12, VTX0_19 (use VTX0_19 for Oracle 18 and higher) |
userid/password[/net_service_name] See Oracle notes and examples below. |
|
Oracle (UNIX and OpenVMS) |
VTX0 |
|
|
Oracle Rdba |
VTX1 |
schema_name |
|
PostgreSQL (Windows)a |
VTX16 |
userid/password/database_name/options options must be a string with connection parameters specified as key = value pairs |
|
SQL Server |
VTX12_SQLNATIVE |
userid/[password]/dsn or userid/[password]/[database_name]/[server_name [\\instance_name]]/[app_name]/[language][/other_options] See SQL Server notes and examples below. |
|
Synergy database |
VTX4 |
userid/[password]/sdms:connect_file |
a. Support for these databases may require assistance from Synergex Professional Services and additional support fees. Contact your Synergex account manager for details.
Network string (opennet_info) syntax
The opennet_info portion of a connect string provides the information needed to connect to the SQL OpenNet service on the machine that has the database or a client for the database. It also determines which database driver (on the server) is used.
This is the syntax for opennet_info:
@[port:]host[([domain\]uid/pwd)]!db_driver[,ENV_VAR=env_spec,...]
Arguments
port
(optional) The port number for communicating with SQL OpenNet server. See Port settings below.
host
The IP address or name of the machine that has the database or database client. This can be up to 64 characters long. On OpenVMS, an IP address must be in IPv4 format. On Windows and UNIX it can be in IPv4 or IPv6 format, depending on what the SQL OpenNet service is set to recognize. Note that each colon in an IPv6 address must be escaped with another colon—e.g., fe80::::2cb8::ddce::c127::3cf4. See vtxnetd and vtxnet2 programs for more information.
([domain\]uid/pwd)
(optional) Log-in information for an account on the host machine or, if domain is also specified, an account on a Windows domain. This is different from the user ID and password for the database, which are passed as part of database_info in the connect string.
db_driver
The name of the database driver to be used. See Db_driver and database_info for database driver names.
ENV_VAR=env_spec
(optional) An environment variable definition stored in or used by the target database (generally for specifying or locating data files).
The network string (opennet_info) is the part of a connect string that starts with an “at” sign (@). Include it only if the connect string starts with net:. In the following example, the highlighted portion is the network string (port is 1958, host is win_srv, and db_driver is VTX12_SQLNATIVE):
net:my_uid/my_pwd/my_dsn@1958:win_srv!VTX12_SQLNATIVE
Include the domain, uid, and pwd arguments only if the -a option is specified for vtxnetd or vtxnet2. (See vtxnetd and vtxnet2 programs.) The parentheses and slashes are required for these arguments — e.g., (my_domain\uid/pwd). Use the backslash only after a Windows domain name.
|
When using vtxnet2 to access a database on a Windows server, the “Log on as a batch job” option must be set for the user account specified by uid. Domain user accounts must have a group policy that includes the “Log on as a batch job” option. |
For network string examples, see the following sections below:
- Oracle notes and examples
- ODBC-compliant (VTX11) notes and examples
- MySQL notes and examples
- SQL Server notes and examples
Overriding delimiters in a network string
SQL OpenNet uses the following special characters as string delimiters: at sign (@), colon (:), and exclamation point (!). In the network part of a connect string, each of these delimiters conveys a specific instruction to the SQL OpenNet processor and generally is not passed by the processor unless an identical character follows the first. If you want to pass an at sign, colon, or exclamation point as part of an environment variable definition, or at any other place in the network connect string, you must duplicate the character to enable the parser to interpret the statement correctly.
For example, @unix_srv in the following connect string is the host name of the computer containing the database.
net:my_uid/my_pwd@@unix_srv!/usr/synergyde/connect/VTX0
In the following, DBDATA is set to datdir!.
net:my_uid/my_pwd@unix_srv!/usr/synergyde/connect/VTX0,DBDATA=datdir!!
The following connect string uses two “at” signs to force the first to become the delimiter for a network string for a second server. (See Synergex KnowledgeBase article 2075 for more information on this configuration.)
net:my_uid/my_pwd@88.0.0.12!/usr/bin/VTX3@@unix_srv2!/bin/VTX0
Port settings
To make a connection with SQL OpenNet, the port setting on the client must match the port number used for the SQL OpenNet server. For example, if vtxnetd is started on port 1990, the client port setting for SQL OpenNet must be 1990.
Setting the port number on the server
On a Windows or UNIX server, you must specify the port number in one of the following ways:
- The vtxnetd or vtxnet2 command line in opennet.srv (on Windows), or the vtxnetd command line in the startnet script (on UNIX). These are the recommended methods. Setting the port in this way overrides a services file setting on a Windows or UNIX server.
- The vtxnet setting in the TCP/IP services file (which is in %windir%\system32\drivers\etc on Windows and /etc on UNIX). A port setting in this file is used only when the port is not specified in the vtxnetd or vtxnet2 start-up command.
On an OpenVMS server, the default port is 1958. You can override this default by specifying the port number in the vtxnetd command (in NET.COM).
For more information on server-side port settings, see Configuring Connectivity Series.
Setting the port number on the clients
On clients, you can specify the port number in any of the following ways:
- In the connect string. This is the recommended method. A port setting in the connect string overrides all other port settings for a client.
- The vtxnet setting in the TCP/IP services file (which is in %windir%\system32\drivers\etc on Windows and /etc on UNIX). This is the default port setting; it is used only when there is no other port setting on the client. For more information, see Configuring Connectivity Series.
- In the net.ini file. A port setting in net.ini overrides a setting in the services file. See Setting options in net.ini.
Testing port settings
To ensure your client and server port settings match, use either the synxfpng utility (with the -x option) or the vtxping utility.
MySQL notes and examples
Connections to MySQL use the MySQL database drivers: VTX14 for UNIX and either VTX14_5 or VTX14_8 for Windows (for MySQL 5 and 8, respectively).
MySQL 8 is 64-bit only. For a 32-bit application, you must use the net: connect string syntax to connect to a 64-bit vtxnetd.
|
MySQL 8 requires that OpenSSL 1.1.1 be installed, even if you are not using data packet encryption. Consequently, it is supported only on operating systems that support 1.1.1; see OpenSSL requirements (UNIX and OpenVMS) for additional details on OpenSSL support. On Windows, copy the OpenSSL DLLs to the SynergyDE\dbl\bin directory for direct (local) connections or to the SynergyDE\connect directory for vtxnetd or vtxnet2 connections. |
Connect string examples for MySQL
The following connect string is for a direct connection (i.e., it does not use SQL OpenNet) on Windows. Database_info is “my_uid/my_pwd/my_db”.
VTX14_5:my_uid/my_pwd/my_db
This next example uses SQL OpenNet to connect to a Windows server (win_srv). Database_info is “my_uid/my_pwd/my_db”.
net:my_uid/my_pwd/my_db@1958:win_srv!VTX14_5
The next example uses SQL OpenNet to connect to a Linux server (linux_srv). Because the database driver is not in the synergyde/connect directory, the connect string includes a path for the driver.
net:my_uid/my_pwd/my_db@1958:linux_srv!/usr/my_dir/connect/VTX14
ODBC-compliant (VTX11) notes and examples
To access a database using the ODBC database driver (VTX11), a user or system DSN must be defined for the database (see Db_driver and database_info above). For SQL OpenNet connections, the DSN must be on the server. For direct connections, the DSN must be on the clients. See the Microsoft documentation for information on DSNs.
Connect string examples for VTX11
The following example connects directly to an ODBC-compliant database (i.e., it does not use SQL OpenNet). Database_info is “my_uid/my_pwd/my_dsn”. Information on the database is in the DSN (my_dsn).
VTX11:my_uid/my_pwd/my_dsn
The next example uses SQL OpenNet to connect to a Windows server (win_srv). Database_info is “my_uid/my_pwd/my_dsn”.
net:my_uid/my_pwd/my_dsn@1958:win_srv!VTX11
Oracle notes and examples
The net_service_name argument in database_info (see Db_driver and database_info above) specifies a database instance and is required if the database has multiple instances. The spelling of the database instance name must match the spelling used in the SQL*Net configuration file TNSNAMES.ORA. As an alternative to the net_service_name argument, you can use the ENV_VAR argument documented in Network string (opennet_info) syntax to set the Oracle system ID (SID) for the instance. See the Oracle documentation for more information.
We recommend using SQL OpenNet for network connections, but you can connect directly to an Oracle database via the Oracle client. If you do, note the following:
- Oracle will use SQL*Net to make the connection, so the connect string must use SQL*Net syntax. (See the Oracle documentation.) For example, the following connects to an Oracle 12 server named oracle_srv:
VTX0_12:my_uid/my_pwd/oracle_srv
- You must first source the Oracle script oraenv to connect directly to an Oracle database on UNIX.
We recommend against recycling database connections. See Making database connections for more information.
Connect string examples for Oracle
The following connects directly to a local Oracle 12 database or database client on a Windows machine. Database_info is “my_uid/my_pwd”.
VTX0_12:my_uid/my_pwd
The next example uses SQL OpenNet to connect to a Windows server (win_srv).
net:my_uid/my_pwd@1958:win_srv!VTX0_12
The following uses SQL OpenNet to connect to a UNIX server. Note that the connect string includes a path for the database driver (VTX0). This is necessary if the database driver is not in the synergyde/connect directory.
net:my_uid/my_pwd@1958:unix_srv!/usr/my_dir/connect/VTX0
The next example also uses SQL OpenNet to connect to a UNIX server. “Host_uid/host_pwd” is used to log on to the server (unix_srv), and “my_uid/my_pwd” is used to log on to the database on the server. Because the database driver is not in the synergyde/connect directory, the connect string includes a path for the database driver.
net:my_uid/my_pwd@1958:unix_srv(host_uid/host_pwd)!/usr/mydir/connect/VTX0
The following uses SQL OpenNet to connect to an OpenVMS server.
net:my_uid/my_pwd/my_instance.com@1958:vms_srv!VTX0
SQL Server notes and examples
For SQL Server, there are two syntax forms for the database_info section of the connect string, as shown in the table in Db_driver and database_info above.
- If database_info has only two slashes, the first form is used to interpret the connect information:
userid/[password]/dsn
- If database_info has more than two slashes, the second form is used to interpret the connect information. This second form enables you to create a DSN-less connection or specify a DSN with the other_options argument.
userid/[password]/[database_name]/[server_name[\\instance_name]]/[app_name]/[language][/other_options]
The other_options part of the second form of database_info represents a string that can contain SQLDriverConnect() options that are specific to SQL Server. See your Microsoft documentation for SQL Server for information on these options. This string can contain multiple options separated by semicolons (;). For example, the following uses the SQL Server shared memory protocol (Network=dbmslpcn) and includes a DSN specification (DSN=my_dsn). (See Using the SQL Server shared memory protocol below for more information on this feature.)
VTX12_SQLNATIVE:my_uid/my_pwd/////Network=dbmslpcn;DSN=my_dsn
|
|
DSN-less connections are not recommended. Error messages generated by SQL Server and other layers used to access the database can be misleading with these connections. Connections that use DSNs are much easier to configure and test. |
By default, SQL Connection uses highest available of the following Microsoft ODBC Driver versions:
- Microsoft ODBC Driver 18
- Microsoft ODBC Driver 17
|
|
Although SQL Connection will attempt to use an older, unsupported driver for SQL Server if the latest driver is not available on your machine, we recommend that you always use the most recent driver with the latest updates. |
You can explicitly specify a driver by passing Driver={driver_name} in other_options. Driver_name must be a name returned by the ODBC API function SQLDrivers(). See Connect string examples for SQL Server below.
Note the following:
- For SQL OpenNet connections, DSNs must be system DSNs on the server. For direct connections, DSNs must be user or system DSNs on the clients.
- Make DSN access local when you set up the SQL Server database instance, and use the shared memory protocol. See Using the SQL Server shared memory protocol below.
- Windows authentication is not supported for SQL OpenNet connections, so for these connections, make sure your SQL Server database is set up to use SQL Server authentication. For example, with SQL Server 2012 you can use SQL Server Management Studio to set the “SQL Server and Windows Authentication mode” option.
- For Azure SQL Database, do not use SQL OpenNet. Instead, use a direct connection and pass the Azure SQL Database URL as server_name. Make sure variables used for the connect string are large enough to store the full string, which will be longer than most because of the URL. Microsoft ODBC Driver 17 and higher for SQL Server is the only driver supported for Azure SQL Database.
- We recommend that you use vtxnetd for SQL Server connections (rather than vtxnet2).
- Unlike MySQL, Oracle, and Synergy databases, all columns and indexes for SQL Server databases default to a case-insensitive ordering using the SQL_Latin1_General_CP1_CI_AS collation sequences. This means that reports using an ORDER BY, an index, or a relational operator (>, <, ==, and so forth) in an SQL statement will work differently for SQL Server than for other databases (including Synergy databases, which use an ANSI collation sequence). Additionally, for characters such as underscore (_) and dash (-), SQL Server will return results in an order that’s different than other databases, even though for both ASCII and Synergy DBL, an underscore is considered higher than a dash.
- Use a Synergy a36 field for the SQL Server GUID data type.
- You can instruct SQL Connection to call SQLDescribeParam behind the scenes to improve performance and avoid excessive cache memory thrashing for SQL statements with I/O parameters. To invoke this functionality, use the SQLPERFORMANCESQL environment variable or the SSQL_PERFORMANCE_SQL %SSC_CMD option.
- Along with other database warnings, by default SQL Connection logging (SSQLLOG) reports warnings for data that SQL Server will truncate.
- For information on connecting to SQL Server on Windows clusters, see Synergex KnowledgeBase article 0654.
- SQL Server supports the ISO-LATIN character set by default. This is similar to the default Windows code page used for displaying characters in European languages such as German and French, whose binary values exceeds 127. SQL Server automatically translates these types into database columns without the need to define columns as NCHAR or NVARCHAR. Use of NCHAR and NVARCHAR incurs significant database translation overhead and should be avoided if possible. The “Perform translation for character data” option in the SQL Server DSN Configuration dialog must be selected (which is the default). See Microsoft documentation for more information.
Connect string examples for SQL Server
This first example illustrates the first form of database_info syntax: (userid/[password]/dsn). It creates a direct connection (i.e., SQL OpenNet is not used), and the information in the DSN determines whether the database is local or remote.
VTX12_SQLNATIVE:my_uid/my_pwd/my_dsn
The next example is also for a direct connection, but it uses the second form of database_info syntax. Note that the DSN (my_dsn) is passed in the other_options part of database_info. The connection will use the SQL Server shared memory protocol (which improves performance) because the connect string includes Network=dbmslpcn. It will also use multiple active result sets (MARS) because the connect string sets MARS_Connection=yes.
VTX12_SQLNATIVE:my_uid/my_pwd/my_db////Network=dbmslpcn;DSN=my_dsn;MARS_Connection=yes
The next example uses SQL OpenNet to connect to a port number 1960 on a Windows server (win_srv).
net:my_uid/my_pwd/my_db////Network=dbmslpcn;DSN=my_dsn;MARS_Connection=yes@1960:win_srv!VTX12_SQLNATIVE
The following example uses the first form of database_info syntax and uses SQL OpenNet to connect to a Windows server. Database_info is my_uid/my_pwd/my_dsn, port is 1960, and host is win_srv.
net:my_uid/my_pwd/my_dsn@1960:win_srv!VTX12_SQLNATIVE
The next three examples are for DSN-less connections (which we do not recommend). The first is for a direct connection that uses the second form of database_info. With this example, no user ID, password, or DSN is specified. Instead, the connect string instructs SQL Server to use Windows authentication (Trusted_connection=yes) for the user ID and password, and the SQL Server driver is specified by passing Driver={SQL Server Native Client 11.0}. Both are passed as other_options. My_db is the database name, and my_instance is the instance name. The period before my_instance indicates that the instance is on the local machine.
VTX12_SQLNATIVE://my_db/.\\my_instance///Driver={SQL Server Native Client 11.0};Trusted_connection=yes
The following specifies an instance name (TESTDB):
vtx12_sqlnative:my_uid/my_pwd//my_server\\TESTDB
The following uses SQL OpenNet and specifies a database name (db_name) and an application name (app_name):
net:my_uid/my_pwd/db_name//app_name/@1958:win_srv!VTX12_SQLNATIVE
The following uses the second form of the syntax for SQL Server to connect to an Azure SQL Database named my_db at my-azure-sql-db.net:
vtx12_sqlnative:my_uid/my_pwd/my_db/my-azure-sql-db.net\\//
Using the SQL Server shared memory protocol
For improved performance with SQL Server, use the SQL Server shared memory protocol. This reduces the number of TCP/IP sockets used for a connection, making it less likely that all sockets for the server will be used at one time, which can greatly impede performance. Note the following:
- For remote databases, the SQL Server shared memory protocol is available only if you use VTX12_SQLNATIVE and SQL OpenNet for all network connections. The DSN must be on the machine that has the SQL Server database.
- When you configure the DSN, select “(local)” as the server name in the DSN configuration screen, and prefix this with “LPC:” (without quotes)—i.e., LPC:(local). See figure 1 below.
To use the shared memory protocol with a DSN-less connection (which is not recommended), use the second form of syntax for database_info and either omit the server name from database_info or add the shared memory protocol option to the other_options string—for example, Network=dbmslpcn or Server=LPC:(local). See the Microsoft documentation for information on SQL Server options. For example, the following uses the Network=dbmslpcn option:
VTX12_SQLNATIVE:my_uid/my_pwd/my_db////Network=dbmslpcn
The next example also uses this option, but connects to a remote database:
net:my_uid/my_pwd/my_db////Network=dbmslpcn@1958:win_srv!VTX12_SQLNATIVE
