SAP SQL Anywhere

This section describes how to prepare and define an SAP SQL Anywhere data source in order to connect to it using the SQL Anywhere ODBC driver.

SQL Anywhere includes two database servers -- a personal database server and a network database server. For information about using SQL Anywhere, see the SQL Anywhere documentation.

Supported versions for SQL Anywhere

The SQL Anywhere ODBC driver supports connection to local and remote databases created with the following:

  • InfoMaker running on your computer

  • SQL Anywhere 17.x

  • SQL Anywhere 16.x

  • SQL Anywhere 12.x

Basic software components for SQL Anywhere

The following figure shows the basic software components required to connect to an SQL Anywhere data source in InfoMaker.

Figure: Components of an SQL Anywhere connection

Preparing to use the SQL Anywhere data source

Before you define and connect to an SQL Anywhere data source in InfoMaker, follow these steps to prepare the data source.

To prepare an SQL Anywhere data source:

  1. Make sure the database file for the SQL Anywhere data source already exists. You can create a new database by:

    • Launching the Create SQL Anywhere Database utility. This utility can be accessed from the Utilities folder for the ODBC interface in the Database profile or Database painter when InfoMaker is running on your computer.

      This method creates a local SQL Anywhere database on your computer, and also creates the data source definition and database profile for this connection. (For instructions, see the User's Guide.)

    • Creating the database some other way, such as with InfoMaker running on another user's computer or by using SQL Anywhere outside InfoMaker. (For instructions, see the SQL Anywhere documentation.)

  2. Make sure you have the log file associated with the SQL Anywhere database so that you can fully recover the database if it becomes corrupted.

    If the log file for the SQL Anywhere database does not exist, the SQL Anywhere database engine creates it. However, if you are copying or moving a database from another computer or directory, you should copy or move the log file with it.

Defining the SQL Anywhere data source

When you create a local SQL Anywhere database, InfoMaker automatically creates the data source definition and database profile for you. Therefore, you need only use the following procedure to define an SQL Anywhere data source when you want to access an SQL Anywhere database not created using InfoMaker on your computer.

To define an SQL Anywhere data source for the SQL Anywhere driver:

  1. Select Create ODBC Data Source from the list of ODBC utilities in the Database Profiles dialog box or the Database painter.

  2. Select User Data Source and click Next.

  3. On the Create New Data Source page, select the SQL Anywhere driver and click Finish.

    The ODBC Configuration for SQL Anywhere dialog box displays:

  4. You must supply the following values:

    • Data source name on the ODBC tab page

    • User ID and password on the Login tab page

    • Database file on the Database tab page

    Use the Help button to get information about boxes in the dialog box.

    Using the Browse button

    When you use the Browse button to supply the Database File name (for example, SALES.DB), this name also displays without the extension in both the Data Source Name and Database Name boxes. This might change values that you previously supplied in these boxes. If you want to specify a different name for the data source or database, you can edit one or both of these boxes after using the Browse button.

  5. (Optional) To select an ODBC translator to translate your data from one character set to another, click the Select button on the ODBC tab.

    See Selecting an ODBC translator.

  6. Click OK to save the data source definition.

Specifying a Start Line value

When the SQL Anywhere ODBC driver cannot find a running personal or network database server using the PATH variable and Database Name setting, it uses the commands specified in the Start Line field to start the database servers.

Specify one of the following commands in the Start Line field on the Database tab, where n is the version of SQL Anywhere you are using.

Specify this command

To

dbengn.exe

Start the personal database server and the database specified in the Database File box

rtengn.exe

Start the runtime database server and the database specified in the Database File box


For information on completing the ODBC Configuration For SQL Anywhere dialog box, see the SQL Anywhere documentation.

Support for Transact-SQL special timestamp columns

When you work with an SQL Anywhere table in the Data Pipeline or Database painter, the default behavior is to treat any column named timestamp as an SQL Anywhere Transact-SQL special timestamp column.

Creating special timestamp columns

You can create a Transact-SQL special timestamp column in an SQL Anywhere table.

To create a Transact-SQL special timestamp column in an SQL Anywhere table in InfoMaker:

  1. Give the name timestamp to any column having a timestamp datatype that you want treated as a Transact-SQL special timestamp column. Do this in one of the following ways:

    • In the painter -- Select timestamp as the column name. (For instructions, see the User's Guide.)

    • In a SQL CREATE TABLE statement -- Follow the CREATE TABLE example.

  2. Specify timestamp as the default value for the column. Do this in one of the following ways:

    • In the painter -- Select timestamp as the default value for the column. (For instructions, see the User's Guide.)

    • In a SQL CREATE TABLE statement -- Follow the CREATE TABLE example.

  3. If you are working with the table in the Data Pipeline painter, select the initial value exclude for the special timestamp column from the drop-down list in the Initial Value column of the workspace.

    You must select exclude as the initial value to exclude the special timestamp column from INSERT or UPDATE statements.

    For instructions, see the User's Guide.

CREATE TABLE example

The following CREATE TABLE statement defines an SQL Anywhere table named timesheet containing three columns: employee_ID (integer datatype), hours (decimal datatype), and timestamp (timestamp datatype and timestamp default value):

CREATE TABLE timesheet (
   employee_ID INTEGER,
   hours DECIMAL,
   timestamp TIMESTAMP default timestamp )

Not using special timestamp columns

If you want to change the default behavior, you can specify that InfoMaker not treat SQL Anywhere columns named timestamp as Transact-SQL special timestamp columns.

To specify that InfoMaker not treat columns named timestamp as a Transact-SQL special timestamp column:

  • Edit the SAP SQL Anywhere section of the PBODB initialization file to change the value of SQLSrvrTSName from 'Yes' to 'No'.

    After making changes in the initialization file, you must reconnect to the database to have them take effect. See Adding Functions to the PBODB Initialization File

What to do next

For instructions on connecting to the ODBC data source, see Connecting to a database.