5
$\begingroup$

I can connect to the local Database(installed in my system) from Mathematica via

SQLConnection[JDBC["Microsoft SQL Server(jTDS)", "localhost:1433/TestDatbase"], 
"Catalog" -> Automatic, "Description" -> None, "Name" -> "DemoSql", 
"Password" -> "test", "Properties" -> {}, "ReadOnly" -> False, 
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted", 
"UseConnectionPool" -> False, "Username" -> "DemoLogin", "Version" -> 2.]

But When I try to connect to remote database I can't. I have following details

Sever Name: xxxx7ezff6.database.windows.net
User Name: xxxxxxxx
password: password
Database: AdventureWorks2012

I can see this remote database in my SQL Server Management Studio(SSMS) by using server name, user name and password. But I don't know how can I connect to this remote database from Mathematica. I tried like this

SQLConnection[JDBC["Microsoft SQL Server(jTDS)", 
"xxxx7ezff6.database.windows.net:1433/AdventureWorks2012"], 
"Catalog" -> Automatic, "Description" -> None, "Name" -> "AdvTest", 
"Password" -> "password", "Properties" -> {}, "ReadOnly" -> False, 
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted", 
"UseConnectionPool" -> False, "Username" -> "xxxxxxxx", "Version" -> 2.]

It's end up with error. I think there is problem with hostname and port, But I don't have these details. How can I connect to this remote database?

Note: I don't have IP address as well. The port Number I have given here has been set by mathematica as default. I tried without port number as well, but no use.

$\endgroup$

4 Answers 4

1
$\begingroup$

You can try changing the driver and, instead of the MSFT driver "Microsoft SQL Server(jTDS)", use the "jtds_sqlserver" driver. I'm using it to connect to my remote SQL Server and works fine.

$\endgroup$
2
  • $\begingroup$ as I mention in my question I have server name only not hostname and port. what should I give there? $\endgroup$
    – niren
    Jul 29, 2013 at 9:41
  • $\begingroup$ I tried with jtds_sqlserver but no luck. $\endgroup$
    – niren
    Aug 2, 2013 at 11:43
1
$\begingroup$

I have fought with this problem for a while now and have finally solved it. I tried connecting using windows authentication first as per this post but I just couldn't get it to work. I also tried to use the GUI (wizard) to create the connection but this seemed buggy. The details below refer to a database on a server which has the Mathematica network licence manager on it as well as Mathematica 8 installed on it. I use Mathematica 8 on my local machine with one of the licences from the network licence.

This is how i got it to work:

  1. I made sure that I could connect via SQL Server Management Studio to the database on the server from my local machine using the SQL Authentication credentials I had. I could connect thus I knew there were no network problems and my SQL Authentication credentials were working.

  2. I found the correct name of the server - it was the name as used when connecting via SQL Server Management studio but without the SQL instance included.

server name i used

  1. I selected two databases that I wanted to connect to - they are called master and model

  2. I compiled a notebook as below and was able to connect.

    Loading the package

    Needs["DatabaseLink`"]

Details: ECSDBN-SRV-09.enerserv.local sa Password Catalog: master 192.168.1.11 Open the connection

conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09/model"], 
  "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[1, "Open", "Catalog" -> "model", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

conn1 = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09/master"], 
  "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[2, "Open", "Catalog" -> "master", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

(*Here I have not specified the database nane after the server name and it just seems to pick the one on the top of the database list.*)

conn2 = OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09"],
   "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[3, "Open", "Catalog" -> "master", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

(*Look at the open connection to make sure.*)

SQLConnections[]

{SQLConnection[1, "Open", "Catalog" -> "model", 
  "TransactionIsolationLevel" -> "ReadCommitted"], 
 SQLConnection[2, "Open", "Catalog" -> "master", 
  "TransactionIsolationLevel" -> "ReadCommitted"], 
 SQLConnection[3, "Open", "Catalog" -> "master", 
  "TransactionIsolationLevel" -> "ReadCommitted"]}

(*Check what tables exist through the current connection*)

SQLTables[conn]

{}

SQLTables[conn1]

{SQLTable["MSreplication_options", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_db", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_dev", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_usg", "TableType" -> "TABLE"], 
 SQLTable["spt_monitor", "TableType" -> "TABLE"], 
 SQLTable["spt_values", "TableType" -> "TABLE"]}

SQLTables[conn2]

{SQLTable["MSreplication_options", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_db", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_dev", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_usg", "TableType" -> "TABLE"], 
 SQLTable["spt_monitor", "TableType" -> "TABLE"], 
 SQLTable["spt_values", "TableType" -> "TABLE"]}

(*Check that I can access data from one of the tables in the connection using the SQL query approach*)

SQLExecute[conn1, "SELECT * FROM MSreplication_options", 
  "GetAsStrings" -> True] // InputForm

{{"transactional", "1", "90", "0", "0", "0"}, {"merge", "1", "90", "0", "0", "0"}, {"security_model", "1", "90", "0", "0", "0"}}

(*Try another server*)

conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-04.enerserv.local"], 
  "Username" -> "sa", "Password" -> "P@ssw0rd"]

JDBC::error: Network error IOException: Connection refused: connect >>

$Failed

However when I tried the same thing with another server which does not have Mathematica installed on it (this may or may not be the problem) I could not connect. Also, the server I was able to connect to has two seperate instances of SQL on it - SQL 2005 and SQL 2008. Mathematica connected me to the 2005 instance by default and I can't find out how to connect to the 2008 instnace. I hope this helps a little bit.

Reference material: DatabaseLink/tutorial/Overview
$\endgroup$
1
1
$\begingroup$
Use this code : 

public class ConnectionClass {
    String ip = "192.168.0.131";
    String classs = "net.sourceforge.jtds.jdbc.Driver";
    String db = "Andro";
    String un = "sa";
    String password = "Admnsql";
    @SuppressLint("NewApi")
    public Connection CONN() {
    StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
    .permitAll().build();
    StrictMode.setThreadPolicy(policy);
    Connection conn = null;
    String ConnURL = null;
    try {
    Class.forName(classs);
    ConnURL = "jdbc:jtds:sqlserver://" + ip + ";"
    + "databaseName=" + db + ";user=" + un + ";password="
    + password + ";";
    conn = DriverManager.getConnection(ConnURL);
    } catch (SQLException se) {
    Log.e("ERRO", se.getMessage());
    } catch (ClassNotFoundException e) {
    Log.e("ERRO", e.getMessage());
    } catch (Exception e) {
    Log.e("ERRO", e.getMessage());
    }
    return conn;
    }
}
$\endgroup$
0
$\begingroup$

Here is the connection string that I use to SQL Server connection:

conn=OpenSQLConnection[
   JDBC["Microsoft SQL Server(jTDS)", "185.50.123.132"], 
   "Username" -> "myUser", 
   "Password" -> "MyPass" 
]

Have you tried using the direct IP of your remote machine?

$\endgroup$
1
  • $\begingroup$ I used the way you given as conn = OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "192.x.x.x"], "Username" -> "test", "Password" -> "password"]. It's also not working $\endgroup$
    – niren
    Aug 2, 2013 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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