st: Re: odbc load

["Joseph Coveney" <jcoveney@bigplanet.com>]

Jen Zhen wrote:

I am trying to load a dataset (or, for size reasons, parts of it) from
SQL into Stata.

I got as far as typing
   local db "DRIVER={MySQL ODBC 5.1
Driver};SERVER=my_server;DATABASE=my_database;UID=jenzhen;PWD=jenzhenspassword;"
   local sql "SHOW tables"
   odbc load, exec("`sql'") conn("`db'") clear
   br
which shows me a list of all tables in the database "my_database":
Tables_in_my_database", "table1" and "table2".

However, I don't manage to open one of these tables. I typed
   odbc load, table(table1) conn("`db'") clear
which I thought should load table1 into memory, but Stata then just tells me:


   The ODBC driver reported the following diagnostics
   [MySQL][ODBC 5.1 Driver][mysqld-5.1.30]You have an error in your
SQL syntax; check the manual that corresponds to your
   MySQL server version for the right syntax to use near 'table1' at line 1
   SQLSTATE=42000

Might anyone be able to tell me what I'm getting wrong here?

--------------------------------------------------------------------------------

I'm not familiar with MySQL, so I can't guess what might be wrong with Stata's
SQL statement that MySQL doesn't like.  You can do a couple of things, though.  

First, you take a look at what Stata is sending to MySQL, by using the -
sqlshow- option of -odbc , load-:

odbc load, table(table1) conn("`db'") sqlshow clear

You'll see the SQL statement that Stata's sending.  You might be able to
diagnose what it is that "your MySQL server version" doesn't like from that and
set things right.

Another option is to write your own SQL statement to retrieve the data, by means
of the -exec()- option--something like:

odbc load, exec("SELECT * FROM table1;") conn("`db'") clear

Last, you can try simplifying the conection string, and use the prompts for user
name and password, in case that might be the problem, using the connection
options available with -odbc-.  See the online help or user's manual for more on
those.

Joseph Coveney


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/