Running Batch Jobs with ODBC

With SQL Server and other client-server databases, you are likely to need a password and other parameters.  Fortunately, when you connect from the command line, the ODBC driver manager will prompt you for whatever information is needed.  However, in order to run a job in an automatic batch mode with a command file, you need to supply the necessary information automatically.

 

The simplest way to do this is to take advantage of the fact that ODBC will return a connection string that contains all of the information that you entered.  This information is in the proper form to complete the connection again without any prompting from the ODBC driver manager.

 

You can obtain the connection string from an ODBC data source by connecting interactively to the source and then entering either:

 

    dbr connstr

or

    dbw connstr

 

You will find that the connection strings are generally long and complicated, so that typing one into a command file after retrieval is tedious and error prone.  Furthermore, because the connection string may contain your password, you might not want it written openly to a command file.

 

Stat/Transfer solves these difficulties with operators that allow you to write or retrieve the value of any parameter and by allowing encryption.

 

The Write and Retrieve Operators

The operator '>' allows you to store the value of any parameter in a file, while the operator '<' allows you to read back a stored value.

 

    DBR | DBW  parametername   > | <   storagefile

 

Thus, in order to write the connection string for an input ODBC data source to a file, connect.str, type:

 

    dbr connstr >  connect.str

 

To retrieve the connection string and make it available to an input ODBC data source, type:

 

     dbr connstr <  connect.str

 

To use a connection string in a batch job,  you can connect interactively to the ODBC data source, use the '>' operator to store the connection string in a file and then use the '<' operator with a DBR or DBW command in a command file.

 

Encrypting Connection Strings

Because the connection string may contain your password, you may not want it written to unprotected files. You can encrypt and decrypt the stored value of any parameter by adding a pound sign to the '>' or '<' operators.

 

For example,

 

    dbr connstr >#  connect.str

 

writes the connection string in encrypted format and

 

    dbr connstr <#  connect.str

 

retrieves and decrypts the connection string.