Statalist


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

Re: st: odbc postgres [version 9.2]


From   Kevin Crow <kcrow@stata.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: odbc postgres [version 9.2]
Date   Wed, 27 May 2009 09:40:46 -0500

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/



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