User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 29. Server Classes for Remote Data Access       

ODBC-based server classes

The ODBC-based server classes are:

• asaodbc

• aseodbc

• db2odbc

• mssodbc

• oraodbc

• odbc

  Defining ODBC external servers

    The most common way of defining an ODBC-based server is to base it on an ODBC data source. To do this, you must create a data source in the ODBC Administrator.

Once you have the data source defined, the USING clause in the CREATE SERVER statement should match the ODBC data source name.

For example, to configure a DB2 server named mydb2 whose Data Source Name is also mydb2, use:

CREATE SERVER mydb2 
CLASS 'db2odbc' 
USING 'mydb2'

     For more information on creating data sources, see Creating an ODBC data source.

Using connection strings instead of data sources 

An alternative, which avoids using data sources, is to supply a connection string in the USING clause of the CREATE SERVER statement.

To do this, you must know the connection parameters for the ODBC driver you are using. For example, a connection to an ASA may be as follows:

CREATE SERVER testasa 
CLASS 'asaodbc'
USING 'driver=adaptive server anywhere 6.0;eng=testasa;dbn=sample;links=tcpip{}'

This defines a connection to an Adaptive Server Anywhere database server named testasa, database sample, and using the TCP-IP protocol.

See also 

For information specific to particular ODBC server classes, see:

• Server class asaodbc

• Server class aseodbc

• Server class db2odbc

• Server class oraodbc

• Server class mssodbc

• Server class odbc

  Server class asaodbc

A server with server class asaodbc is:

• Adaptive Server Anywhere version 5.5 or later

No special requirements exist for the configuration of an Adaptive Server Anywhere data source.

The ODBC driver for version 6 databases is installed when you install Adaptive Server Anywhere. To access version 5 servers, install the version 5 ODBC driver. You cannot use the version 6 ODBC driver to connect to a version 5 Adaptive Server Anywhere.

To access Adaptive Server Anywhere servers that support multiple databases, create an ODBC data source name defining a connection to each database. Issue a CREATE SERVER statement for each of these ODBC data source names.

  Server class aseodbc

A server with server class aseodbc is:

• Adaptive Server Enterprise

• SQL Server (version 10 and later)

Adaptive Server Anywhere requires the installation of the Adaptive Server Enterprise ODBC driver and Open Client connectivity libraries to connect to a remote Adaptive Server with class aseodbc. However, the performance is better than with the asejdbc class.

Notes 

• Open Client should be version 11.1.1, EBF 7886 or above. Install Open Client and verify connectivity to the Adaptive Server before you install ODBC and configure Adaptive Server Anywhere. The Sybase ODBC driver should be version 11.1.1, EBF 7911 or above.

• Configure a User Data Source in the Configuration Manager with the following attributes:

  • Under the General tab:

    Enter any value for Data Source Name. This value is used in the USING clause of the CREATE SERVER statement.

    Server Name should match the name of the server in the Sybase interfaces file.

  • Under the Advanced tab, check the Application Using Threads box and check the Enable Quoted Identifiers box.

  • Under the Connection tab:

    Set the charset field to match your Adaptive Server Anywhere character set.

    Set the language field to your preferred language for error messages.

  • Under the Performance tab:

    Set Prepare Method to "2-Full."

    Set Fetch Array Size as large as possible for best performance. This increases memory requirements since this is the number of rows that must be cached in memory. Sybase recommends using a value of 100.

    Set Select Method to "0-Cursor."

    Set Packet Size to as large as possible. Sybase recommends using a value of -1.

    Set Connection Cache to 1.

  Data type conversions

When you issue a CREATE TABLE statement, Adaptive Server Anywhere automatically converts the data types to the corresponding Adaptive Server Enterprise data types. The following table describes the Adaptive Server Anywhere to Adaptive Server Enterprise data type conversions.

Adaptive Server Anywhere data type	Adaptive Server Enterprise default data type
bit	bit
tinyint	tinyint
smallint	smallint
int	int
integer	integer
decimal [defaults p=30, s=6]	numeric(30,6)
decimal(128,128)	not supported
numeric [defaults p=30 s=6]	numeric(30,6)
numeric(128,128)	not supported
float	real
real	real
double	float
smallmoney	numeric(10,4)
money	numeric(19,4)
date	datetime
time	datetime
timestamp	datetime
smalldatetime	datetime
datetime	datetime
char(n)	varchar(n)
character(n)	varchar(n)
varchar(n)	varchar(n)
character varying(n)	varchar(n)
long varchar	text
text	text
binary(n)	binary(n)
long binary	image
image	image
bigint	numeric(20,0)

  Server class db2odbc

A server with server class db2odbc is:

• IBM DB2

Notes 

• Sybase certifies the use of IBM's DB2 Connect version 5, with fix pack WR09044. Configure and test your ODBC configuration using the instructions for that product. Adaptive Server Anywhere has no specific requirements on configuration of DB2 data sources.

• The following is an example of a CREATE EXISTING TABLE statement for a DB2 server with an ODBC data source named mydb2:

  CREATE EXISTING TABLE ibmcol 
  AT 'mydb2..sysibm.syscolumns'

  Data type conversions

When you issue a CREATE TABLE statement, Adaptive Server Anywhere automatically converts the data types to the corresponding DB2 data types. The following table describes the Adaptive Server Anywhere to DB2 data type conversions.

Adaptive Server Anywhere data type	DB2 default data type
bit	smallint
tinyint	smallint
smallint	smallint
int	int
integer	int
bigint	decimal(20,0)
char(1-254)	varchar(n)
char(255-4000)	varchar(n)
char(4001-32767)	long varchar
character(1-254)	varchar(n)
character(255-4000)	varchar(n)
character(4001-32767)	long varchar
varchar(1-4000)	varchar(n)
varchar(4001-32767)	long varchar
character varying(1-4000)	varchar(n)
character varying(4001-32767)	long varchar
long varchar	long varchar
text	long varchar
binary(1-4000)	varchar for bit data
binary(4001-32767)	long varchar for bit data
long binary	long varchar for bit data
image	long varchar for bit data
decimal [defaults p=30, s=6]	decimal(30,6)
numeric [defaults p=30 s=6]	decimal(30,6)
decimal(128, 128)	NOT SUPPORTED
numeric(128, 128)	NOT SUPPORTED
real	real
float	float
double	float
smallmoney	decimal(10,4)
money	decimal(19,4)
date	date
time	time
smalldatetime	timestamp
datetime	timestamp
timestamp	timestamp

  Server class oraodbc

A server with server class oraodbc is:

• Oracle version 8.0 or later

Notes 

• Sybase certifies the use of version 8.0.03 of Oracle's ODBC driver. Configure and test your ODBC configuration using the instructions for that product.

• The following is an example of a CREATE EXISTING TABLE statement for an Oracle server named myora:

  CREATE EXISTING TABLE employees
  AT 'myora.database.owner.employees'

• Due to Oracle ODBC driver restrictions, you cannot issue a CREATE EXISTING TABLE for system tables. A message returns stating that the table or columns cannot be found.

  Data type conversions

When you issue a CREATE TABLE statement, Adaptive Server Anywhere automatically converts the data types to the corresponding Oracle data types. The following table describes the Adaptive Server Anywhere to Oracle data type conversions.

Adaptive Server Anywhere data type	Oracle data type
bit	number(1,0)
tinyint	number(3,0)
smallint	number(5,0)
int	number(11,0)
bigint	number(20,0)
decimal(prec, scale)	number(prec, scale)
numeric(prec, scale)	number(prec, scale)
float	float
real	real
smallmoney	numeric(13,4)
money	number(19,4)
date	date
time	date
timestamp	date
smalldatetime	date
datetime	date
char(n)	if (n > 255) long; else varchar(n)
varchar(n)	if (n > 2000) long; else varchar(n)
longchar	long
binary(n)	if (n > 255) long raw else raw(n)
varbinary(n)	if (n > 255) long raw else raw(n)
longbinary	long raw

  Server class mssodbc

A server with server class mssodbc is:

• Microsoft SQL Server version 6.5, Service Pack 4.

Notes 

• Sybase certifies the use of version 3.60.0319 of Microsoft SQL Server's ODBC driver (included in MDAC 2.0 release). Configure and test your ODBC configuration using the instructions for that product.

• The following is an example of a CREATE EXISTING TABLE statement for a Microsoft SQL Server named mymssql:

  CREATE EXISTING TABLE accounts, 
  AT 'mymssql.database.owner.accounts'

  Data type conversions

When you issue a CREATE TABLE statement, Adaptive Server Anywhere automatically converts the data types to the corresponding Microsoft SQL Server data types. The following table describes the Adaptive Server Anywhere to Microsoft SQL Server data type conversions.

Adaptive Server Anywhere Data type	Microsoft SQL Server Default Data type
bit	bit
tinyint	tinyint
smallint	smallint
int	int
bigint	numeric(20,0)
decimal [defaults p=30, s=6]	decimal(prec, scale)
numeric [defaults p=30 s=6]	numeric(prec, scale)
float	if (prec) float(prec) else float
real	real
smallmoney	smallmoney
money	money
date	datetime
time	datetime
timestamp	datetime
smalldatetime	datetime
datetime	datetime
char(n)	if (length > 255) text else varchar(length)
character(n)	char(n)
varchar(n)	if (length > 255) text else varchar(length)
longchar	text
binary(n)	if (length > 255) image else binary(length)
long binary	image
double	float

  Server class odbc

Server class odbc is used for ODBC data sources that do not have their own server class. Any ODBC driver that complies with ODBC version 2.0 compliance level 1 or higher can be used. Sybase certifies the following ODBC data sources:

• Microsoft Excel

• Microsoft Access

• Microsoft Foxpro

• Lotus Notes

The latest versions of Microsoft ODBC drivers can be obtained through the Microsoft Data Access Components (MDAC) distribution found at www.microsoft/data/download.htm. The Microsoft driver versions listed below are part of MDAC 2.0.

The following sections provide notes on accessing these data sources.

  Microsoft Excel (Microsoft 3.51.171300)

With Excel, each Excel workbook is logically considered to be a database holding several tables. Tables are mapped to sheets in a workbook. When you configure an ODBC data source name in the ODBC driver manager, you specify a default workbook name associated with that data source, however when you issue a CREATE TABLE statement, you can override the default and specify a workbook name in the location string. This allows you to use a single ODBC DSN to access all of your excel workbooks.

In this example, an ODBC data source named excel was created. To create a workbook named work1.xls with a sheet (table) called mywork:

CREATE TABLE mywork (a int, b char(20)) 
AT'excel;d:\work1.xls;;mywork'

To create a second sheet (or table) execute a statement such as:

CREATE TABLE mywork2 (x float, y int) 
AT 'excel;d:\work1.xls;;mywork2'

Existing worksheets can be imported into Adaptive Server Anywhere using CREATE EXISTING. It is expected that the first row of your spreadsheet contains column names.

CREATE EXISTING TABLE mywork 
AT'excel;d:\work1;;mywork'

If Adaptive Server Anywhere reports that the table is not found, you may need to explicitly state the column and row range that you wish to map to. For example:

CREATE EXISTING TABLE mywork 
AT 'excel;d:\work1;;mywork$'

Adding the $ to the sheet name indicates that the entire worksheet should be selected.

Note in the location string specified by AT that a semicolon is used instead of a period for field separators This is because periods occur in the file names. Excel does not support the owner name field so leave this blank.

Deletes are not supported. Also some updates may not be possible since the Excel driver does not support positioned updates.

  Microsoft Access (Microsoft 3.51.171300)

Access databases are stored in a .mdb file. Using the ODBC manager, create an ODBC data source and map it to one of these files. A new .mdb file can be created through the ODBC manager. This database file will be used as the default if one is not specified when you create a table through Adaptive Server Anywhere.

Assuming an ODBC data source named access.

CREATE TABLE tab1 (a int, b char(10)) 
AT 'access...tab1'

or

CREATE TABLE tab1 (a int, b char(10)) 
AT 'access;d:\pcdb\data.mdb;;tab1'

or

CREATE EXISTING TABLE tab1 
AT 'access;d:\pcdb\data.mdb;;tab1'

Access does not support the owner name qualification, leave it empty.

  Microsoft Foxpro (Microsoft 3.51.171300)

Foxpro tables can be stored together inside a single foxpro database file (.dbc) or each table can be stored in its own separate .dbf file. When using .dbf files, be sure the file name is filled into the location string, otherwise the directory that Adaptive Server Anywhere was started in will be used.

CREATE TABLE fox1 (a int, b char(20)) 
AT 'foxpro;d:\pcdb;;fox1'

This statement will create a file named d:\pcdb\fox1.dbf when the "free table directory" option is chosen in the odbc driver manager.

  Lotus Notes SQL 2.0 (2.04.0203))

This driver can be obtained from the .

Read the documentation that comes with this driver for an explanation of how Notes data is mapped to relational tables. You can easily map Adaptive Server Anywhere tables to Notes forms.

Here is how to set up Adaptive Server Anywhere to access the Address sample file.

• Create an ODBC data source using the NotesSQL driver. The database will be the sample names file: c:\notes\data\names.nsf. The Map Special Characters option should be turned on. For this example, the Data Source Name is my_notes_dsn.

• Create a server in Adaptive Server Anywhere:

  CREATE SERVER names 
  CLASS 'odbc' 
  USING 'my_notes_dsn'

• Map the Person form into an Adaptive Server Anywhere table:

  CREATE EXISTING TABLE Person 
  AT 'names...Person'

• Query the table

  SELECT * FROM Person 

Avoiding password prompts 

Lotus Notes does not support sending a user name and password through the ODBC API. If you try to access Lotus notes using a password protected ID, a window appears on the machine where Adaptive Server Anywhere is running prompting you for a password. This behavior is not desirable for a multi-user server.

To access Lotus Notes unattended, without ever receiving a password prompt, you must use a non-password-protected ID. You can remove password protection from your ID by clearing it (File->Tools->User ID->Clear Password), unless your Domino administrator required a password to be used when your ID was created. In this case, you will not be able to clear it.