Re: st: Using odbc load, table with MySQL

[Orvalho Joaquim Augusto <orvaquim@gmail.com>]

Sorry!

The option is noquote

Caveman

Thomas Jacobs wrote:
> *****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
> 
> 


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