Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: odbc postgres [version 9.2]


From   "Keith Dear (work)" <keith.dear@anu.edu.au>
To   statalist@hsphsun2.harvard.edu
Subject   st: odbc postgres [version 9.2]
Date   Mon, 25 May 2009 18:39:13 +1000

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

--
Dr Keith Dear
Senior Fellow
National Centre for Epidemiology and Population Health
ANU College of Medicine, Biology and Environment
Building 62, cnr Mills and Eggleston Roads
Australian National University
Canberra ACT 0200 Australia
T: 02 6125 4865
F: 02 6125 0740
M: 0424 450 396
W: nceph.anu.edu.au/Staff_Students/staff_pages/dear.php

CRICOS provider #00120C
http://canberragliding.org/

*
*   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/



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