CREATE SERVER statement

CREATE SERVER server-name
CLASS server-class-string
USING connection-info-string 
[ READ ONLY ]

server-class-string :
'SAODBC' 
| 'ASEODBC' 
| 'DB2ODBC' 
| 'IQODBC' 
| 'MSSODBC'
| 'ORAODBC' 
| 'MSACCESSODBC' 
| 'MYSQLODBC' 
| 'ULODBC' 
| 'ADSODBC' 
| 'ODBC' 
| 'SAJDBC'
| 'ASEJDBC'
| 'IQJDBC'

connection-info-string :
  { host-name:port-number [/dbname ] | data-source-name | sqlanywhere-connection-string }

CREATE SERVER server-name
CLASS 'DIRECTORY'
USING using-string

using-string :
'ROOT = path
[ ;SUBDIRS = n ]
[ ;READONLY = { YES | NO } ]'
[ ;CREATEDIRS = { YES | NO } ]'

CLASS clause Specifies the server class you want to use for a remote connection. Server classes contain detailed server capability information. The DIRECTORY class is used in Syntax 2 to access a directory on the local computer.
USING clause In Syntax 1, the USING clause supplies a connection string for the database server. The appropriate connection string depends on the driver being used, which in turn depends on the server-class-string.
If an ODBC-based server class is used, the USING clause is an ODBC connection string that can include 'dsn=data-source-name' to specify an ODBC data source name and/or 'driver=driver-name' to specify a driver binary on Unix or driver definition on Windows.

For SQL Anywhere remote servers (SAODBC server classes), the connection-info-string parameter can be any valid SQL Anywhere connection string. You can use any SQL Anywhere connection parameters. For example, if you have connection problems, you can include a LOG connection parameter to troubleshoot the connection attempt.

The USING clause can also contain a local or global variable name enclosed in {}. For example, a USING clause that contains 'dsn={@mydsn}' indicates that @mydsn is a SQL variable and that the current contents of the @mydsn variable should be substituted when expanding the USING clause.

The SQL variable variable-name that must be of type CHAR, VARCHAR, or LONG VARCHAR.

For more information about using variables in the USING clause, see Example 2 in Creating directory access servers (Sybase Central).

If a JDBC-based server class is used, the USING clause is of the form host-name:port-number [/dbname], where:
- host-name The computer the remote server runs on.
- port-number The TCP/IP port number the remote server listens on. The default port number for SQL Anywhere is 2638.
- dbname For SQL Anywhere remote servers, if you do not specify a dbname, then the default database is used. For Adaptive Server Enterprise, the default is the master database, and an alternative to using dbname is to specify another database by some other means (for example, in the FORWARD TO statement).
  In Syntax 2, the USING clause specifies the following values for the local directory:
  - ROOT clause The path, relative to the database server, that is the root of the directory access class. When you create a proxy table using the directory access server name, the proxy table is relative to this root path.
  - SUBDIRS clause A number between 0 and 10 that represents the number of levels of directories within the root that the database server can access. If SUBDIRS is omitted or set to 0, then only the files in the root directory are accessible via the directory access server. You can create proxy tables to any of the directories or subdirectories available via the directory access server.
  - READONLY clause Specifies whether the files accessed by the directory are READONLY and cannot be modified. By default, this is set to NO.
  - CREATEDIRS clause Specifies whether directories can be created using the directory access server. The default is NO.

When you create a remote server, it is added to the ISYSSERVER system table.

Syntax 1 The CREATE SERVER statement defines a remote server.

The SAJDBC, ASEJDBC, and IQJDBC JDBC-based server classes are deprecated and should not be used.

To bypass the ODBC driver manager when defining a SQL Anywhere remote server, use the syntax below, followed by the remainder of the connection-info-string:

CREATE SERVER remote-server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere Native;DSN=my-dsn';

This syntax allows remote data access to load the SQL Anywhere ODBC driver directly and is supported by Windows and Unix. Loading the SQL Anywhere ODBC driver directly ensures that the ODBC driver for the current server version is used. Also, if the SQL Anywhere ODBC driver is only used for remote data access, it does not need to be registered.

Note

If the application also makes use of non-SQL Anywhere remote servers, or if there are SQL Anywhere remote servers defined without using 'DRIVER=SQL Anywhere Native', then remote data access still uses a driver manager for the other remote servers.

On Unix platforms you can also reference the ODBC driver manager using the supplied iAnywhere Solutions ODBC driver. The syntax is as follows:

USING 'driver=SQL Anywhere 12;DSN=my-dsn'

Syntax 2 The CREATE SERVER statement lets you create a directory access server that accesses the local directory structure on the computer where the database server is running. You must create an external login for each database user that needs to use the directory access server. On Unix, the database server runs as a specific user, so file permissions are based on the permissions granted to the database server user.

DBA authority.

Not supported on Windows Mobile.

Automatic commit.

SQL/2008 Vendor extension.

The following example creates a SQL Anywhere remote server named testsa, using the SQL Anywhere ODBC driver.

CREATE SERVER testsa
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 12;DSN=remoteSA';

The following example directly loads the SQL Anywhere ODBC driver without using the ODBC driver manager:

CREATE SERVER testsa
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere Native;DSN=remoteSA';

The following example creates an Adaptive Server Enterprise (ASE) remote server named ase_prod using the ASE ODBC driver.

CREATE SERVER ase_prod
CLASS 'ASEODBC'
USING 'DSN=remoteASE';

The following example creates a remote server for the Oracle server named oracle723. Its ODBC data source name is oracle723.

CREATE SERVER oracle723
CLASS 'ORAODBC'
USING 'oracle723';

The following example creates a directory access server that only sees files within the directory c:\temp.

CREATE SERVER diskserver0
CLASS 'directory'
USING 'root=c:\temp';
CREATE EXTERNLOGIN DBA TO diskserver0;
CREATE EXISTING TABLE diskdir0 AT 'diskserver0;;;.';

-- Get a list of those files.
SELECT permissions, file_name, size FROM diskdir0;

The following example creates a directory access server that sees nine levels of directories.

-- Create a directory server that sees 9 levels of directories.
CREATE SERVER diskserver9
CLASS 'directory'
USING 'ROOT=c:\temp;SUBDIRS=9';
CREATE EXTERNLOGIN DBA TO diskserver9;
CREATE EXISTING TABLE diskdir9 AT 'diskserver9;;;.';

CREATE SERVER statement

Syntax 1

Syntax 2

Parameters

Remarks

Note

Permissions

Side effects

See also

Standards and compatibility

Example