No announcement yet.
  • Filter
  • Time
  • Show
Clear All
new posts

  • odbc load throwing error for long SQL query

    I am trying to download data from a read-only database using odbc load, exec().
    My SQL query is generated from my existing data and saved into a local macro. It's about 400,000 characters long (there's a good reason for this, but it's not relevant to the question).
    When I try to run odbc with it, I get the following error:
    table() or exec() is required
    I played around with it a little, and for strings that are under 260K, the command runs without a problem, but over 320K it fails. I could narrow that number down more, but I didn't see much point to it.
    It seems that the parser is not able to parse the entire input beyond a certain length and throws that error.
    I can't find any memory/size limit that is being violated.
    Does anyone have any solutions for this?
    I'd rather try to "solve" this elegantly before resorting to some kludgy solution that breaks up the query and then appends the results back together...

    Thank you in advance.

  • #2
    What "flavor" of Stata are you using?
    help limits
    Stata IC has a limit of 264 392 characters in a macro, just over your successful example. Stata SE has a limit of a little over four million and Stata MP has a limit over 15 million characters.

    Maybe you could modify the query to put the result set into a temporary table or view in your database (assuming that you have the permissions to do so), put that query in a file and call it with odbc sqlfile(), and then pull the contents of the temporary table or view into Stata with a simple odbc load, table(MyTempTable).


    • #3
      Sorry, I should have specified.
      I'm running Stata MP, so I shouldn't have any problems with that limit.
      I also verified that the macro I'm inputting has the complete string.
      Are there any command-related limits that aren't on that list?

      I wish I could create a temp table, but unfortunately, I only have read-only access.

      Thank you!


      • #4
        Okay, the fact that the Stata IC limit for a macro is in the range I posted made me suspicious.
        I just verified that 264,380 characters work, but 264,395 does not, so this limit must be hard-coded into the odbc command (or maybe other/all commands?) even in the MP edition.
        Does anyone know about this?
        I can't even find the odbc.ado file -- I guess it's working with some sort of external program.


        • #5
          At this point, I have to assume it's a "bug."
          I've contacted Stata support to see what they have to say.
          I'll post their response here.

          Meanwhile, I'm saving the query as a text file and using shell to run the query from python and export it to a .dta file using pandas.


          • #6
            Here's a MWE:
            clear all
            set maxvar 120000 // to rule this limit out
            local sql = "a"*264391
            odbc load, exec("`sql'") // this throws the correct error about no dsn
            local sql = "a"*264392
            odbc load, exec("`sql'") // this yields "table() or exec() is required"


            • #7
              A Stata technical support agent put in a request to increase the limit.
              I'll update this if and when this is implemented.