iAnywhere.com - Managing ODBC data sources using the dbdsn command-line utility

Managing ODBC data sources using the dbdsn command-line utility

Syntax

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

Parameters

Major option	Description
@data	Read options from the specified environment variable or configuration file.
-l[ u \| s ] [ -qq ]	List either all Adaptive Server Anywhere user or system data sources. By default, user data sources are listed. Using -qq with this option lists the DSNs without any banner or titles.
-d[ u \| s ] dsn	Delete the named Adaptive Server Anywhere user or system data source. User data sources is the default.
-g[ u \| s ] dsn	List (get) details about the named Adaptive Server Anywhere user or system data source. User data sources is the default.
-w[ u \| s ] dsn [ details-options ]	Create (write) a user or system data source definition. User data sources is the default.
-cl[ -qq ]	List available connection parameters. Using -qq with this option lists the available connection parameters without any banner or titles.

Modifier-options	Description
-b	Brief. Print connection string for the data source.
-cm	Display the data source creation command.
-q	Quiet. Do not print banner.
-v	Verbose. Print connection parameters in tabular form.
-y	Delete or overwrite data source without confirmation.

Details-options	Description
-cw	Ensure that the DBF parameter (specified using -c) is an absolute filename. If the value of DBF is not an absolute filename, the Data Source utility prepends the current working directory (CWD).
-c "keyword=value;..."	Supply database connection parameters.
-ec encryption type	Encrypt all network packets.
-o filename	Write client messages to filename.
-p size	Set maximum network packet size.
-r	Disable multiple record fetching.
-tl seconds	Client liveness timeout period.
-x list	List network drivers to run.
-z	Display debugging information.
server-name	Connect to named database server.

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. The utility is useful for batch operations. On Windows operating systems, the data sources are held in the registry.

On UNIX operating systems, the data sources are held in the .odbc.ini file. When you use the Data Source utility to create or delete Adaptive Server Anywhere ODBC data sources on UNIX, the utility automatically updates the [ODBC Data Sources] section of the .odbc.ini 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 Adaptive Server Anywhere ODBC driver based on the setting of the ASANY9 environment variable.

For more information about the .odbc.ini file, see The system information file (.odbc.ini).

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

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

Exit codes are 0 (success) or non-zero (failure).

Data Source utility options

When you use the Data Source utility, you can choose from major options, modifier options, and details options.

Major options

@data

Use this option to read in options from the specified environment variable or configuration file. If both exist with the same name, the environment variable is used.

For more information about configuration files, 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.

For more information, see Hiding the contents of files using the dbfhide command-line utility.

List available connection parameters (-cl)

This convenience option lists the connection parameters supported by the dbdsn utility.

For descriptions of the Adaptive Server Anywhere connection parameters supported by the Data Source utility (dbdsn), see 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 Adaptive Server Anywhere 9.0. The driver-name must be Adaptive Server Anywhere X.0, where X is the major version number of the software. If the driver-name does not begin with Adaptive Server 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 Adaptive Server Anywhere ODBC driver based on the setting of the ASANY9 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 with 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. 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 Adaptive Server 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 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 backwards compatibility. The use of translators is not recommended.
TranslationName	This option is provided for backwards compatibility. The use of translators is not recommended.
TranslationOption	This option is provided for backwards compatibility. The use of translators is not recommended.

Delete the named data source (-d)

Deletes the named Adaptive Server Anywhere data source. You can modify the option using the u (user) or s (system) specifiers. The default specifier is u. If you supply -y, any existing data source is deleted without confirmation.

List (get) details of the named data source (-g)

List the definition of the named Adaptive Server Anywhere data source. You can modify the format of the output using the -b or -v options. You can modify the option using the u (user) or s (system) specifiers. The default specifier is u.

List defined data sources (-l)

Lists the available Adaptive Server Anywhere ODBC data sources. You can modify the list format using the -b or -v options. You can modify the option using the u (user) or s (system) specifiers. The default specifier is u.

Create (write) a data source definition (-w)

Creates a new data source, or overwrites one if one of the same name exists. You can modify the option using the u (user) or s (system) specifiers. The default specifier is u. If you supply -y, any existing data source is overwritten without confirmation.

Modifier options

Print connection string for the data source (-b) Format the output of the list as a single line connection string.

Display the data source creation command (-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 machine 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 machine, dbdsn -cm -g mydsn would return the following command to delete the mydsn data source:

dbdsn -y -du "mydsn"

Do not print banner (-q)

Suppress the informational banner. If you specify -q when deleting or modifying a data source, you must also specify -y.

Do not print banner or titles (-qq)

Suppress both the informational banner and titles. This option can only be used with the -l and the -cl options.

Print connection parameters in tabular form (-v)

Format the output of the list over several lines, as a table.

Delete or overwrite data source without confirmation (-y)

Automatically delete or overwrite 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

Connection parameters (-c)

Specify connection parameters as a connection string.

For more information, see Connection parameters.

Absolute filenames (-cw)

Ensure that the DBF parameter (specified using -c) is an absolute filename. If the value of DBF is not an absolute filename, dbdsn will prepend the current working directory (CWD). This option is useful because some operating systems (Win 9x) do not have CWD information readily available in batch files.

Encrypt network packets (-ec)

Encrypt packets sent between the client application and the server.

For more information, see Encryption connection parameter [ENC].

Log output messages to file (-o)

Write output messages to the named file. By default, messages are written to the console.

For more information, see Logfile connection parameter [LOG].

Set maximum network packet size (-p)

The maximum packet size for network communications, in bytes. The value must be greater than 300, and less than 16000. The default setting is 1460.

For more information, see CommBufferSize connection parameter [CBSIZE].

Disable multiple-record fetching (-r)

By default, when the database server gets a simple fetch request, the application asks for extra rows. You can disable this behavior using this option.

For more information, see DisableMultiRowFetch connection parameter [DMRF].

Set client liveness timeout (-tl)

Terminates connections when they are no longer intact. The value is in seconds.

The default is server setting, which in turn has a default value of 120 seconds.

For more information, see LivenessTimeout connection parameter [LTO].

Set communications links (-x)

A comma separated list of network drivers to run.

For more information, see CommLinks connection parameter [LINKS].

Display debugging information (-z)

Provide diagnostic information on communications links on startup.

server-name

Connect to the named server. Only the first 40 characters are used.

For more information, see The Database Server.

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" -v

or, with a different option order,

dbdsn -w newdsn -c "uid=DBA;pwd=SQL;LINKS=TCPIP" -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 user data source MyDSN:

dbdsn -g MyDSN

Report the connection string for 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 a NewDSN already exists, the previous definition is overwritten.

List all connection parameter names and their aliases:

dbdsn -cl

List all user DSNs (without banner and titles):

dbdsn -l -qq

List all connection parameter names (without banner and titles):

dbdsn -cl -qq

Specify an absolute filename. When the DSN is created, it will contain dbf=E:\asa90\my.db.

E:\asa90> dbdsn -w testdsn -cw -c uid=dba;pwd=sql;eng=asa;dbf=my.db

Generate the command to create the ASA 9.0 Sample data source and output it to a file called restoredsn.bat:

dbdsn -cm -g "ASA 9.0 Sample" > restoredsn.bat

The restoredsn.bat file contains the following:

dbdsn -y -wu "ASA 9.0 Sample" -c "UID=dba;PWD=sql;
DBF=C:\Program Files\Sybase\SQL Anywhere 9\asademo.db;
ENG=asademo9;START=C:\Program Files\Sybase\SQL Anywhere 9\
win32\dbeng9.exe -c 8m;ASTOP=yes;
Description=Adaptive Server Anywhere Sample Database"