Data Source utility (dbdsn)

Creates, deletes, describes, and lists SQL Anywhere ODBC data sources.

Syntax

dbdsn [ modifier-options ]
   { -l[ s | u ]
   | -d[ s | u ] dsn
   | -g[ s | u ] dsn
   | -w[ s | u ] dsn [details-options;...]
   | -cl }

Major option	Description
@data	Reads in options from the specified environment variable or configuration file. See Using configuration files. If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide).
-l[ s \| u ]	Lists the available SQL Anywhere ODBC data sources. You can modify the list format using the -b or -v options. On Windows, you can modify the option using the u (user) or s (system) specifiers. The default specifier is u.
-d[ s \| u ] dsn	Deletes the named SQL Anywhere data source. If you supply -y, any existing data source is deleted without confirmation. On Windows, you can modify the option using the u (user) or s (system) specifiers. The default specifier is u.
-g[ s \| u ] dsn	Lists the definition of the named SQL Anywhere data source. You can modify the format of the output using the -b or -v options. On Windows, you can modify the option using the u (user) or s (system) specifiers. The default specifier is u.
-w[ s \| u ] dsn [ details-options ]	Creates a new data source, or overwrites one if one of the same name exists. If you supply -y, any existing data source is overwritten without confirmation. On Windows, you can modify the option using the u (user) or s (system) specifiers. The default specifier is u.
-cl	Lists the connection parameters supported by the dbdsn utility. See Connection parameters. For information about supported ODBC connection parameters, see ODBC connection parameters.

Modifier-options Description

-b Formats the output of the list as a single line connection string.

-cm

Displays the command used to create the data source. This option can be used to output the creation command to a file, which can be used to add the data source to another computer or can be used to restore a data source to its original state if changes have been made to it. You must specify the -g option or -l option with -cm or the command fails. Specifying -g displays the creation command for the specified data source, while specifying -l displays the creation command for all data sources.

If the specified data source does not exist, the command to delete the data source is generated. For example, if the mydsn data source does not exist on the computer, dbdsn -cm -g mydsn would return the following command to delete the mydsn data source:

dbdsn -y -du "mydsn"

-dr

Includes the Driver parameter when displaying data sources. When you use the -cm option to recreate data sources, it allows the current version of dbdsn to create data sources that reference a different version of the ODBC driver.

For example, suppose you used the following command to create a version 9 data source:

dbdsn -y -wu "9.0 Student Sample" -c "UID=DBA;PWD=sql;...;Driver=
Adaptive Server Anywhere 9.0"

When you execute dbdsn -cm -l, dbdsn lists the same command without the Driver= parameter, which would then recreate the DSN using the SQL Anywhere version 10.0 ODBC driver.

However, if you execute dbdsn -dr -cm -l, then the Driver= parameter is included and the data source is recreated exactly as it was created originally: using the version 9 ODBC driver.

-f Displays the name of the system file that is being used. This option is only available on Unix.

-ns

Specifies that the environment variable settings are used to determine the location of the system information file (named .odbc.ini by default). This option is also useful for determining which file is being used by dbdsn when there are multiple candidates for the system information file in the environment. This option is only available on Unix.

If you do not specify -ns when creating a data source, dbdsn also checks for the system information file in the user's home directory and the path.

For more information about how the system information file is located, see Using ODBC data sources on Unix.

-o filename Writes output messages to the named file.

-or

Creates a data source for the iAnywhere Solutions Oracle driver when specified with the -c option. For example:

dbdsn -w MyOracleDSN -or -c Userid=DBA;Password=sql;
SID=abcd;ArraySize=500;ProcResults=y

You can specify the -cl option with the -or option to obtain a list of the connection parameters for the iAnywhere Solutions Oracle driver.

For more information, see iAnywhere Solutions Oracle driver.

-pe Encrypts the password in the PWD entry, and replaces the PWD entry with an ENP entry containing the encrypted password when this option is specified and a PWD entry is included in the DSN.

-q Suppresses output to the database server messages window. If you specify -q when deleting or modifying a data source, you must also specify -y.

-v Formats the output of the list over several lines, as a table.

-y Deletes or overwrites each data source without prompting you for confirmation. If you specify -q when deleting or modifying a data source, you must also specify -y.

Details-options	Description
-c "keyword=value;..."	Specifies connection parameters as a connection string. See Connection parameters.
-cw	Ensures that the DBF parameter (specified using -c) is an absolute file name. If the value of DBF is not an absolute file name, dbdsn will prepend the current working directory (CWD). This option is useful because some operating systems do not have CWD information readily available in batch files.

Remarks

The modifier options can occur before or after the major option specification.

The Data Source utility is a cross-platform alternative to the ODBC Administrator for creating, deleting, describing, and listing SQL Anywhere ODBC data sources. The utility is useful for batch operations.

Caution

Storing user IDs, passwords (encrypted or unencrypted), and/or database keys in a data source is not secure. It is recommended that you do not store this information in a data source if the database contains sensitive data.

On Windows operating systems, the data sources are held in the registry.

For information about creating a data source on Windows using the ODBC Administrator, see Creating ODBC data sources.

On Unix operating systems, data sources are held in the system information file (named .odbc.ini by default). When you use the Data Source utility to create or delete SQL Anywhere ODBC data sources on Unix, the utility automatically updates the [ODBC Data Sources] section of the system information file. If you do not specify the Driver connection parameter using the -c option on Unix, the Data Source utility automatically adds a Driver entry with the full path of the SQL Anywhere ODBC driver based on the setting of the SQLANY11 environment variable.

For more information about the system information file, see Using ODBC data sources on Unix.

Caution

You should not obfuscate the system information file (.odbc.ini) with the File Hiding utility (dbfhide) on Unix unless you will only be using SQL Anywhere data sources. If you plan to use other data sources (for example, for MobiLink synchronization), then obfuscating the system information file may prevent other drivers from functioning properly.

Exit codes are 0 (success) or non-zero (failure). See Software component exit codes.

ODBC connection parameters

The Data Source utility (dbdsn) supports the following ODBC connection parameters. Boolean (true or false) arguments are either YES or 1 if true, or NO or 0 if false.

Name	Description
Delphi	Delphi cannot handle multiple bookmark values for a row. When you set this value to NO, one bookmark value is assigned to each row, instead of the two that are otherwise assigned. Setting this option to YES can improve scrollable cursor performance.
DescribeCursor	This parameter lets you specify how often you want a cursor to be redescribed when a procedure is executed. The default setting is If Required. Never Specify 0, N, or NO if you know that your cursors do not have to be redescribed. Redescribing cursors is expensive and can decrease performance. If Required Specify 1, Y, or YES if you want the ODBC driver to determine whether a cursor must be redescribed. The presence of a RESULT clause in your procedure prevents ODBC applications from redescribing the result set after a cursor is opened. This is the default setting. Always If you specify 2, A, or ALWAYS, the cursor is redescribed each time it is opened. If you use Transact-SQL procedures or procedures that return multiple result sets, you must redescribe the cursor each time it is opened.
Description	This parameter allows you to provide a description of the ODBC data source.
Driver	This parameter allows you to specify an ODBC driver for the connection, as follows: `Driver=driver-name`. By default, the driver that is used is SQL Anywhere 11. The driver-name must be SQL Anywhere X, where X is the major version number of the software. If the driver-name does not begin with SQL Anywhere, it cannot be read by the Data Source utility (dbdsn). On Unix, this parameter specifies the fully-qualified path to the shared object. If you do not specify the Driver connection parameter on Unix, the Data Source utility automatically adds a Driver entry with the full path of the SQL Anywhere ODBC driver based on the setting of the SQLANY11 environment variable.
GetTypeInfoChar	When this option is set to YES, CHAR columns are returned as SQL_CHAR instead of SQL_VARCHAR. By default, CHAR columns are returned as SQL_VARCHAR.
InitString	InitString allows you to specify a command that is executed immediately after the connection is established. For example, you may want to set a database option or execute a stored procedure.
IsolationLevel	You can specify one of the following values to set the initial isolation level for this data source: 0 This is also called the read uncommitted isolation level. This is the default isolation level. It provides the maximum level of concurrency, but dirty reads, non-repeatable reads, and phantom rows may be observed in result sets. 1 This is also called the read committed level. This provides less concurrency than level 0, but eliminates some of the inconsistencies in result sets at level 0. Non-repeatable rows and phantom rows may occur, but dirty reads are prevented. 2 This is also called the repeatable read level. Phantom rows may occur. Dirty reads and non-repeatable rows are prevented. 3 This is also called the serializable level. This provides the least concurrency, and is the strictest isolation level. Dirty reads, non-repeatable reads, and phantom rows are prevented. snapshot You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistences are possible and performance is the same as isolation level 0. statement-snapshot You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistences are possible and performance is the same as isolation level 0. readonly-statement-snapshot This is also called the isolation level. You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistences are possible and performance is the same as isolation level 0. For more information, see Choosing isolation levels.
KeysInSQLStatistics	Specify YES if you want the SQLStatistics function to return foreign keys. The ODBC specification states that SQLStatistics should not return primary and foreign keys; however, some Microsoft applications (such as Visual Basic and Access) assume that primary and foreign keys are returned by SQLStatistics.
LazyAutocommit	Setting this parameter to YES delays the commit operation until a statement closes.
PrefetchOnOpen	When PrefetchOnOpen is set to YES, a prefetch request is sent with a cursor open request. The prefetch eliminates a network request to fetch rows each time a cursor is opened. Columns must already be bound for the prefetch to occur on the open. This connection parameter can help reduce the number of client/server requests to help improve performance over a LAN or WAN.
PreventNotCapable	The SQL Anywhere ODBC driver returns a `Driver not capable` error because it does not support qualifiers. Some ODBC applications do not handle this error properly. Set this parameter to YES to prevent this error code from being returned, allowing these applications to work.
SuppressWarnings	Set this parameter to YES if you want to suppress warning messages that are returned from the database server on a fetch. Versions 8.0.0 and later of the database server return a wider range of fetch warnings than earlier versions of the software. For applications that are deployed with an earlier version of the software, you can select this option to ensure that fetch warnings are handled properly.
TranslationDLL	This option is provided for backward compatibility. The use of translators is not recommended.
TranslationName	This option is provided for backward compatibility. The use of translators is not recommended.
TranslationOption	This option is provided for backward compatibility. The use of translators is not recommended.

Examples

Write a definition of the data source newdsn. Do not prompt for confirmation if the data source already exists.

dbdsn -y -w newdsn -c "UID=DBA;PWD=sql;LINKS=TCPIP;ENG=myserver"

or, with a different option order,

dbdsn -w newdsn -c "UID=DBA;PWD=sql;LINKS=TCPIP;ENG=myserver" -y

List all known user data sources, one data source name per line:

dbdsn -l

List all known system data sources, one data source name per line:

dbdsn -ls

List all data sources along with their associated connection string:

dbdsn -l -b

Report the connection string for the user data source MyDSN:

dbdsn -g MyDSN

Report the connection string for the system data source MyDSN:

dbdsn -gs MyDSN

Delete the data source BadDSN, but first list the connection parameters for BadDSN and prompt for confirmation:

dbdsn -d BadDSN -v

Delete the data source BadDSN without prompting for confirmation.

dbdsn -d BadDSN -y

Create a data source named NewDSN for the database server MyServer:

dbdsn -w NewDSN -c "UID=DBA;PWD=sql;ENG=MyServer"

If NewDSN already exists, you are prompted to confirm overwriting the data source.

List all connection parameter names and their aliases:

dbdsn -cl

List all user data sources:

dbdsn -l -o dsninfo.txt

List all connection parameter names:

dbdsn -cl -o dsninfo.txt

Specify an absolute file name. When the DSN is created, it will contain DBF=c:\SQLAnywhere11\my.db.

c:\SQLAnywhere11> dbdsn -w testdsn -cw -c UID=DBA;PWD=sql;ENG=SQLAny;DBF=my.db

Generate the command to create the SQL Anywhere 11 Demo data source and output it to a file called restoredsn.bat:

dbdsn -cm -gs "SQL Anywhere 11 Demo" > restoredsn.bat

The restoredsn.bat file contains the following:

dbdsn -y -ws "SQL Anywhere 11 Demo" -c "UID=DBA;PWD=sql;
DBF='C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\demo.db';
ENG=demo11;START='C:\Program Files\SQL Anywhere 11\bin32\dbeng11.exe';
ASTOP=yes;Description='SQL Anywhere 11 Sample Database'"

Return the location of the system information file on Unix:

dbdsn -f

This command returns the following output:

dbdsn using /home/user/.odbc.ini

Change the location of the system information file:

export ODBCINI=./myodbc.ini

Verify the new location of the system information file using dbdsn -f:

dbdsn using ./myodbc.ini

Use the -ns option when creating the data source:

dbdsn -w NewDSN -c "UID=DBA" -ns

This results in the following output:

Configuration "newdsn" written to file ./myodbc.ini

Discuss this page in DocCommentXchange.
Send feedback about this page using email.