4 odbc — Load, write, or view data from ODBC sources
. odbc load id=ID name="Last Name", table(Employees) dsn(Northwind)
reads two columns, ID and Last Name, from the Employees table of the Northwind data source.
It will also rename variable ID to id and variable Last Name to name.
odbc insert writes data from memory to an ODBC table. The data can be appended to an existing
table, replace an existing table, or be placed in a newly created ODBC table.
odbc exec allows for most SQL statements to be issued directly to any ODBC data source.
Statements that produce output, such as SELECT, have their output neatly displayed. By using Stata’s
ado language, you can also generate SQL commands on the fly to do positional updates or whatever
the situation requires.
odbc sqlfile provides a “batch job” alternative to the odbc exec command. A file is specified
that contains any number of any length SQL commands. Every SQL command in this file should be
delimited by a semicolon and must be constructed as pure SQL. Stata macros and ado-language syntax
are not permitted. The advantage in using this command, as opposed to odbc exec, is that only one
connection is established for multiple SQL statements. A similar sequence of SQL commands used
via odbc exec would require constructing an ado-file that issued a command and, thus, a connection
for every SQL command. Another slight difference is that any output that might be generated from
an SQL command is suppressed by default. A loud option is provided to toggle output back on.
set odbcmgr iodbc specifies that the ODBC driver manager is iODBC (the default). set odbcmgr
unixodbc specifies that the ODBC driver manager is unixODBC.
Options
user(UserID) specifies the user ID of the user attempting to establish the connection to the data
source. By default, Stata assumes that the user ID is the same as the one specified in the previous
odbc command or is empty if user() has never been specified in the current session of Stata.
password(Password) specifies the password of the user attempting to establish the connection to the
data source. By default, Stata assumes that the password is the same as the one previously specified
or is empty if the password has not been used during the current session of Stata. Typically, the
password() option will not be specified apart from the user() option.
dialog(noprompt | prompt | complete | required) specifies the mode the ODBC Driver Manager
uses to display the ODBC connection-information dialog to prompt for more connection information.
noprompt is the default value. The ODBC connection-information dialog is not displayed, and you
are not prompted for connection information. If there is not enough information to establish a
connection to the specified data source, an error is returned.
prompt causes the ODBC connection-information dialog to be displayed.
complete causes the ODBC connection-information dialog to be displayed only if there is not
enough information, even if the information is not mandatory.
required causes the ODBC connection-information dialog to be displayed only if there is not
enough mandatory information provided to establish a connection to the specified data source.
You are prompted only for mandatory information; controls for information that is not required to
connect to the specified data source are disabled.
dsn("DataSourceName") specifies the name of a data source, as listed by the odbc list command.
If a name contains spaces, it must be enclosed in double quotes. By default, Stata assumes that
the data source name is the same as the one specified in the previous odbc command. This option
is not allowed with odbc query. Either the dsn() option or the connectionstring() option