Message Boards Message Boards

Connect a database in Azure to Mathematica?

GROUPS:

Hi I have connection to SQL database via Azure Active Directory. How can I connect it to Mathematica. I have no idea how I can use this http://reference.wolfram.com/language/DatabaseLink/ref/OpenSQLConnection.html.

I can not see way to set path to database.

POSTED BY: petr stanek
Answer
4 months ago

Do you know how to connect to it without Mathematica? What SQL database are you using?

POSTED BY: Gustavo Delfino
Answer
4 months ago

This is a example on my laptop. I work with Oracle on my local machine:

Needs["DatabaseLink`"]
Needs["JLink`"]
$connection=OpenSQLConnection[JDBC["Oracle","localhost:1521/aaaa"],"Username"->"bbbb","Password"->"cccc"]

'aaaa' is the name of the database 'bbbb'/çcc' is my password & username to connect to my local database.

And then I can make my first SQL statement:

var=SQLExecute[$connection,"select * from tabelname  WHERE ROWNUM <= 972"];
POSTED BY: Michiel van Mens
Answer
4 months ago

Our back office adminer - Mysql , or Power BI

POSTED BY: petr stanek
Answer
3 months ago

-Mysql I tried https://www.mkyong.com/jdbc/how-to-connect-to-mysql-with-jdbc-driver-java/ to transform to Mathematica but It failed.

OpenSQLConnection[JDBC["MySQL(Connector/J)", "locahost"], 
 "Username" -> "xxxxx", "Password" -> "xxxx"]
JDBC::error: Access denied for user 'name@'localhost' (using password: YES)

Password is correct, but I have read only access. So I do not know if this is the reason why it failed.

We use also Power BI.

POSTED BY: petr stanek
Answer
3 months ago

I have created dump of my Sql database. When I want to create sql connection, I will replace "localhost:1521/aaaa" by C://.......... . Is that correct? But this error appears JDBC::error: Cannot load connection class because of underlying exception: 'java.lang.NumberFormatException: For input string: "\Users............................"'.

POSTED BY: petr stanek
Answer
3 months ago

what is the file format?

POSTED BY: Michiel van Mens
Answer
3 months ago

SQLText file

POSTED BY: petr stanek
Answer
2 months ago

Can you import the file with Import[]

POSTED BY: Michiel van Mens
Answer
2 months ago

No I cant see .sql format as option

POSTED BY: petr stanek
Answer
2 months ago

There is not a standard .sql format as each sql database has unique features so it is usually meant to be used within the same system. To export data from say MySQL into Mathematica you could use the mysqldump command with the --tab option:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-format-options

But you should be able to connect to MySQL directly using DatabaseLink. The access denies error that you are getting can probably be solved by using the MySQL GRANT command.

POSTED BY: Gustavo Delfino
Answer
2 months ago

Group Abstract Group Abstract