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/
