2

I have a Stata plug-in that allows me to read SQL from a file, run it on my PostgreSQL server, and have the data delivered to Stata.

Unfortunately, a lot of my SQL relies on newlines being correctly parsed so as to generate correct (or even valid) SQL. For example,

SELECT *
FROM some_table
-- Require something to be true
WHERE some_boolean;

ends up in Stata as SELECT * FROM some_table -- Require something to be true WHERE some_boolean;, which clearly doesn't work.

Here is the .ado file I use to read the data. I have tried many approaches, but can't find a way to put newlines into the exec variable.

program define loadsql
*! Load the output of an SQL file into Stata, version 1.4 (iandgow@gmail.com)
version 13.1
syntax using/, CONN(string)

#delimit;
tempname sqlfile exec line;

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

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

file close `sqlfile';

* display "`conn'";

pgload "`conn'" "``exec''", clear;
* pgload "``dsn''" "SELECT permno, date, abs(prc) AS prc FROM crsp.dsf LIMIT 10", clear;

end;
Ian Gow
  • 3,098
  • 1
  • 25
  • 31
  • I'm not sure I understand. Can you post **exactly** how the `pgload` syntax is supposed to be? I mean, substitute the local macros `conn` and `exec` with the appropriate text. – Roberto Ferrer Feb 07 '15 at 22:24
  • Here's an example call to `pgload`: `pgload "dbname=crsp host=localhost" "SELECT permno, date, abs(prc) AS prc FROM crsp.dsf LIMIT 10"`. What I want to be able to do is to pass `SELECT *\n FROM some_table\n -- Require something to be true\n WHERE some_boolean;\n` to `pgload` where `\n` represents the newline character. As the code is, the newlines in the text file I load turn into spaces, to I get `SELECT * FROM some_table -- Require something to be true WHERE some_boolean;`, which doesn't work. – Ian Gow Feb 07 '15 at 22:52
  • I just find it unusual that a Stata command requires inserting newline characters. If so, I think `pgload` itself should indicate the way. I've never faced or heard/read of such a situation (though I have a relatively short experience). – Roberto Ferrer Feb 07 '15 at 23:18
  • It's not Stata that is requiring this, but PostgreSQL (or SQL). SQL uses line-breaks to determine the end of comments that begin with `--`. In other words, I don't want PostgreSQL to treat `-- Require something to be true WHERE some_boolean;` as a comment; the `WHERE some_boolean` is meant to affect the result of the query. – Ian Gow Feb 07 '15 at 23:21
  • 1
    Let me try again: what character(s) do you need the local `exec` (not the "variable") to hold? Put differently, what characters would SQL understand to be new lines? Is it `\n` or are you using `\n` as a placeholder for some character(s) you are hoping to find? – Roberto Ferrer Feb 08 '15 at 00:38
  • `\n` is the way to represent "newlines" in some languages. A literal `\n` is not what I am after. But it seems I can get this using `char(10)`. See my answer and also http://stackoverflow.com/questions/1552749/difference-between-cr-lf-lf-and-cr-line-break-types Your question prompted more research, which led to the answer. Thanks. – Ian Gow Feb 08 '15 at 06:14

1 Answers1

2

Use char(10) (or perhaps char(13) + char(10) on Windows).

Thus, replace

local `exec' `"``exec'' ``line'' 
 "';

in the .ado file supplied in the question with 

local `exec' = "``exec''" + char(10) + "``line''";
Ian Gow
  • 3,098
  • 1
  • 25
  • 31