5
$\begingroup$

My first attempt at using MMA's database capabilities appears to be a failure:

ref = DatabaseReference[FindFile["optiondb_tb_options.sql"]];
DatabaseConnect[ref]

enter image description here

So far so good. But then:

RelationalDatabase[ref]

enter image description here

The database is a standard MySQL database and is not encrypted.

$\endgroup$
6
  • 1
    $\begingroup$ I use V 12 data base all the time. Never had problem. But I do not use the method you show to access the database. I use JDBC driver and OpenSqlConnection as described here reference.wolfram.com/language/DatabaseLink/ref/… I use SQLite as my database btw. $\endgroup$
    – Nasser
    May 13 '19 at 12:26
  • 3
    $\begingroup$ The .sql file is not a database, it is a dump or a file that is needed to create one. You need a running MySQL server with your database already created and setup, not an .sql file. The only file-based db that is currently supported by the new relational db functionality is SQLite, but that is not an .sql file either. The fact that the connection returns "Success" on .sql file is probably a bug, that I will report. $\endgroup$ May 13 '19 at 13:42
  • 4
    $\begingroup$ @Nasser If you use DatabaseLink, this means that you don't use the new relational db functionality. These two are complementary and unrelated. $\endgroup$ May 13 '19 at 13:43
  • 1
    $\begingroup$ According to "new in v12", MMA can connect to a MySQL db: wolfram.com/language/12/relational-database-connectivity/… $\endgroup$ May 14 '19 at 11:44
  • 1
    $\begingroup$ "That MMA only currently works with SQLite is an issue I had not foreseen" - you did not interpret what I wrote correctly. MySQL is supported, but the ".sql" script is not MySQL. Please see my answer. $\endgroup$ May 14 '19 at 12:30
9
$\begingroup$

General

The new relational databases functionality does currently support SQLite, MySQL, PostgreSQL, MS SQL, and the support for Oracle is coming very soon. There are a few steps one has to go through to expose the database as a source of entities in WL.

In all cases, the database must be prepared / exist before you try to connect. Please note that having ".sql" files does not cut it - these files are on their own not a database, they are the scripts needed to create one.

For server-based database backends (all supported ones except SQLite are such), be sure to have a running db instance with connection information, such as

  • the server (name or IP address)
  • database port
  • database name
  • username
  • password

available, before you try to connect. If the database is not administered by you, such information should be available to your sys. admin, so you will need to request it.

Connecting to the database

The way to connect to server-based database backends (MySQL, PostgreSQL, MS SQL, Oracle - support for the latter coming very soon) - is to have the server set up and use either the connection url string or, alternatively, an assoc with the specs.

For MySQL, it may look like this. First, define a connection:

connectionMYSQL =  DatabaseReference[
  {
   "Backend" -> "mysql",
   "Port" -> 3306,
   "Host" -> "localhost",
   "Username" -> "test",
   "Password" -> "test",
   "Name" -> "classicmodels"
  }
]

Connection is an inert object, it doesn't do anything. Now, connect to the database (RelationalDatabase constructor performs the connection and inspects the database structure, returning the inert RelationalDatabase[...] object that contains metadata about database structure):

rdb = RelationalDatabase@connectionMYSQL

(*  RelationalDatabase[Table count: 8 Backend: MySQL] *)

You can see which tables are in there:

rdb["Tables"]
(* 
   {
      productlines,orders,products,orderdetails,
      employees,payments,offices,customers
   }
*)

There are other properties you can extract from this object, but I will not get into that here.

Creating and registering EntityStore object

Now you can create an entity store out of RelationalDatabase object:

es = EntityStore @ rdb

(* EntityStore[Type count: 8 RelationalDatabase: MySQL] *)

Finally, you register the entity store:

EntityRegister @ es

(* 
   {
      productlines,orders,products,orderdetails,
      employees,payments,offices,customers
   }
*)

Running queries

Now you are ready to run queries. For example:

EntityValue[
  "offices", 
  {"officeCode", "addressLine1", "city", "state", "country"}
]

(* 
 {{"1", "100 Market Street", "San Francisco", "CA", "USA"}, 
  {"2", "1550 Court Place", "Boston", "MA", "USA"}, 
  {"3", "523 East 53rd Street", "NYC", "NY", "USA"}, 
  {"4","43 Rue Jouffroy D'abbans", "Paris", Missing["NotAvailable"],"France"}, 
  {"5", "4-1 Kioicho", "Tokyo", "Chiyoda-Ku", "Japan"}, 
  {"6", "5-11 Wentworth Avenue", "Sydney", Missing["NotAvailable"], "Australia"}, 
  {"7", "25 Old Broad Street", "London", Missing["NotAvailable"], "UK"}
 }
*)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.