User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 28. Accessing Remote Data       

Working with proxy tables

Location transparency of remote data is enabled by creating a local proxy table that maps to the remote object. To create a proxy table you use one of the following statements:

• If the table already exists at the remote storage location, use the CREATE EXISTING TABLE statement. This statement defines the proxy table for an existing table on the remote server.

• If the table does not exist at the remote storage location, use the CREATE TABLE statement. This statement creates a new table on the remote server, and also defines the proxy table for that table.

  Specifying proxy table locations

The AT keyword is used with both CREATE TABLE and CREATE EXISTING TABLE to define the location of an existing object. This location string has 4 components that are separated by either a period or a semicolon. Semicolons allow filenames and extensions to be used in the database and owner fields.

... AT 'server.database.owner.tablename'

Server 

This is the name by which the server is known in the current database, as specified in the CREATE SERVER statement. This field is mandatory for all remote data sources.

Database 

The meaning of the database field depends on the data source. In some cases this field does not apply and should be left empty. The periods are still required, however.

• Adaptive Server Enterprise     Specifies the database where the table exists. For example master or pubs2.

• Adaptive Server Anywhere     This field does not apply; leave it empty.

  The database name for an Adaptive Server Anywhere ODBC data source should be specified when the data source name is defined in the ODBC Administrator.

  For jConnect-based connections, the database should be specified in the USING clause of the CREATE SERVER statement.

  For both ODBC and JDBC based connections to Adaptive Server Anywhere, you need a separate CREATE SERVER statement for each Adaptive Server Anywhere database being accessed.

• Excel, Lotus Notes, Access     For these file-based data sources, the database name is the name of the file containing the table. Since file names can contain a period, a semicolon should be used as the delimiter between server, database, owner, and table.

Owner 

If the database supports the concept of ownership, this field represents the owner name. This field is only required when several owners have tables with the same name.

Tablename 

Tablename specifies the name of the table. In the case of an Excel spreadsheet, this is the name of the "sheet" in the workbook. If the table name is left empty, the remote table name is assumed to be the same as the local proxy table name.

Examples: 

The following examples illustrate the use of location strings:

• Adaptive Server Anywhere:

'testasa..dba.employee'

• Adaptive Server Enterprise:

'ASEServer.pubs2.dbo.publishers'

• Excel:

'excel;d:\pcdb\quarter3.xls;;sheet1$'

• Access:

'access;\\server1\production\inventory.mdb;;parts'

  Creating proxy tables using Sybase Central

You must use the Java version of Sybase Central to create remote tables, unless you choose to construct the SQL statements yourself. You cannot use the Windows version of Sybase Central.

  To create a proxy table using Sybase Central (Java):

1. Connect to the host database from Sybase Central (Java) using a JDBC connection.

2. In the left panel, open the Remote Server folder. Then open the server for which you wish to create a proxy table.

3. Double-click Add Proxy Table, and follow the instructions in the wizard:

   • On the first page, enter the remote database name. For Adaptive Server Anywhere, leave this blank.

          For more information on identifying proxy tables, see Specifying proxy table locations.

   • On the next page, enter the name and owner of the remote table, as they are specified in the remote database. Also, add a name for the proxy table. This can be different from the remote table name if you wish.

4. Click Finish to create the proxy table. You may have to refresh the display to show the proxy table.

Notes 

• The proxy table is displayed under the remote server, inside the remote servers folder.

• The proxy table also appears in database tables folder. It is distinguished from other tables by a letter P on the icon.

• You can display the column properties for the proxy table by double clicking the table.

  Using the CREATE EXISTING TABLE statement

The CREATE EXISTING TABLE statement creates a proxy table that maps to an existing table on the remote server. Adaptive Server Anywhere derives the column attributes and index information from the object at the remote location.

Example 1 

To create a proxy table named p_employee on the current server to a remote table named employee on the server named asademo1, use the following syntax:

CREATE EXISTING TABLE p_employee 
AT 'asademo1..dba.employee'

Example 2 

The following statement maps the proxy table a1 to the Microsoft Access file mydbfile.mdb. In this example, the AT keyword uses the semicolon (;) as a delimiter. The server defined for Microsoft Access is named access.

CREATE EXISTING TABLE a1 
AT'access;d:\mydbfile.mdb;;a1'

     For a full description of the CREATE EXISTING TABLE statement, see CREATE EXISTING TABLE statement.

  Using the CREATE TABLE statement

The CREATE TABLE statement creates a new table on the remote server, and defines the proxy table for that table when you use the AT option. You enter the CREATE TABLE statement using Adaptive Server Anywhere data types. Adaptive Server Anywhere automatically converts the data into the remote server's native types.

Example 

The following statement creates a table named members on the remote server asademo1, and creates a proxy table named employee that maps to the remote location:

CREATE TABLE members
( membership_id INTEGER NOT NULL,
member_name CHAR(30) NOT NULL,
office_held CHAR( 20 ) NULL)
AT 'asademo1..dba.employee'

     For a complete description of the CREATE TABLE statement, see CREATE TABLE statement.

  Listing the columns on a remote table

If you are entering a CREATE EXISTING statement and you are specifying a column list, it may be helpful to get a list of the columns that are available on a remote table. The sp_remote_columns system procedure produces a list of the columns on a remote table and a description of those data types.

sp_remote_columns servername [,tablename] [, owner ] [, database]

If a table name, owner, or database name is given, the list of columns is limited to only those that match.

For example, to get a list of the columns in the sysobjects table in the production database in an Adaptive Server Enterprise server named asetest:

sp_remote_columns  asetest, sysobjects, null, production

     For more information, see sp_remote_columns system procedure.