# Accessing mySQL database using DatabaseReference[ ]?

Posted 9 days ago
221 Views
|
5 Replies
|
0 Total Likes
|
 Hi All,I'm struggling to understand the best way to access a mySQL database. I would like to execute SQL commands but just cant seem to get it to work. I use the following approach via entitystore; connectionMYSQL = DatabaseReference[{"Backend" -> "mysql", "Port" -> 3306, "Host" -> "localhost", "Username" -> "root", "Password" -> "", "Name" -> "socialdata"}] rdb = RelationalDatabase@connectionMYSQL es = EntityStore@rdb I can see the tables in EntityStore but trying to get data via EntityValue doesn't seem to work. The following fails: EntityValue["table1", {"col1", "col2.... The other way of doing this is: conn = DatabaseReference[ URL["mysql://root@localhost:3306/socialdata"]] SQLExecute[conn, "SELECT * FROM .... which does not work!Ideally I would like to execute SQL commands. are there any good examples showing this working (especially for a local database)?
5 Replies
Sort By:
Posted 9 days ago
 Please provide the output you get and any error messages. The following failsdoesn't seem to workwhich does not work!
Posted 7 days ago
 SQLExecute[DatabaseReference[ Association[ "Backend" -> "mysql", "Name" -> "socialdata", "Port" -> 3306, "Host" -> "localhost", "Username" -> "root", "Password" -> ""]], "SELECT * FROM table1 WHERE 1 LIMIT 10 ", "ShowColumnHeadings" -> True] The output is as above. There dont seem to be any error messages. The output seems to just repeat the previous command. Apologies, I'm sure this is something very simple, I'm just starting to get into Mathematica!
Posted 6 days ago
 Hi S GSQLExecute is defined in the DatabaseLink package so you have to load it first Needs["DatabaseLink"] You need to open a connection using OpenSQLConnection and pass that to SQLExecute. Check the DatabaseLink User Guide for details.
 Thanks for that. Adding the final fix as it might be useful for others trying to do sql queries on a local mySQL database.The solution was to run the following; Needs["DatabaseLink"] OpenSQLConnection[] This opens up a database connection tool. Using this tool, I was able to insert the settings for the database and add it to the connections in the "connection tool". I called the database "SocialData" in the connection tool. Once the database had been added in the connections tool I could then run; Needs["DatabaseLink"] SQLExecute[ OpenSQLConnection[ "SocialData"], "SELECT * FROM table1 WHERE \ test='abc' LIMIT 10 "] This is working nicely now. Many thanks.
 Glad you got it working. One word of caution. The DB connection tool is handy, however, it stores the connection information locally so if you want to share your notebook with someone else on your team they will not be able to use that connection information to connect to the database. Since your DB is hosted on localhost that may not be an issue for you.You should see a file named SocialData.m here. You can copy the connection information from there into your notebook if needed. FileNames["*.m", FileNameJoin@{\$UserBaseDirectory, "DatabaseResources"}] `