odbc.hlp

是一个经济学管理应用软件 很难找的 但是经济学学生又必须用到

{smcl}
{* 24feb2005}{...}
{cmd:help odbc}
{hline}

{title:Title}

{p2colset 5 17 19 2}{...}
{p2col :{hi: [D] odbc} {hline 2}}Load, write, or view data from ODBC sources{p_end}
{p2colreset}{...}


{title:Syntax}

{phang}List ODBC sources to which Stata can connect

{p 8 13 2}
{cmd:odbc} {opt li:st}

{phang}Retrieve available names from specified data source

{p 8 13 2}
{cmd:odbc} {opt q:uery}
[{cmd:"}{it:DataSourceName}{cmd:"}
{cmd:,}
{it:{help odbc##connect_options:connect_options}}]

{phang}List column names and types associated with specified table

{p 8 13 2}
{cmd:odbc} {opt des:cribe}
[{cmd:"}{it:TableName}{cmd:"}
{cmd:,}
{cmd:dsn(}{cmd:"}{it:DataSourceName}{cmd:"}{cmd:)}
{it:{help odbc##connect_options:connect_options}}]

{phang}Read ODBC table into memory

{p 8 18 2}
{cmd:odbc} {opt lo:ad}
[{it:extvarlist}]
{ifin}
{cmd:,}
{c -(}{cmdab:t:able:(}{cmd:"}{it:TableName}{cmd:"}{cmd:)}{c |}{cmdab:e:xec:(}{cmd:"}{it:SqlStmt}{cmd:"}{cmd:)}{c )-} {break}
[{it:{help odbc##load_options:load_options} {help odbc##connect_options:connect_options}}]

{phang}Write data to an ODBC table

{p 8 18 2}
{cmd:odbc} {opt in:sert}
[{varlist}] {cmd:,}
{cmdab:t:able:(}{cmd:"}{it:TableName}{cmd:"}{cmd:)}
{cmd:dsn(}{cmd:"}{it:DataSourceName}{cmd:"}{cmd:)} {break}
[{it:{help odbc##insert_options:insert_options}} {it:{help odbc##connect_options:connect_options}}]

{phang}Allow SQL statements to be issued directly to ODBC data source

{p 8 18 2}
{cmd:odbc} {cmdab:exe:c(}{cmd:"}{it:SqlStmt}{cmd:"}{cmd:) ,}
{cmd:dsn(}{cmd:"}{it:DataSourceName}{cmd:"}{cmd:)}
[{it:{help odbc##connect_options:connect_options}}]

{phang}Batch job alternative to odbc exec

{p 8 18 2}
{cmd:odbc} {cmdab:sql:file:(}{cmd:"}{it:filename}{cmd:"}{cmd:) ,}
{cmd:dsn(}{cmd:"}{it:DataSourceName}{cmd:"}{cmd:)}
[{opt loud} {it:{help odbc##connect_options:connect_options}}]

{pstd}where

{phang2}{it:DataSourceName} is the name of the ODBC source (database,
spreadsheet, etc.)

{phang2}{it:TableName} is the name of a table within the ODBC data source

{phang2}{it:SqlStmt} is a SQL SELECT statement

{phang2}{it:filename} is pure SQL commands separated by semicolons

{phang}and where {it:extvarlist} contains

{phang2}{it:sqlvarname}{p_end}
{phang2}{varname}={it:sqlvarname}

{synoptset 25}{...}
{marker connect_options}{...}
{synopthdr :connect_options}
{synoptline}
{synopt :{opt u:ser(UserID)}}user ID of user establishing connection{p_end}
{synopt :{opt p:assword(Password)}}password of user establishing
connection{p_end}
{synopt :{cmdab:d:ialog(noprompt)}}do not display ODBC connection information
dialog, and do not prompt user for connection information{p_end}
{synopt :{cmdab:d:ialog(prompt)}}display ODBC connection information dialog
{p_end}
{synopt :{cmdab:d:ialog(complete)}}display ODBC connection information dialog
only if there is not enough information{p_end}
{synopt :{cmdab:d:ialog(required)}}display ODBC connection information dialog
only if there is not enough mandatory information provided{p_end}
{synoptline}

{synoptset 25 tabbed}{...}
{marker load_options}{...}
{synopthdr :load_options}
{synoptline}
{p2coldent :* {cmdab:t:able("}{it:TableName}{cmd:")}}name of table
stored in data source{p_end}
{p2coldent :* {cmdab:e:xec("}{it:SqlStmt}{cmd:")}}SQL SELECT
statement to generate a table to be read into Stata{p_end}
{synopt :{cmd:dsn("}{it:DataSourceName}{cmd:")}}name of data source{p_end}
{synopt :{opt clear}}load dataset even if there is one in memory{p_end}
{synopt :{opt noq:uote}}alter Stata's internal use of SQL commands;
seldom used{p_end}
{synopt :{opt low:ercase}}read variable names as lowercase{p_end}
{synopt :{opt sql:show}}show all SQL commands issued{p_end}
{synopt :{opt allstr:ing}}read all variables as strings{p_end}
{synopt :{opt datestr:ing}}read date-formatted variables as strings{p_end}
{synoptline}
{p2colreset}{...}
{p 4 6 2}
* Either {cmd:table("}{it:TableName}{cmd:")} or
{cmd:exec("}{it:SqlStmt}{cmd:")} must be specified.{p_end}

{synoptset 25 tabbed}{...}
{marker insert_options}{...}
{synopthdr :insert_options}
{synoptline}
{p2coldent :* {cmdab:t:able("}{it:TableName}{cmd:")}}name of table
stored in data source{p_end}
{p2coldent :* {cmd:dsn("}{it:DataSourceName}{cmd:")}}name of data source{p_end}
{synopt :{opt cr:eate}}create a simple ODBC table{p_end}
{synopt :{opt over:write}}clear data in ODBC table before data in memory is
written to the table{p_end}
{synopt :{opt ins:ert}}default mode of operation for the {opt odbc insert}
command{p_end}
{synopt :{opt q:uoted}}quote all values with single quotes as they are inserted
in ODBC table{p_end}
{synopt :{opt sql:show}}show all ODBC variables on the data source{p_end}
{synopt :{cmd:as("}{varlist}{cmd:")}}ODBC variables on the data source that correspond
to the variables in Stata's memory{p_end}
{synoptline}
{p2colreset}{...}
{p 4 6 2}
* {cmd:table("}{it:TableName}{cmd:")} and
{cmd:dsn("}{it:DataSourceName}{cmd:")} are required.{p_end}


{title:Description}

{pstd}
{opt odbc} allows you to load, write, or view data from ODBC sources into
Stata.  ODBC,
an acronym for Open DataBase Connectivity, is a standardized set of function
calls for accessing data stored in both relational and
nonrelational database-management systems.  Currently, the {opt odbc}
command is available for Windows, Mac OS X, and Linux versions of Stata.

{pstd}
ODBC's architecture
consists of four major components (or layers): the client interface, the
ODBC driver manager, the ODBC drivers, and the data sources.  Stata provides
{opt odbc} as the client interface.  The system is illustrated as following,

    {c TLC}{hline 19}{c TRC}{dup 37: }{c TLC}{hline 10}{c TRC}
    {c |}{dup 19: }{c |}{dup 5: }{c TLC}{hline 9}{c TRC}{dup 5: }{...}
{c TLC}{hline 9}{c TRC}{dup 5: }{c |}{dup 10: }{c |}
    {c |} {ul:Client interface}  {c |}{dup 5: }{c |}{dup 9: }{c |}{...}
{dup 5: }{c |}{dup 9: }{c |}{dup 5: }{c |}{dup 10: }{c |}
    {c |}       (Stata)     {c |}{dup 5: }{c |}{dup 9: }{c |}{...}
{dup 5: }{c |}{dup 9: }{c |}{dup 5: }{c |}{dup 10: }{c |}
    {c |}{dup 19: }{c |}{dup 5: }{c |} ODBC    {c |}{dup 5: }{...}
{c |} ODBC    {c |}{dup 5: }{c |}  ODBC    {c |}
    {c |} {cmd:odbc} {cmd:list}         {c |} {hline 3} {c |} driver  {c |} {...}
{hline 3} {c |} driver  {c |} {hline 3} {c |}  data    {c |}
    {c |} {cmd:odbc} {cmd:query}        {c |}{dup 5: }{c |} manager {c |}{dup 5: }{...}
{c |}{dup 9: }{c |}{dup 5: }{c |}  Source  {c |}
    {c |} {cmd:odbc} {cmd:describe}     {c |}{dup 5: }{c |}{dup 9: }{c |}{dup 5: }{...}
{c |}{dup 9: }{c |}{dup 5: }{c |}{dup 10: }{c |}
    {c |} {cmd:odbc} {cmd:load}         {c |}{dup 5: }{c BLC}{hline 9}{c BRC}{...}
{dup 5: }{c BLC}{hline 9}{c BRC}{dup 5: }{c |}{dup 10: }{c |}
    {c |} {cmd:odbc} {cmd:insert}       {c |}{dup 37: }{c |}{dup 10: }{c |}
    {c |} {cmd:odbc} {cmd:exec}         {c |}{dup 37: }{c |}{dup 10: }{c |}
    {c |} {cmd:odbc} {cmd:sqlfile}      {c |}{dup 37: }{c |}{dup 10: }{c |}
    {c BLC}{hline 19}{c BRC}{dup 37: }{c BLC}{hline 10}{c BRC}

{pstd}
{opt odbc list} produces a list of ODBC data source names to which Stata can
connect.

{pstd}
{opt odbc query} retrieves a list of table names available from a specified
data source's system catalog.

{pstd}
{opt odbc describe} lists column names and types associated with a specified
table.

{pstd}
{opt odbc load} reads an ODBC table into memory.  You can load
an ODBC table specified in the {opt table()} option or load an ODBC
table generated by a SQL SELECT statement specified in the {opt exec()}
option.  In both cases, you can choose which columns and rows of the
ODBC table to read by specifying {it:extvarlist} and {opt if}, {opt in}
conditions.  {it:extvarlist} specifies the
columns to be read and allows you to rename variables.  For
example,

{p 8 16 2}
{cmd:. odbc load id=EmployeeID LastName, table(Employees) dsn(Northwind)}

{pstd}
reads in 2 columns, {cmd:EmployeeID} and {cmd:LastName}, from the
{cmd:Employees} table of the {cmd:Northwind} data source.  It will also rename
variable {cmd:EmployeeID} to {cmd:id}.

{pstd}
{opt odbc insert} writes data from memory to an ODBC table.  The data can
be appended to an existing table, replace an existing table, or be placed in
a newly created ODBC table.

{pstd}
{opt odbc exec} allows for most SQL statements to be issued directly to any
ODBC data source.  Statements that produce output, such as SELECT, have their
output neatly displayed.  By using Stata's ado language, you can also generate
SQL commands on the fly to do positional updates or whatever the situation
requires.

{pstd}
{opt odbc sqlfile} provides a "batch job" alternative to the 
{opt odbc exec} command.  A file is specified that contains any number of any
length SQL commands.  Every SQL command in this file should be delimited by a 
semicolon and must be constructed as pure SQL.  Stata macros and ado-language 
syntax are not permitted.  The advantage in using this command, as opposed to
{opt odbc exec}, is that only one connection is established for multiple SQL
statements.  A similar sequence of SQL commands used via {opt odbc exec} would
require constructing an ado file that issued a command and, thus, a connection,
for every SQL command.  Another slight difference is that any output that might