Re: st: Creating custom Viewer windows

[David Elliott <dcelliott@gmail.com>]

Joseph

I have experienced the same frustration since the -odbc- commands produce
results with some tantalizing interactivity (depending on whether the dsn
requires a username and password).  I also find it tedious to scroll around
to find the table I want in 100+ tables of a large relational database.

That being said, it isn't hard to write a short dofile that creates a smcl
log to capture the tablenames and open the log in a viewer.  Any smcl
interactivity will still be available to run from the viewer so clicking a
table will execute a -odbc desc tablename-.  Is that the sort of thing you
want?

Anyhow, here is a program listing that may do the trick

=============begin================
program define odbctables
*! Interactive ODBC Table listing
*! Version 1.0 20100924
*! by David C. Elliott
*! dcelliott@gmail.com
version 9

syntax anything(name=dsn id="Data Source Name") [, View]
local dsn `dsn'
local tables `dsn'_tables.smcl

tempname hi ho lf
tempfile templist

capture log close `lf'
local more `c(more)'
set more off
qui log using `templist', smcl name(`lf')
odbc query `"`dsn'"', dialog(complete)
qui log close `lf'
set more `more'

file open `hi' using "`templist'", read text
file open `ho' using "`tables'", write text replace

file read `hi' line
while r(eof)==0 {
	if regexm(`"`line'"',`"(^{stata odbc desc ".*")(:.*}$)"')==1 {
		file write `ho' `"`=regexs(1)' ,dsn("`dsn'") dialog(complete)
`=regexs(2)'"' _n
		}
		else {
			file write `ho' `"`macval(line)'"' _n
			}
	file read `hi' line
	}
file close _all
if "`view'" == "view" {
	view `"`dsn'_tables.smcl"'
	}
end

=============end================

As with all code listings on Statalist, beware line wrapping.

Note that one has to add the dialog(complete) to the smcl listing.
This is the flaw in the built in -odbc- commands - they only work with
DSNs that are not username and password protected.  I can't imagine
that it would be difficult to add a "dialog(complete)" to the output
of the built-in commands.  Note:I am still using Stata 10 so
functionality may have changed - I know some -odbc- commands work a
bit differently.

Finally, one could go one step further and loop-process the table list
with successive -odbc desc- commands, logging the variable listings
and then modify the smcl output in a similar manner allowing point and
click table loading.

If anyone wants a copy of the odbctables.ado, just contact me off-list.

DC Elliott

On 23 September 2010 04:04, Joseph Coveney <jcoveney@bigplanet.com> wrote:
>
> Does anyone know how to (or whether it's even possible to) create a Viewer
> window that contains a list of the tables from a database that I'm working with
> interactively?  The list is created by -odbc query-.
>
> The databases typically have 50-100 user tables, and I can't remember all of
> their names while working interactively.  Now, whenever I want to -odbc load- a
> different table, I have to either scroll back up in the Results window to where
> I last executed the -odbc query- command in order to see the list, or execute
> the command again and again.
>
> If the list of tables is in a persistent Viewer window then I can reference it
> briefly while working just by clicking on the its icon and making the Viewer
> window visible (on top, have focus) again.
>
> From -help view- and [R] View, I would first have to -log- the output of the
> -odbc query- command to a log file and then view the log file, but that seems
> circuitous, and a dead-end as far as extensibility goes (for example, eventually
> making the list of tables as dynamic as -odbc query- makes it in the Results
> window).  This approach also incurs additional log file management overhead,
> which isn't terribly burdensome, but it seems unnecessary.
>
> I've looked at -help window_programming- and at [P] Window Programming in the
> user's manual, but they're concerned with Stata's built-in windows and with
> dialogue programming, and the popup windows for the latter are apparently
> limited to a three- or four-line error message.
>
> Joseph Coveney
>
>
> *
> *   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/



--
David Elliott

Everything is theoretically impossible, until it is done.
Progress is made by lazy men looking for easier ways to do things.
 -- Robert A. Heinlein (American science-fiction Writer, 1907-1988)

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