Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: Re: SQL Query Password/User ID


From   "Jesper K. Hansen" <jkh-stata@msecon.eu>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Re: SQL Query Password/User ID
Date   Fri, 14 Dec 2007 10:34:21 +0100

You might also consider investing in StatTransfer.

I had to find an easy, automated SQL-server-to-Stata setup recently,
and I ended up using StatTransfer batch files, executed from a Stata
do-file.

The key is the StatTransfer commands -dbr- and -dbw- (db read/write)
with the connstr option (connect string). With connstr you can store
the ODBC connection information, including password, in an encrypted
file.

Example:

//====== Begin ImportDataFromSQLServer.stcmd =============//
log using ImportDataFromSQLServer.txt
dbr connstr <# ConnectStr_ODBC.str

dbr table PATIENTView
copy ODBC Data_org\PATIENTView.dta /y

dbr table dugastandard
copy ODBC Data_org\dugastandard.dta /y
[...]

quit

//======== End ImportDataFromSQLServer.stcmd =============//

//================ Begin import.do =======================//
shell $StatTransfer_path ImportDataFromSQLServer.stcmd

type ImportDataFromSQLServer.txt , asis

//================== End import.do =======================//

You have to create the connstr file interactively before first use.
See the StatTransfer manual for more information.

- Jesper K. Hansen

2007/12/14, Joseph Coveney <jcoveney@bigplanet.com>:
> I wrote:
>
> I haven't tested this out, and so it might need a little tweaking.
>
> --------------------------------------------------------------------------------
>
> The do-file below should help in any tweaking efforts.  For use, substitute
> "odbc" for "display in smcl as result" in the program.
>
> Joseph Coveney
>
> clear *
> set more off
> capture program drop myodbc
> program define myodbc
>     version 10
>     syntax anything(everything) [, *]
>     capture assert "$us" == ""
>     if !_rc display in smcl as text "What's your user ID" _request(us)
>     capture assert "$pw" == ""
>     if !_rc display in smcl as text "What's your password" _request(pw)
>     if (`"`anything'"' == "list") display in smcl as result "odbc list"
>     else display in smcl as result ///
>       "odbc " `"`anything'"' ", " `"`options'"' " user($us) password($pw)"
> end
> *
> local dsn MyDSN
> local file C:/Files/My File.SQL
> myodbc list
> myodbc query "`dsn'", dialog(required)
> myodbc describe "[My Table]", dsn("`dsn'") dialog(complete)
> myodbc load here there everywhere ///
>   if nowhere == "nothing", table("[My Table]")
> myodbc load , exec("SELECT * FROM [My Table]") dsn("`dsn'")
> myodbc sqlfile("`file'"), dsn("`dsn'")
> myodbc exec("SELECT a, b FROM [My Table]"), dsn("`dsn'")
> exit
>
>
> *
> *   For searches and help try:
> *   http://www.stata.com/support/faqs/res/findit.html
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>
>
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



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