ODBC Data Sources




The DBR and DBW Commands

Generally, the process of opening an ODBC data source for reading or writing requires information beyond a simple file name.  Thus, you may need to give commands which define the data source name, the table name, and sometimes a password or database name.


Stat Transfer has two commands specifically for ODBC data sources:


    DBR (short for database read)

    DBW (short for database write)


These commands must precede the COPY command.


Each of these commands is followed by a single parameter that gives necessary information for the ODBC data source.  Each of these parameters consists of the parameter name followed by a value for that parameter.


    DBR | DBW parametername  value


The parameter names are:


DATASOURCE - the name of the ODBC data source

DATABASE - the name of the database

PASSWORD - logon password for the data source

TABLE - the name of the table you want to read or write

SERVER - the name of the machine the database is running on

SQL - a sql statement to retrieve specific fields and/or join tables

CONNSTR - an ODBC connection string (explained below).


The parameter CONNSTR is the most useful of these in command files.


Which of the parameters is necessary for a particular database depends on the database and on the driver.  You must enter as many DBR and DBW commands as necessary to supply all of the parameters.


For example, if you want to read from the employee table of a SQL Server database you could specify the table with the following command.


    dbr table employee


You could then submit a COPY command:


    copy ODBC out.sd2


to copy the table to a SAS file named out.sd2.


To write to a table, data, in an ODBC data source, you could use the commands:


    dbw table data

  copy indata.sd2 ODBC



Retrieving Parameter Values

During a Stat/Transfer session, you can obtain the value of any parameter by entering DBR or DBW and the parameter name without a value:


    DBR | DBW parametername


If no value has been entered yet, you will be told that the value is blank.



Clearing Parameter Values

The DBR and DBW values are persistent across COPY commands during a session.  Thus, you will need to change them or clear them if you are executing multiple COPY commands using different data sources.


You can clear the value of any parameter by entering 'clear' in place of a value.  For example:


    dbr connstr clear


clears the value of the parameter CONNSTR.  Note that it is necessary to 'clear' parameter values if you want to be re-prompted by the ODBC driver manager for any values that you have previously entered.