• "Everything should be made as simple as possible, but not simpler." - Anonymous (although often attributed to Albert Einstein)
  • Enter your email address to subscribe.

  • Categories

  • Recent Comments

  • RSS Statalist: the Stata forum

  • RSS Stackoverflow [Stata]

  • Google Analytics Stats

    Period:Last 30 Days
    Total Visits:12118

Stata-MySQL a first encounter


In late April, I had my first encounter with .myd, .myi, and .frm files. The challenge was to read these directly from Stata so that users can easily work on the data using Stata. I was told that these are SQL databases. I initially thought that this can easily be read using a straightforward use of -odbc- command. But the file extensions .myd, .myi, and .frm are not among the choices avilable in Windows ODBC Data Source Administrator*. Google…google…google.

Thanks to Andrew Dyck’s post Connect to MySQL database using Stata. His Step 0 and Step 1 is exactly the second half of what I needed to do. But, as I had no idea how SQL works, it took me a while to understand what I needed to do in the first half. I confused the term ‘MySQL server’ to a specific physical machine other than my own PC. Eventually I figured I need MySQL server installed. For those who have their first encounters like me, here is the first half: Step -2 and Step -1.

Step -2: Download/Install the MySQL server**
Not knowing any better, I just used all default settings during the installation, and  typed “pass” as password when asked. I did not bother to change the default username “root”.

You did OK if you can open the MySQL Command Line Client (screen shot below) from the start menu. As you can see (or maybe not because they are too small) from the screenshot, I typed “show databases;” to see the databases that the system has access to. I found that only the default MySQL databases are in the list. Why can’t it see my database? Because my folder was in the desktop.



I guess it is like Stata. If you put your data or ado-file in the wrong place, Stata will not be able to find it. Where is the right place?

Step -1: Paste the folder with your data in the right place
That right place can be found in my.ini file (in my case, my.ini file is in “C:\Program Files\MySQL\MySQL Server 5.5″).

#Path to the database root
datadir=”C:\ProgramData\MySQL\MySQL Server 5.5\data”

Note that C:\ProgramData\ is, by default, a hidden folder. If you can’t find it, it is either you have not changed the settings in folder options or you have no admin rights.

Can you change this data directory? Yes. First, you need to stop MySQL from running (closing the MySQL Client window is not enough). You can use Windows task manager to end the program mysqld.exe. Second, move the entire data folder from old path to the new path. Third, change the data directory path in my.ini. In my case, my new data directory path is:

datadir=”C:/Data/”

Now, you can move your folder (with the .myd, .myi, and .frm files) to this new data directory. Reboot.

Follow Andrew Dyck’s Step 0 and Step 1. Note that the connectionstring() used in Step 1 was only added to -odbc- late last year (see Stata’s blog). Make sure your Stata is up-to-date.

It also helps to learn a few SQL syntax so that you can load only selected lines or those that meet specific conditions, or load everything (see MySQL documentation). To illustrate, we use the built-in MySQL database information_schema.

Example 1: Load rows 1-20 of table TABLES
odbc load, exec(“SELECT * FROM TABLES LIMIT 20“) conn(“DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=information_schema;UID=root;PWD=pass;”) clear


Example 2: Load observations where the variable ROW_FORMAT==”Fixed”
odbc load, exec(“SELECT * FROM TABLES WHERE ROW_FORMAT=’Fixed’”) conn(“DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=information_schema;UID=root;PWD=pass;”) clear

Example 3: Load everything from table TABLES
odbc load, exec(“SELECT * FROM TABLES“) conn(“DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=information_schema;UID=root;PWD=pass;”) clear



*Control Panel–>Administrtive Tools–>Data Sources (ODBC)

** Complications arise if you have no admin rights to the PC. I am using a 64-bit Windows 7 machine; I am not sure if this works the same in other platforms as well.

4 Responses

  1. Steps -2 as you’ve outlined here is extremely important and I’m glad that you’ve posted this (as well as regretting not including more information about MySQL setup in my post).

    On my windows computers I use WAMP (Windows Apache MySQL PHP) rather than the MySQL install alone. The biggest benefit of using WAMP is that it comes with PHPMyAdmin which is a great help in managing and browsing your databases in a web-browser and it really easy to setup and install.

    I’ve also recently been favouring the use of -dsn- with -odbc- over -connectionstring- because if you’re using the -pwd- argument in a do-file you are storing your DB password in plain-text and this could easily fall into the wrong hands……yes, I’m probably being paranoid. :)

    Thanks for this write-up Mitch!!

  2. Dear Mitch & Andrew,

    Very interesting posts. Anyway I would like to ask if there is any possibility to connect STATA to a web based MySQL db through entering as address the server IP? Certainly this would be very handy to aces the data remotely.

    Thanks,
    Kujtim

  3. Hey

    Thank you for wonderful tutorial. However, as Kujtim mentioned, that would be very useful if you can provide an example of how to connect with web based mysql with Stata. Looking forward to see it soon…

    Thanks and regards,
    Aung

  4. Great to know all this. I had been using Microsoft SQL server but never used MySQL with Stata. Will be great to know if you give an example of how to connect with mysql with Stata remotely.

Leave a Reply

%d bloggers like this: