Home > Data Management > Connection string support added to odbc command

Connection string support added to odbc command

Stata’s odbc command allows you to import data from and export data to any ODBC data source on your computer. ODBC is a standardized way for applications to read data from and write data to different data sources such as databases and spreadsheets.

Until now, before you could use the odbc command, you had to add a named data source (DSN) to the computer via the ODBC Data Source Administrator. If you did not have administrator privileges on your computer, you could not do this.

In the update to Stata 11 released 4 November 2010, a new option, connectionstring(), was added to the odbc command. This option allows you to specify an ODBC data source on the fly using an ODBC connection string instead of having to first add a data source (DSN) to the computer. A connection string lets you specify all necessary parameters to establish a connection between Stata and the ODBC source. Connection strings have a standard syntax for all drivers but there are also driver-specific keyword/value pairs that you can specify. The three standard things that you will probably need in a connection string are DRIVER, SERVER, and DATABASE. For example,

odbc load, … ///
connectionstring(“DRIVER={SQL Server};SERVER=myserver;DATABASE=db;”)

If you also need to specify a username and password to get access to your database you would type

odbc load, …///
conn(“DRIVER={SQL Server};SERVER=server;DATABASE=db;UID=id;PWD=pwd;”)

Again, there are driver specific keyword/value pairs you can add to the connection string. You can perform a search on the Internet for “connection string” and your database name to find what other options you can specify in the connection string. Just remember to separate each connection string keyword/value pair with a semicolon. You can read more about connection string syntax on Microsoft’s website.

To get this capability in your copy of Stata 11, simply type update all and follow the instructions to complete the update. You can then type help odbc to read more about the connectionstring() option.