Direct Access to SQL Server From Android

• Download source - 4.3 MB

Introduction

Sometimes, we need to deploy mobile app for local network. Open source JDBC 3.0 type 4 driver for Microsoft SQL Server (6.5 up to 2012) and Sybase ASE. jTDS is a complete implementation of the JDBC 3.0 spec and the fastest JDBC driver for Microsoft SQL Server.

Background

1. Basic Android development
2. Can using SQL Server

GUI XML Code As

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="8dp"
    android:background="#336699"
    android:orientation="vertical"
    tools:context="${relativePackage}.${activityClass}" >

    <EditText
        android:id="@+id/etFirstName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:padding="4dp"
        android:background="#eeeeee"
        android:hint="Firstname"
        android:textColor="#000000"
        android:layout_marginBottom="4dp"
        android:textSize="24dp" >

        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/etLastName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="#eeeeee"
        android:hint="Lastname"
        android:padding="4dp"
        android:textColor="#000000"
        android:textSize="24dp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:padding="4dp" >

        <Button
            android:id="@+id/btnConnect"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Connect" />

        <Button
            android:id="@+id/btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
             android:layout_weight="1"
            android:text="Add new" />
    </LinearLayout>

    <TextView
        android:id="@+id/tvDs"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="#ffffff"
        android:padding="8dp"
        android:text=""
        android:textAppearance="?android:attr/textAppearanceMedium" />

</LinearLayout>

Register Access Permission for your App

StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);

Connect to Microsoft SQL Server by JDBC

Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(""
                            + "jdbc:jtds:sqlserver://172.16.160.81/northwind;instance=SQL2008;"
                            + "user=sa;password=sa;");

Description

• 172.16.160.81 is your database's server address
• northwind: access database
• SQL2008 is server instance
• user and password

Reading data:

Statement comm;
try {
// create command to read data
    comm = conn.createStatement();
    ResultSet rs = comm.executeQuery("Select EmployeeID, Firstname From Employees");
    String msg = "";
// read all row
    while (rs.next()) {
        msg += "\nID: " + rs.getInt("EmployeeID") + " Name: "
                + rs.getString("Firstname");
    }
    tv.setText(msg);
} catch (SQLException e) {
    tv.setText(e.toString());
}

AddNew Record

PreparedStatement comm;
try {
    comm = conn.prepareStatement("insert into Employees("
            + "firstname, lastname) values(?,?)");
    comm.setString(1, etFirst.getText().toString());
    comm.setString(2, etLast.getText().toString());   
// run commandto add new record                 
    comm.executeUpdate();
} catch (SQLException e) {
    tv.setText(e.toString());
}

Conclusion

Sometimes, we need to protect our database server, only access in local network. For more details about jTDS, see: http://jtds.sourceforge.net/.

History

• Initial version 1.0