loadsql.ado

* NOTE: this ADO uses shell calls to run `rm`, so it could potentally delete files
* from your computer. Use at your own risk.

cap drop program loadsql
program define loadsql
*! Load the output of an SQL file into Stata, version 1.3 (dvmaster@gmail.com)
*! Based on http://stackoverflow.com/a/12272627/173351
version 14.1
syntax using/, DSN(string) [User(string) Password(string) CLEAR NOQuote LOWercase SQLshow ALLSTRing DATESTRing]

#delimit;
tempname mysqlfile exec line;

!cat "`using'" | ruby -e "print ARGF.read.gsub(/^\s+--.*$/, '').gsub(/\s+/, ' ')" > "`using'_oneline";

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

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

file close `mysqlfile';
!rm "`using'_oneline";

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

end;