Bizarre problem in trying to replicate Help example for jdbc

[Gordon Hughes]

I am trying to replicate an example from the Help information on the jdbc command. The example is:

local sql                                      ///
    "CREATE TEMPORARY TABLE t(a INT, b INT);   ///
    INSERT INTO t VALUES (1,2);                ///
    SELECT * FROM t;"
jdbc load, exec("`sql'")

and the result when I execute this is:

. local sql                                      ///
>     "CREATE TEMPORARY TABLE t(a INT, b INT);   ///
invalid syntax
r(198);

I don't have a delimiter set up and I am bemused as to what is going on. My best guess is that Stata is not accepting three forward slashes /// as a line continuation. But why? I have restarted Stata several times. I am using Stata 18.5.

The result from concatenating the local command to a single line is:

local sql "CREATE TEMPORARY TABLE t(a INT, b INT); INSERT INTO t VALUES (1,2); SELECT * FROM 
> t;"

. jdbc load, exec("`sql'")
failed to load resultset
    No results were returned by the query.

I have all of the drivers, etc required by PostgreSQL set up and I know they are working properly from other runs.

[Clyde Schechter]

I can help you with the first problem. You cannot break a quoted string across lines using ///. You also can't do it with /* */. The only way you can do it is to change the delimiter to semi-colon. And when you do that, any tabs used to indent the second line get included as characters in the local macro. Here:

. clear*

.
. local test "ABCDEFG ///
invalid syntax
r(198);

end of do-file

r(198);

. do "C:\Users\clyde\AppData\Local\Temp\STD47c8_000006.tmp"

. local test "ABCDEFG /*
invalid syntax
r(198);

end of do-file

r(198);

. do "C:\Users\clyde\AppData\Local\Temp\STD47c8_000007.tmp"

. #delimit ;
delimiter now ;
. local test "ABCDEFG
>         HIJKLMNOP";

. #delimit cr
delimiter now cr
. macro list _test
_test:          ABCDEFG HIJKLMNOP

.
end of do-file

I believe what is happening is that inside the quoted string, /// or /* are seen as literal characters that are part of the quoted string. And with no closing quote on the line, it just looks to Stata like a string with unbalanced quotes.

That said, I don't see any reason why you need the quotes around local macro anyway.

. local test                                      ///
>     CREATE TEMPORARY TABLE t(a INT, b INT);   ///
>     INSERT INTO t VALUES (1,2);                ///
>     SELECT * FROM t;

. macro list _test
_test:          CREATE TEMPORARY TABLE t(a INT, b INT); INSERT INTO t VALUES (1,2); SELECT * FROM t;

You can then run your commands using the local macro, and if you need to pass it in quotes, you can supply those in the option itself, e.g. -exec("`test'")-.

More generally, you should avoid putting quotes around a macro definition. Stata automatically strips opening and closing quotes from macro definitions. This can cause a lot of confusion. Sometimes, of course, the contents of the macro must include quotes, and occasionally those must be located at the beginning and end. In that case, an extra pair of compound double quotes (that Stata then strips) around the whole thing are needed:

. local test "ABCDEFG"

. macro list _test
_test:          ABCDEFG

. local test `""ABCDEFG""'

. macro list _test
_test:          "ABCDEFG"