Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I ran into this over the weekend at a hackathon on Mac OSX - took me a solid 4 hours to piece everything together despite having a few reference materials (mentioned at the end). I didn't find an easy walk-through, so I decided to post one while it is fresh in my mind.</p> <p>I'm not sure of the compatibility with Windows, but hopefully these instructions will make it easier for you too.</p> <p>I was trying to get R and MySQL to communicate in a local environment (there may need to be changes for a server environment). I use XAMPP (though I didn't use RMySQL for the connection), but in the end I was able to use a PHP page to write an R file, execute that file, and have R write to a MySQL table. To the best of my knowledge this only works for MacOSX...</p> <p>All software used was in dmg form so no binary installs necessary.</p> <ol> <li><p><a href="https://cran.r-project.org/bin/macosx/" rel="nofollow noreferrer">Download R</a> and run some basic commands to make sure that you have it working.</p></li> <li><p>In R, you need to install RODBC (if you don't have it already). Type this into the R console.</p></li> </ol> <pre><code>install.packages("RODBC") </code></pre> <p>This installs RODBC, but since OS Mavericks, certain files are no longer included, so you get an error message </p> <blockquote> <p>ODBC headers sql.h and sqlext.h not found</p> </blockquote> <p>and you need to get the sql.h and sqlext.h files in the right place.</p> <p>To do this the easiest way, make sure that you have <a href="http://brew.sh/" rel="nofollow noreferrer">homebrew</a> installed (easy instructions). Then use this <a href="https://stackoverflow.com/a/29214529/2330917">code</a> in terminal to make the install.</p> <p>Once that's done, you enter into the R console one more time</p> <pre><code>install.packages("RODBC") </code></pre> <ol start="3"> <li><p>Search <a href="https://dev.mysql.com/downloads/connector/odbc/" rel="nofollow noreferrer">MySQL for the appropriate ODBC installation</a>. I'm running Mac OSX 10.6 so I downloaded the dmg and installed it. This took care of itself.</p></li> <li><p>Now comes the tricky part. Apparently Mac OX took out the ODBC Administrator after a recent OS release, so you need to download ODBC Manager (<a href="http://www.odbcmanager.net/" rel="nofollow noreferrer">http://www.odbcmanager.net/</a>). It too is a dmg file so just drag and drop to your utilities folder.</p></li> </ol> <p>I had difficulties with the 5.3.6 dmg install (kept failing), so I installed 5.2.7 instead.</p> <ol start="5"> <li><p>Open ODBC Manager. You need to configure the DSN, so click the tab "System DSN" and click "add". </p></li> <li><p>You'll get a popup window asking you to select a driver. Mine had "MySQL ODBC 5.2 Driver" based on my MySQL ODBC install. Click "Ok". If you don't see the driver, then you need to confirm that the MySQL ODBC installed.</p></li> <li><p>In the next popup window, make the Data Source Name (DSN) whatever you want - but remember that this is the name you need to use to call from R. In the keyword area below (keywords will be in quotes and the value will be in parentheses), ADD</p> <p>"database" (with value of your database name)</p> <p>"server" (for the local environment do NOT use localhost - instead use the local IP address 127.0.0.1. *** This was the KEY piece for me)</p> <p>"uid" (database user ID)</p> <p>"pwd" (database password)</p> <p>"socket" (not sure if this was required, but after multiple tutorials it was left in my configuration and things work, so maybe you need it. You can find your socket location in my.cnf - do a spotlight search. The socket file location is under CLIENT)</p> <p>Here's what my configuration looked like:</p> <p>DSN ("test" - this was the at the top)</p> <p>database ("televisions")</p> <p>socket ("/Applications/XAMPP/xamppfiles/var/mysql.sock")</p> <p>uid ("root")</p> <p>pwd ("")</p> <p>server ("127.0.0.1")</p></li> <li><p>In R, execute below - I believe these last 3 steps need to be done every time you start R and before you make a MySQL query.</p> <p>library(RODBC)</p></li> <li><p>Make sure that you've turned on MySQL and Apache from the XAMPP control panel.</p></li> <li><p>Then execute</p> <p>odbcConnect("test") - notice how I used my DSN in the double quotes. Interchange as necessary.</p></li> </ol> <p>This should get you up and running. You can read other tutorials about making MySQL queries in R.</p> <p>I hacked this together from a lot of great posts on Stack Overflow (thanks everyone!), random other sites/email exchange histories, and the "R In A Nutshell" book by Joseph Adler, but let me know if I missed something or it's unclear.</p> <p>Good luck!</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to Connect R with MySQL or how to install RMySQL package?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USElliot Koss
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload