Wednesday, October 30, 2013

Android and Microsoft SQL (MS SQL SERVER 2008) marriage

All you have to do is use the appropriate driver, being a opensource fan , i'd recommend using JTDS.

Instructions below assumes you are using

  • Android Developer Tools Build id: v22.0.1-685705,
  • Eclipse Version: 4.2.1.v20130118 Build id: M20130204-1200
  • JTDS 1.2.5 

 though things will work out other versions higher than this and possibly lower as well , its just to ensure a known state where things worked out.

So, once the JTDS is jar is download, first step is to include jar to your build path




Next, though it seems like a bug, go to order and Export tab and enable jtds


Now, for the database connection , all network operations should be in different thread, ie away from the main UI thread,  so use  AsyncTask.
package com.alkber.erp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;

/**
 * This is as demo code to demonstrate db connection and operations and not 
 * meant for a live run. 
 * 
 * @author Althaf K Backer
 *
 */

public class DBTestActivity extends Activity {

 private Connection conn;

 @Override
 protected void onCreate(Bundle savedInstanceState) {
  
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_dbtest);
  
 }

 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  
  getMenuInflater().inflate(R.menu.dbtest, menu);
  return true;
  
 }
 @Override
 protected void onResume() {
  
  super.onResume();
  (new DBConnection()).execute(null, null, null);
 
 }
 
 @Override 
 protected void onPause() {
  
  super.onPause();
  try {
   
   conn.close();
   
  } catch (SQLException e) {
  
   e.printStackTrace();
   
  }
  
 }
 class DBConnection extends AsyncTask<String, String, String> {

  @Override
  protected String doInBackground(String... arg0) {

   try {

    Log.e("MSSQL", "Attempting to connect");

    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    conn = DriverManager.getConnection(
      "jdbc:jtds:sqlserver://yourserver.com/DBName",
      "username", "password");

    Log.e("MSSQL", "Connected");

   } catch (Exception e) {

    e.printStackTrace();
    Log.e("MSSQL", e.toString());

   }

   return null;
  }

 }

 class UserInfo {

  String userID;
  String userName;
  String PhoneNo;
  String age;

  public UserInfo(String userID, String userName, String PhoneNo,
    String age) {

   this.userID = userID;
   this.userName = userName;
   this.PhoneNo = PhoneNo;
   this.age = age;

  }

  public String getUserID() {
   return userID;
  }

  public void setUserID(String userID) {
   this.userID = userID;
  }

  public String getUserName() {
   return userName;
  }

  public void setUserName(String userName) {
   this.userName = userName;
  }

  public String getPhoneNo() {
   return PhoneNo;
  }

  public void setPhoneNo(String phoneNo) {
   PhoneNo = phoneNo;
  }

  public String getAge() {
   return age;
  }

  public void setAge(String age) {
   this.age = age;
  }

 }

 class DBOperation {

  public List<UserInfo> getAllUsers() throws SQLException {

   Statement statement = getStatement(conn);
   
   List<UserInfo> userlist = new ArrayList<UserInfo>();
   
   ResultSet rs = statement.executeQuery("SELECT * FROM UserInfoTable");
   rs.next();
   int count = 0;
   
   while (rs.next()) {
    
    userlist.add(new UserInfo(rs.getString(1), rs.getString(2),
      rs.getString(3), rs.getString(4)));
    count++;

   }

   rs.close();
   statement.close();
   return userlist;
   
  }

  public void addUser(UserInfo info) {
   
   Log.e("MSSQL", "in adduser");
   
   Statement statement = getStatement(conn);
   
   try {
    
    ResultSet rs = statement.executeQuery("INSERT INTO UserInfoTable "
      + " VALUES ('1001', 'Bob', '333333', '33')");
    rs.close();
    statement.close();
    
   } catch (SQLException e) {
    
    e.printStackTrace();
    
   } 

  }

  private Statement getStatement(Connection connection) {
   
   try {
    
    return connection.createStatement();
    
   } catch (Exception e) {
    
    throw new RuntimeException(e);
    
   }
  }

 }

}




5 comments:

  1. I can't get data from MSSQL2008 ....
    I got error ' null '

    ReplyDelete
    Replies
    1. it been a year right know lol , can you share with us the code about how to connect android to MSSQL2008 ?

      Delete
    2. This comment has been removed by the author.

      Delete
    3. @Mohamed Brahen can you ask your doubts specifically from the above code ? The code is quite sefl explanatory. Please be free to ask.

      Delete
  2. Can you be more specific with your code you have adapted ? Kindly use a pastebin to show your code.

    ReplyDelete