With the package DatabaseLink
I can open a connection to my MYSQL database. Then I can use SQL commands like
SQLExecute[conn, "SELECT * FROM table WHERE column ='some string'"]
SQLExecute[conn, "SELECT * FROM table WHERE column LIKE '%abcd%'"]
With the first command, we look for rows where the column value equals the given string, with the second one for rows where the column string contains the substring 'abcd'.
In Mathematica 12 we have the experimental functions DatabaseReference
, RelationalDatabase
and EntityStore
. The documentation describes how we can use these functions to set up a connection with a database. That works fine. I can inspect all rows of the tables in my database as Entities
, of which the types are the table names. So many of these Entities
have strings as properties.
Now I want to do queries on the database. For example, return those Entities
of type 'table' for which property 'column' equals 'some string'. That is the first SQL-command above. I found it can be done in the following way ( I am not sure if that is the best way for doing a query):
EntityList @ EntityClass["table", "column"->"some string"]
For the second SQL-example, I tried a lot, e.g.
EntityList @ EntityClass["table", "column"->(StringContainsQ[ #, "abcd"]&)]
This results in an error message that the StringContainsQ
expression cannot be compiled to SQL.
Any hint how I can do a query on substrings of string values is highly welcome.
EntityFunction
, but I'm a little unfamiliar with it right now. $\endgroup$ – Carl Lange May 9 '19 at 19:42