Connecting Android to SQL Server using JDBC

Question

I am currently making an application for Android that is supposed to synchronize it's data to MSSQL Server 2008. I am currently testing out ways to make it work, since I have never done it before. I should mention that the device will sync whenever it is connected to the USB port and not through WiFi, since the company doesn't want to register the devices on the network.

So far this is what I've worked out to connect Java to SQL Server. This is a simple Select code (I am currently using SQLExpress to test):

  String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;" +
             "databaseName=Android;integratedSecurity=true;";

  // Declare the JDBC objects.
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;

  try {
     // Establish the connection.
     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
     con = DriverManager.getConnection(connectionUrl);

     // Create and execute an SQL statement that returns some data.
     String SQL = "SELECT * FROM AndroidTest;";
     stmt = con.createStatement();
     rs = stmt.executeQuery(SQL);

     // Iterate through the data in the result set and display it.
     while (rs.next()) {
        System.out.println(rs.getString(1) + " " + rs.getString(2));
     }
  }

  // Handle any errors that may have occurred.
  catch (Exception e) {
     e.printStackTrace();
  }
  finally {
     if (rs != null) try { rs.close(); } catch(Exception e) {}
     if (stmt != null) try { stmt.close(); } catch(Exception e) {}
     if (con != null) try { con.close(); } catch(Exception e) {}
  }

Now, I've tried the same thing in Android and this is what it looks like:

package com.example.testsqlserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;

public class MainActivity extends Activity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    }

    public void clickSend(View view) {
        (new Thread(new TestThread())).start();
    }
    public class TestThread extends Thread {
      public void run() {
          String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;" +
                     "databaseName=Android;integratedSecurity=true;";

          // Declare the JDBC objects.
          Connection con = null;
          Statement stmt = null;

          try {
             // Establish the connection.
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             con = DriverManager.getConnection(connectionUrl);

             //Get information from EditText
             EditText txtTest = (EditText)findViewById(R.id.txtTest);
             EditText txtName = (EditText)findViewById(R.id.txtName);
             String test = txtTest.getText().toString();
             String name = txtName.getText().toString();

             // Create and execute an SQL statement that returns some data.
             String SQL = "INSERT INTO AndroidTest VALUES('" + test + "', '" + name + "');";
             stmt = con.createStatement();
             stmt.executeUpdate(SQL);
             Log.e("Success", "Success");
          }

          // Handle any errors that may have occurred.
          catch (Exception e) {
             e.printStackTrace();
              Log.e("Error", e.toString());
          }
          finally {
             if (stmt != null) try { stmt.close(); } catch(Exception e) {}
             if (con != null) try { con.close(); } catch(Exception e) {}
          }
      }

      public void main(String args[]) {
          (new TestThread()).start();
      }
    }
}

In the first example it works perfectly, but in the second example it gives me this error:

12-17 20:15:12.589: E/Error(1668): com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "failed to connect to /127.0.0.1 (port 1433) after 403ms: isConnected failed: ECONNREFUSED (Connection refused). Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

I had that error the first time I ran the first code and I just had to enable port 1433 in the SQL Server Settings. I don't understand, though, why isn't it working on the second table. It is the same code, the only difference is that it's executed through a button press and that I have it running on a separate thread.

Any help will be appreciated, thank you.

Answer 1

See this section on Emulator Netorking.

You need to use 10.0.2.2 which allows you to communicate from an emulator to the development machines 127.0.0.1 address.

You may also have to do some port redirection (see further in that documentation).