Re: st: Using odbc load, table with MySQL

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

On Wed, Jul 16, 2008 at 5:58 PM, Joseph Coveney <jcoveney@bigplanet.com> wrote:
> Thomas Jacobs wrote:
>
> 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")
> [snip]                                              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")
> [snip]
>
> so my question is, what am I doing wrong in the load, table command?
> Thanks.
>
> --------------------------------------------------------------------------------
>
> 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
>

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/