Java – Use JDBC to connect Android to SQL Server

Use JDBC to connect Android to SQL Server… here is a solution to the problem.

Use JDBC to connect Android to SQL Server

I’m currently making an app for Android that should sync its data to MSSQL Server 2008. I’m currently testing ways to make it work, as I’ve never done this before. I should mention that the device syncs as long as it is connected to a USB port and not over WiFi, as the company does not want to register the device on the network.

So far, this is how I connected Java to SQL Server. Here’s a simple Select code (which I’m currently testing with SQLExpress):

  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 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 worked perfectly, but in the second it gave 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

got this error the first time I ran the first code, I just need to enable port 1433 in the SQL Server settings. I don’t understand why it doesn’t work on the second table though. It’s the same code, the only difference is that it’s executed by pressing a button, whereas I let it run on a separate thread.

Any help would be greatly appreciated, thank you.

Solution

See this section on Emulator Netorking

You need to use 10.0.2.2, which allows you to communicate with the development machine 127.0.0.1 address from the emulator.

You may also need to do some port redirection (see further in that document).

Related Problems and Solutions