odbc load in .do file

[Allen Sirolly]

I set up an odbc connection and would like to retrieve data via SQL query:

local query "SELECT ..."

clear
odbc query "wdmsql01"
odbc load, exec("`query'")

I can execute this without a problem from the Stata command console. But running the same from within a .do file triggers the error

table() or exec() is required

What am I not doing correctly?

[William Lisowski]

My guess is that, with your code displayed in the Do-file Editor window, you selected the first line - the local command - and submitted it, then selected subsequent lines, perhaps one at a time, and submitted them.

The problem is, when you submit selected lines from the Do-file Editor, it writes them to a temporary do-file and submits that file. The local macro is defined, but once the temporary do-file ends, the local macro vanishes. So later you are effectively executing

odbc load, exec("")

which led to the error message you received.

The solution is to run the entire contents of the Do-file Editor window at the same time.

[wbuchanan]

I could also be the result of issues accessing the user credentials. Some drivers make it challenging to store your username and password, so it could be that the pop up window that allows you to enter your credentials if they cannot be directly accessed was not handled properly.

[Allen Sirolly]

The solution is to run the entire contents of the Do-file Editor window at the same time.

That's precisely what I did

[Joseph Coveney]

You might want to check your do-file again. Somewhere between the local macro's definition and its use in odbc load it got deleted. The following replicates the essential features of what you show in your first post above, and it runs without a hitch in a do-file.

version 14.2

clear *
set more off
quietly sysuse auto

tempfile AutoXL
quietly export excel make price using `AutoXL', sheet(Auto) firstrow(variables)

*
* From here
*
local query "SELECT * FROM [Auto$]"

clear
odbc query "Excel Files;DBQ=`AutoXL';"
odbc load, exec("`query'")

exit

[William Lisowski]

Following Joseph's lead, I will add that the error message displayed in the original post can be reproduced with

. odbc load, exec("")
table() or exec() is required
r(198);

and clicking on r(198) shows a discussion of syntax errors. (Specifying an option with a null value is identical to not specifying the option at all when the command is parsed using the usual syntax command.) So if r(198) is what was reported with the error message in post #1, that reinforces Joseph's and my diagnosis that your local macro query has become undefined somehow by the time the odbc load command is executed.

[Allen Sirolly]

Well, I added a di "`query'" immediately before odbc load, and it prints the query exactly as I defined it.

I failed to mention that my query spans multiple lines:

local query "CREATE TABLE #y(DunsNumber VARCHAR(9), Latitude DEC(8,4), Longitude DEC(8,4)); " ///
              "INSERT INTO #y SELECT DunsNumber, Latitude, Longitude FROM vw_db_nets_misc WHERE Kids > 1; " ///
              "SELECT x1.DunsNumber, Company, Latitude, Longitude, Emp90, Emp91, Emp92, Emp93, Emp94, Emp95, Emp96, " ///
                  "Emp97, Emp98, Emp99, Emp00, Emp01, Emp02, Emp03, Emp04, Emp05, Emp06, Emp07, Emp08, Emp09, Emp10, " ///
                  "Emp11, Emp12, Emp13, Emp14 FROM #y AS x1 TABLESAMPLE(40) " ///
              "JOIN vw_db_nets_emp AS x2 ON x1.DunsNumber=x2.DunsNumber " ///
              "JOIN vw_db_nets_company AS x3 ON x2.DunsNumber=x3.DunsNumber"

I tried a single-line query just now and it worked fine. So something must be wrong with the above (i.e., with the line breaks; the query itself is valid).

[Joseph Coveney]

Ditch the quotes. You don't need them, anyway, when defining multiline local macros. For example, the following runs without any problem.

version 14.2

clear *
set more off
quietly sysuse auto

tempfile AutoXL
quietly export excel make price using `AutoXL', sheet(Auto) firstrow(variables)

*
* From here
*
local query SELECT * ///
    FROM [Auto$]

clear
odbc query "Excel Files;DBQ=`AutoXL';"
odbc load, exec("`query'")

exit

I'm guessing that inside odbc the string arguments are being dereferenced with compound double quotes, and your combination of quoted segments and line breaks is probably screwing things up—see below for what odbc is probably seeing when you have the local macro defined as you show.

.ÿversionÿ14.2

.ÿ
.ÿclearÿ*

.ÿsetÿmoreÿoff

.ÿ
.ÿlocalÿqueryÿ"SELECTÿ*ÿ"ÿ///
>ÿÿÿÿÿÿÿÿÿ"FROMÿ[Auto$]"

.ÿ
.ÿdisplayÿinÿsmclÿasÿtextÿ`"`query'"'
SELECTÿ*ÿ"ÿÿÿÿÿÿÿÿÿ"FROMÿ[Auto$]

.ÿ
.ÿexit

endÿofÿdo-file


.

[William Lisowski]

Let's go through Allen's problem a step at a time.

First, although post #7 he tells us the macro displays as he defined it, display is not your friend when seeing how macros are being constructed. Consider

. local s0  a b c d e f

. local s1 "a b c d e f"

. local s2 "a b c" "d e f"

. local s3 "`s2'"

. local s4 = "a b c " + "d e f"

. macro list _s0 _s1 _s2 _s3 _s4
_s0:            a b c d e f
_s1:            a b c d e f
_s2:            a b c" "d e f
_s3:            a b c" "d e f
_s4:            a b c d e f

. display `: word count "`s0'" '
1

. display `: word count "`s1'" '
1

. display `: word count "`s2'" '
2

First we see the effect Joseph pointed out. This is a known difficulty when using quotation marks in a macro definition; I stumbled in the same fashion as Allen did when I was new to Stata, but can't at the moment locate the clarifying documentation. In general, though, Joseph's advice is pertinent: while not uniformly incorrect, using quotation marks in macro definition can have unintended effects.

Next, we see that counting the number of "words" in a macro returns different results for the different examples. My hypothesis is that odbc load is checking for the presence of the table() or exec() options by similarly counting the number of words in the arguments, and is unfortunately comparing the value to 1, since it expects at most a single argument. This fails, since 2!=1, but the error message it chooses to display is misleading.

Next, in post #1 Allen suggests that he ran the same code from the command line and in the do-file. This is not the case, because the command line does not permit command continuation onto multiple lines. As he reports in post #7, running the same code in the do-file works.

Next, in post #2 I neglected to remind Allen of the guidance in the Statalist FAQ linked to from the top of the page, in particular Section 12.1:

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!

And instead I mistakenly inferred the simple query he seemed to indicate.

[William Lisowski]

Let me add this separately rather than edit the previous post yet again. Implicit in the output of help macro

local    lclname [=exp | :extended_fcn | [`]"[string]"['] ]

is that a macro definition includes at most a single quoted string - but it also suggests that quotation marks surrounding the string are required, which we have empirically seen is not the case.

Perhaps someone can point out an authoritative discussion in the Stata documentation on this issue.