Re: st: odbc postgres [version 9.2]

[Kevin Crow <kcrow@stata.com>]

Keith Dear <keith.dear@anu.edu.au> asked about setting up the -odbc- 
commands to work with a PostgreSQL database and how to get schema 
information to work with the -odbc- commands when using Stata 9.2.

>Two questions: how can I get  -odbc query-  and  -odbc desc-  to work 
>on a (networked) PostgreSQL database? and how can I load data  (-odbc 
>load-) from a schema other than "public"?

In the 02feb2009 executable update to Stata 10 we added the below 
features to the -odbc query- command.

     "13.  odbc query now has two new options, verbose and schema. 
         verbose lists any data source alias, nickname, typed table, 
typed view, and view along with tables so that you can load data from 
these table types.  schema lists schema names with the table names if 
the data source returns schema information."

A work around in Stata 9 would be to type the schema name in front of 
the table name when using the -table()- option. For example,

     odbc load, dsn(mydsn) table(`"schema.table"') clear

or

     odbc load, dsn(mydsn) exec(`"SELECT * FROM schema.table"') clear

This might or might not work depending on the driver you are using.  If 
you run into any more trouble setting up Stata to work with PostgreSQL 
you might want to email tech-support@stata.com.  I would also include 
whether you are using a 64-bit or 32-bit Windows operating system and 
the exact psqlodbc driver you are using so that we can try and recreate 
the problem here.


Kevin
kcrow@stata.com


> Dear Statalisters,
> Two questions: how can I get  -odbc query-  and  -odbc desc-  to work on 
> a (networked) PostgreSQL database? and how can I load data  (-odbc 
> load-) from a schema other than "public"?
>
> I have access to a PostgreSQL server which I called "Ivan's server" when 
> I connected to it using the Win-XP "ODBC data source administrator":
>
> . odbc list
>
> Data Source Name                   Driver
> ------------------------------------------------------------------------------- 
>
> <snip>
> Ivan's server                      PostgreSQL ANSI
> ------------------------------------------------------------------------------- 
>
>
> But -odbc query- returns an empty list of databases: ("weather" is one 
> of the databases residing on this server: but not the only one, and I 
> didn't I think do anything to specify it, so this is another mystery).
>
> . odbc q "Ivan's server"
> DataSource: Ivan's server
> Path      : weather
> ------------------------------------------------------------------------------- 
>
> ------------------------------------------------------------------------------- 
>
>
> Nevertheless the databases are there: I can see them in pgAdminIII 
> (nothing to do with Stata) so I know, for example, that there is a table 
> called "OZ_coast" in the "public" schema. I can't  -odbc desc-  it (get 
> another null list, this time of variables), but I CAN load the data 
> (thus odbc differs from other religions: all the usual complexity and 
> subtlety, but now with real miracles).
>
> . odbc desc OZ_coast
> DataSource: Ivan's server (query)
> Table:      OZ_coast (load)
> ------------------------------------------------------------------------------- 
>
> Variable Name                               Variable Type
> ------------------------------------------------------------------------------- 
>
> ------------------------------------------------------------------------------- 
>
>
> . clear
> . odbc load, table(OZ_coast)
> . desc
> Contains data
>  obs:         1,170                         vars:            
> 12                         size:     1,184,040 (88.7% of memory free)
> ------------------------------------------------------------------------------- 
>
>              storage  display     value
> variable name   type   format      label      variable label
> ------------------------------------------------------------------------------- 
>
> gid             long   %12.0g                ID              long   
> %12.0g                F_CODE          str5   %9s                   
> F_CODE_DES      str244 %244s                 ACC             long   
> %12.0g                ACC_DESCRI      str244 %244s                 
> EXS             long   %12.0g                EXS_DESCRI      str244 
> %244s                 SOC             str3   %9s                   
> TILE_ID         long   %12.0g                EDG_ID          long   
> %12.0g                the_geom        str244 %244s                 
> ------------------------------------------------------------------------------- 
>
> Sorted by:     Note:  dataset has changed since last saved
>
> That's question 1: qn.2 is, how can I load data from tables in other 
> schemas? Again, I can see them there in pgAdmin, but -help odbc- has 
> nothing to say about schemas (or indeed about postgres data sources at 
> all). I have also looked at Stata10's odbc.sthlp file, but there's still 
> nothing there relevant to this.
>
> If anyone knows of a tutorial that covers stata.odbc.postgres....
> kd
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.339 / Virus Database: 270.12.40/2135 - Release Date: 05/26/09 08:53:00

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/