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.