Stata odbc sqlfile [英] stata odbc sqlfile

问题描述

我正在尝试使用odbc sqlfile从数据库(MS Access或SQL Server)中加载数据，似乎代码正在运行，但有任何错误，但我没有获取数据.我正在使用以下代码odbc sqlfile("sqlcode.sql"),dsn("mysqlodbcdata").请注意，sqlcode.sql仅包含带有SELECT的sql语句.事实是，相同的sql代码使用odbc load,exec(sqlstmt) dsn("mysqlodbcdata")提供数据.谁能建议我如何使用odbc sqlfile导入数据?这将对我有很大的帮助.

I am trying to load data from database (either MS Access or SQL server) using odbc sqlfile it seems that the code is running with any error but I am not getting data. I am using the following code odbc sqlfile("sqlcode.sql"),dsn("mysqlodbcdata"). Note that sqlcode.sql contains just sql statement with SELECT. The thing is that the same sql code is giving data with odbc load,exec(sqlstmt) dsn("mysqlodbcdata"). Can anyone suggest how can I use odbc sqlfile to import data? This would be a great help for me.

谢谢 欢乐

推荐答案

sqlfile不加载任何数据.它仅执行(并在指定了Loud选项时显示结果)，而没有将任何数据加载到Stata中.这有点违反直觉，但确实如此.在odbc命令的pdf/dead树手册条目中对原因做了一些不透明的解释.

sqlfile doesn't load any data. It just executes (and displays the results when the loud option is specified), without loading any data into Stata. That's somewhat counter-intuitive, but true. The reasons are somewhat opaquely explained in the pdf/dead tree manual entry for the odbc command.

这是一个更有用的答案.假设您有一个名为sqlcode.sql的SQL文件.您可以在Stata中打开它(只要它不是太长，其中太长取决于Stata的风格).基本上，-file逐行读取SQL代码，并将结果存储在名为exec的本地宏中.然后，将该宏作为参数传递给-odbc load-命令:

Here's a more helpful answer. Suppose you have your SQL file named sqlcode.sql. You can open it in Stata (as long as it's not too long, where too long depends on your flavor of Stata). Basically, -file read- reads the SQL code line by line, storing the results in a local macro named exec. Then you pass that macro as an argument to the -odbc load- command:

更新后的代码以处理一些双引号问题

剪切&将以下代码粘贴到一个名为loadsql.ado的文件中，您应该将其放置在Stata可以查看它的目录中(例如〜/ado/personal).您可以使用-adopath-命令找到此类目录.

Cut & paste the following code into a file called loadsql.ado, which you should put in directory where Stata can see it (like ~/ado/personal). You can find such directories with the -adopath- command.

program define loadsql
*! Load the output of an SQL file into Stata, version 1.3 ([e-mail])
version 14.1
syntax using/, DSN(string) [User(string) Password(string) CLEAR NOQuote LOWercase SQLshow ALLSTRing DATESTRing]

#delimit;
tempname mysqlfile exec line;

file open `mysqlfile' using `"`using'"', read text;
file read `mysqlfile' `line';

while r(eof)==0 {;
    local `exec' `"``exec'' ``line''"';
    file read `mysqlfile' `line';
};

file close `mysqlfile';


odbc load, exec(`"``exec''"') dsn(`"`dsn'"') user(`"`user'"') password(`"`password'"') `clear' `noquote' `lowercase' `sqlshow' `allstring' `datestring';

end;

/*全部完成！ */

Stata中的语法为

The syntax in Stata is

loadsql using "./sqlfile.sql", dsn("mysqlodbcdata") 

您还可以添加所有其他odbc加载选项，例如clear.显然，您将需要更改文件路径和odbc参数以反映您的设置.此代码应与-odbc sqlfile("sqlfile.sql")，dsn("mysqlodbcdata")-相同，并实际加载数据.

You can also add all the other odbc load options, such as clear, as well. Obviously, you will need to change the file path and the odbc parameters to reflect your setup. This code should do the same thing as -odbc sqlfile("sqlfile.sql"), dsn("mysqlodbcdata")- plus actually load the data.

我还添加了如下功能来指定您的数据库凭据:

I also added the functionality to specify your DB credentials like this:

loadsql using "./sqlfile.sql", dsn("mysqlodbcdata") user("user_name") password("not12345") 

这篇关于Stata odbc sqlfile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！