Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

st: Re: Using File DSNs with -odbc-


From   kturner@stata.com (Kevin Turner)
To   Statalist <statalist@hsphsun2.harvard.edu>
Subject   st: Re: Using File DSNs with -odbc-
Date   Fri, 02 Apr 2004 10:08:56 -0600

Joseph Coveney <jcoveney@bigplanet.com> writes:

>Does anyone know whether Stata's -odbc- commands can be used with *file*
>DSNs?  I cannot seem to get -odbc list- to see a file DSN even though it's
>listed in the File DSN tab in the Windows OS's Control Panel's ODBC Data
>Sources.  Does Stata only see DSNs that are stored in the registry?
>
>I've checked the Stata reference manual, searched the Statalist archives,
>and googled for Stata file DSN ODBC.
>
>Joseph Coveney

Answering Joseph's question, you can connect via a FileDSN with Stata on
Windows. However, it is not as convenient as it could be and that is due to 
the fact that FileDSNs are not regular DSNs.

Just for clarification to anyone who doesn't know what a FileDSN is -- it
is a connection method that stores all connection parameters in a file. It has
nothing to do with connecting to a file as a data source, but rather using a
file for obtaining the connection parameters. Because of the subtle difference
in the mode of connection, the ODBC specification separates DSNs from FileDSNs. 
This is why a poll of DSNs with -odbc list- does not show any FileDSNs. 

It is also worth noting that using a FileDSN should not be required to connect
to a particular data source. A similar entry as a regular DSN can be configured
to connect to the same data source, but with a FileDSN the task of configuring
a new connection is greatly reduced since the information is stored within the
file. 

The general concept for connection via a FileDSN with Stata involves using a
regular DSN to initiate the connection, prompting for a connection dialog, and
from the connection dialog, crossing over to a FileDSN. 

I will use an example for an Excel data source:

	1. Verify that there is a User DSN defined with the appropriate driver
	   for your data. Most Windows computers come with a default 'Excel
	   Files' DSN entry, and this is the DSN entry I will use for this
	   particular example. If you are connecting to a specific database,
	   such as Postgres or MySQL, you will have to create a basic DSN entry
	   that uses the appropriate driver. Don't worry about filling in any
	   specific information for the DSN; just make sure it uses the 
	   appropriate driver.

	2. Add your FileDSN entry to the File DSN tab by selecting your *.dsn 
	   connection file.

	3. From Stata, enter an -odbc load- command that uses the dialog prompt
	   option. You will need to know the table name at this point -- which
	   is something that you will have to know from an external (from Stata)
	   source. The DSN to use is the regular DSN from step 1.

	   . odbc load, dsn("Excel Files") table("table1$") dialog(prompt)

	4. From the dialog that appears, select the "File Data Source" tab, 
	   select the FileDSN that you defined, and click the following "OK" 
	   buttons. 

Assuming everything was defined appropriately, the data should now be loaded. 
Other odbc commands that accept the dialog(prompt) option can be used in 
similar fashion.

--Kevin 
kturner@stata.com
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index