st: Using odbc load, table with MySQL

["Thomas Jacobs" <thomasjacobs@gmail.com>]

*****My original post:*****

I am trying to use the odbc commands to connect to a non-local MySQL
database.  I have had success with odbc query, odbc describe and odbc
list but not odbc load

. odbc load, table("tradeday_libor") dsn("MarkitCDS")
Connection Parameters (IN): DSN=MarkitCDS;
Connection Parameters (OUT):
DATABASE=markitcds;DSN=MarkitCDS;OPTION=0;PWD=cmpqmysql;PORT=3306;SERVER=HP2;UID=CMPQ
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]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 '"tradeday_libor"' at
line 1
r(682);

if I execute the similar command I get a successful result:

. odbc load, exec("select * from tradeday_libor") dsn("MarkitCDS")
Connection Parameters (IN): DSN=markitcds;
Connection Parameters (OUT):
DATABASE=markitcds;DSN=markitcds;OPTION=0;PWD=cmpqmysql;PORT=3306;SERVER=HP2;UID=CMPQ

so my question is, what am I doing wrong in the load, table command?  Thanks.

*****Joseph's suggestion:*****

My guess is that it has to do with the way Stata's -odbc load- is handling
quotes around the table name or somewhere else.

Try the command without the double quotes around the table name.  You
shouldn't need them, anyway.

If that doesn't work, try adding the -noquote- option.

If that doesn't work, try the -sqlshow- option and take a look at the SQL
statement that -odbc load- is generating; compare it to what you've got
in -odbc exec-.

Joseph Coveney

*****My Conclusion:*****

Joseph,

Thanks very much for the suggestions.  I missed those options in the
manual.  You were right about Stata's handling of quotes.  It turns
out that whether I use quotes or not Stata adds them when it generates
the SQL for the query on the load table command:

. odbc load, table("tradeday_libor") dsn("MarkitCDS") sqlshow
SELECT * FROM "tradeday_libor"
r(682);

. odbc load, table(tradeday_libor) sqlshow
SELECT * FROM "tradeday_libor"
r(682);

Using the noquote option was the key:

. odbc load, table("tradeday_libor") dsn("MarkitCDS") noquote sqlshow
SELECT * FROM tradeday_libor

Thanks again!

Tom


-- 
Thomas Jacobs
*
*   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/