To work with your data, a connection must be established between a client application and a database server (serving a database or database file(s)). You can connect to an Adaptive Server Anywhere database via any of the following interfaces: ODBC, JDBC, OLE DB or ADO, embedded SQL, Sybase Open Client, or HTTP/HTTPS (web services).
Note: Adaptive Server Anywhere tools respect and can use ODBC data sources for connections.
Connecting via ODBC
ODBC (which stands for Open Data Base Connectivity) is an industry-standard interface to relational database management systems (RDBMSs). An ODBC data source encapsulates the information required to connect to a database. Adaptive Server Anywhere supports the ODBC interface on all platforms. ODBC data sources can be used to connect to Adaptive Server Anywhere from ODBC client applications, such as Sybase Central and Interactive SQL.
If the client computer is running Linux or another UNIX operating system, ODBC data sources can be used to connect with ODBC applications, as well as for Sybase Central and Interactive SQL.
Understanding ODBC data sources
Data sources exist on the client computer. You need one for each database accessible via ODBC. They reside in the .odbc.ini file or in a separate .dsn file.
ODBC clients look for .odbc.ini in the following locations:
- The $ODBCINI, $ODBC_INI, $ODBCHOME, and $HOME environment variables
- The current directory
- The userâ€™s home directory
- The executable path
- The $PATH, $SYBASE, $LD_LIBRARY_PATH (library path), and $ASANY9 (installation directory) directories
- The root directory
Note: It is possible to connect to databases without a data source by specifying all of the connection parameters in the connection string.
Using threaded versus non-threaded client libraries
Both threaded and non-threaded client libraries are provided with Adaptive Server Anywhere. It is important that your application use the correct library. The threaded libraries all have names ending in _r. If your application is using the ODBC interface, it is strongly recommended that you use the libdbodbc9 library shipped with SQL Anywhere Studio 9, which automatically loads the appropriate non-threaded (libdbodbc9_n) or threaded (libdbodbc9_r) ODBC library, based on the applicationâ€™s thread needs. There is very little overhead added by using the libdbodbc9 library. You have the option of referencing either the threaded or non-threaded libraries directly, but if you do, you must ensure that you are using the correct one.
Installing the Adaptive Server Anywhere ODBC driver
To connect to Adaptive Server Anywhere from ODBC applications on Linux, install the Adaptive Server Anywhere driver. With the driver installed, you can create an ODBC data source (DSN) that uses this driver. You can do this by using a third-party ODBC driver manager, or the Adaptive Server Anywhere driver itself can be configured to act as a driver manager. However, choosing to use the driver as a driver manager limits you to connecting only to an Adaptive Server Anywhere data source.
Using a true driver manager allows you to manage any number of drivers and data sources. Two driver managers that are widely available are unixODBC and iODBC. Instructions for setting up one of these driver managers for use with the Adaptive Server Anywhere 9.0.2 driver follow.
Driver managers and Adaptive Server Anywhere
Using unixODBC and Adaptive Server Anywhere
unixODBC is a widely-available ODBC driver manager on Linux. If you are using a different driver manager, the principles should still apply, but consult your driver manager documentation for details.
To configure the Adaptive Server Anywhere driver for unixODBC
- Ensure unixODBC is installed correctly on your system.
See the unixODBC documentation for your Linux platform for details.
- If you have not already done so, source the Adaptive Server Anywhere asa_config.* shell script.
- Search for and comment out the DataDirect driver manager section of the asa_config.* script, which is installed by default, and is used to manage ODBC specifically for the SQL Anywhere Studio MobiLink product. The asa_config.* shell script is located in asany/bin32. In the following section of the asa_config.* shell script, comment (add #) the following lines to remove the ODBC drivers/manager from your library path:
# export LD_LIBRARY_PATH
- If you do not have read/write permissions to the asademo.db file located in your installation directory (/opt/sybase/SYBSa9), copy it to an appropriate directory such as /tmp, using the following command:
cp /opt/sybase/SYBSsa9/asademo.db /tmp i
- To configure unixODBC to use the Adaptive Server Anywhere driver, you need root access or its equivalent. Run the following command:
sudo ODBCConfig . ii
- Select the Drivers tab and then click Add.
- Enter a suitable name for the driver. For example, ASA 9.0 Driver.
- Enter the library for Setup and Driver entries. These entries are the same for both. Assuming the default installation directory was used, the location is /opt/sybase/SYBSsa9/lib/libdbodbc9.so.
- Click the checkmark (located in the top left corner) and then click OK.
Creating data sources with unixODBC
Now that you have configured a driver through the driver manager, you can create an ODBC data source. This can be either a system data source, or a user data source. System data sources are created for system-wide use, and must be created by root. User data sources are created by and for individual users.
When you use the driver manager to create an ODBC data source, it adds the data source to the odbc.ini file if it exists, or creates the file if it does not.
Creating a system data source
To set a system ODBC data source for Adaptive Server Anywhere using unixODBC
- Run ODBCConfig or gODBCConfig as root.
Note: To configure a system data source in unixODBC, you need the equivalent of root access. Run the following command: sudo ODBCConfig or gODBCConfig iii .
- On the System DSN tab, click Add.
- Select ASA 9.0 Driver and then click OK. A dialog appears with Adaptive Server Anywhere connection parameters. If you get the error "Cannot construct property list", update your Adaptive Server Anywhere ODBC driver to version 9.0.2.
Note: The best way to update your current Adaptive Server Anywhere installation, including the ODBC driver, is by applying an appropriate iAnywhere update such as an EBF and/or Maintenance update that is appropriate to your current installationâ€™s version and build. Unless there is a specific reason not to, it is recommended you update to the latest version and build available.
- Enter the name of the data source (ASA), the user ID (DBA), the password (SQL), and the database file location of asademo.db. (/tmp/asademo.db or /opt/sybase/SYBSsa9/asademo.db).
- Click the checkmark.
Creating a user data source
To set up a user ODBC data source for Adaptive Server Anywhere 9.0.2 using unixODBC, repeat the above procedure, however step 2 changes to the following: On the User DSN tab, click Add.
Using unixODBC for additional ODBC testing and troubleshooting
Performing an ODBC test using unixODBC
The unixODBC SDK has an ODBC utility called ODBC Test that can be used for testing and troubleshooting ODBC connections. This utility is based on the ODBC test program supplied by Microsoft. You can start ODBC TEST with the following command:
After you execute this command, you will see the following (or a similar) interface providing a variety of ODBC test functionality (this functionality is beyond the scope of this document- see the documentation for unixODBC and Microsoft ODBC test documentation for details).
Performing an ODBC trace using unixODBC
An ODBC trace can be performed using unixODBC to help trace OBDC connections.
To perform an ODBC trace on a connection
- If you executed ODBCConfig, then on the Advanced tab (or the Tracing tab if you ran gODBCConfig), select the Enabled check box, ensure an appropriate file has been entered, and then click Apply and then click OK.
- Connect using your ODBC data source.
- When you have disconnected the ODBC data source, you can view the trace log file for details of the ODBC connection:
Creating data sources with the dbdsn utility
As an alternative to using the unixODBC driver manager to create ODBC data sources, Adaptive Server Anywhere includes a data source utility called dbdsn. The following is an example that shows how to use dbdsn:
dbdsn -w "ASATestDSN" -c "uid=dba;pwd=sql;dbf=/tmp/asademo.db"
To control what file dbdsn writes to, set the ODBCINI environment variable. For more information about this environment variable, see .odbc.ini and Understanding ODBC data sources.
The dbdsn utility is not a driver manager. Rather, the Data Source utility is a cross-platform alternative to the ODBC Administrator for creating, deleting, describing, and listing Adaptive Server Anywhere ODBC data sources.
For more information about the dbdsn utility, see the Adaptive Server Anywhere Database Administration Guide, Database Administration Utilities, The Data Source utility, in the SQL Anywhere Studio documentation.
The Adaptive Server Anywhere driver still needs to be installed and managed by a driver manager, or configured to act like a driver manager as outlined earlier. The dbdsn utility is used to configure data sources after the drivers have been installed and configured for use.
Testing ODBC connections
Testing data sources using Interactive SQL
- At a command prompt, execute the following command:
dbisql -c "dsn=ASA 9 Sample DB" iv
- Execute the following query in Interactive SQL:
SELECT * FROM department
The following data appears:
- Press F12 to disconnect.
- Close Interactive SQL.
Note: It is possible to do the above in one step and with no GUI using the -nogui option in your Interactive SQL command:
dbisql -c "dsn=ASA 9 Sample DB" -nogui "SELECT * FROM department"
Connecting to Sybase Central
Like Interactive SQL, Sybase Central, can be used to connect to your database using the newly-created ODBC data source. Once connected, you have a large number of features that you can use. See your SQL Anywhere Studio documentation for information about the functionality available in Sybase Central.
The following shows Sybase Central after connecting to the asademo.db sample database through the ASA 9 Sample DB ODBC data source:
Building the Adaptive Server Anywhere ODBC sample
To build the Adaptive Server Anywhere ODBC Sample
- Ensure you have the required compiler tools installed (GNU or a native compiler).
- This sample expects the data source name ASA 9.0 Sample. If you do not have this data source set up on your build machine, you will need to create one. For information about creating this data sources, see Creating data sources with unixODBC and Creating data sources with the dbdsn utility.
- Change to the samples directory:
- If you have version 9.0.2 (or later) installed, run makeall and proceed directly to step 5. Otherwise, set the ODBC and PLATFORM environment variables for the make file:
- For bash shells execute the following command:
export ODBC=/usr; export PLATFORM=linux
- For cshell shells execute the following command:
setenv ODBC /usr; setenv PLATFORM linux
- Build the sample using the following command:
make -f makeall odbc
- Run the resulting sample using the following command:
Configuring the Adaptive Server Anywhere ODBC driver as a driver manager
You can use the Adaptive Server Anywhere ODBC driver as a driver manager if you will only be connecting to Adaptive Server Anywhere databases. However, doing this will prevent you from loading any driver other than the Adaptive Server Anywhere driver.
To use Adaptive Server Anywhere as a driver manager, you need to create symbolic links so that ODBC driver manager requests get routed to the iAnywhere ODBC driver. From the sybase/SYBSsa9/lib subdirectory, execute the following commands:
ln -s libdbodbc9.so libodbc.so
ln -s libdbodbc9.so.1 libodbc.so.1
The sample ODBC program finds these links in the LD_LIBRARY_PATH.
Connecting to an ODBC data source
Once you have created an ODBC data source with dbdsn or a driver manager, you can access it through the DataSourceName (DSN) connection string keyword.
For an ODBC data source called mydatasrc, for example, you would use the following connection string to connect to the database associated with it:
dbisqlc -c "dsn=mydatasrc"
Note: Explicitly-provided connection parameters and SQLCONNECT override any parameters provided in the ODBC data source, in that order. v
i Other suggestions include /home/username/DB.
ii You can also run sudo gODBCConfig.
For both executables, you will be prompted to enter the password appropriate to this user. gODBCConfig is the GTK equivalent of ODBCConfig.
iii You can also run sudo gODBCConfig. For both executables, you will be prompted to enter the password appropriate to this user. See step 5 in the previous section for full details.
iv You can also simply enter dbisql, and you will be prompted with a Connect dialog. In this case, you select the ODBC Data Source name radio button, type ASA 9 Sample DB, and then click OK.
v Connection strings are frequently used when performing actions on a database. They consist of a list of parameter settings, delimited by semicolons and enclosed in double quotes. There should be no extra spaces in a connection string. For example:
The short strings of letters just before each equal sign (in this example, uid, pwd, and dbf) are called keywords, which each correspond to a connection parameter. There are many connection parameters available, and they are listed in the Connecting to a Database chapter of the Adaptive Server Anywhere Database Administration Guide. They are also described in detail in the Connection and Communication Parameters chapter of the Adaptive Server Anywhere Database Administration Guide.
When Adaptive Server Anywhere utilities are looking for connection parameters, they check the SQLCONNECT environment variable for any parameters that were left out of the connection string. If you are putting connection parameters into the SQLCONNECT environment variable, replace the equal signs with number (#) signs. For example, in bash you would use the following command:
To make SQLCONNECT available in subsequent shells, you'd need to use export SQLCONNECT to export the SQLCONNECT variable to the environment.
The single quotes are necessary in the above command because semicolons can be used to separate bash commands. You can also use double quotes.
You may also want to put these commands into your .bash_profile (or .profile, if you're using another shell) if you want the same connection parameters to be available each time you log in.