Android Tutorial Connect Android to MS SQL Server Tutorial

Status
Not open for further replies.
A new more powerful framework is now available: Remote Database Connector.

This tutorial is a follow up to the previous tutorial that explained how to connect to a MySQL database using a PHP web service. This time we will use an ASP.NET web service to connect to a Microsoft SQL Server.

Android cannot directly connect to the database server. Instead we will use HttpUtils to connect to a simple ASP.NET script that will issue the query and return the result as a JSON string.

There are several possible solutions for the web service implementation:
1. Pre-set the possible queries in the ASP script and then choose one of the queries by setting a parameter in the Http request.
2. Pass the query as-is to the database.

While the second solution is more flexible and doesn't require any changes to the ASP.NET script, it can also be more vulnerable to security attacks. For example a hacker can send a "DROP TABLE" query and erase all the data.
Making the database user a "read-only" user will protect this attack.

In this tutorial I will post the ASP.NET script that handles any query and passes it directly to the server. You can of course modify it and make it support only a set of queries.

ASP.NET code:
B4X:
<%@ Page Language="C#"%>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    protected void Page_Load(object sender, EventArgs ec)
    {
        using (SqlConnection cn = new SqlConnection("Database=Test1; User Id=UserName; password=Password")) //change as needed
        {
            using (StreamReader sr = new StreamReader(Request.InputStream, Encoding.UTF8))
            {
                Response.ContentType = "text/plain";
                string c;
                c = Request.QueryString["query"]; //for debugging with the browser
                //you can set the query by adding the query parameter  For ex: http://127.0.0.1/test.aspx?query=select * from table1
                if (c == null)
                    c = sr.ReadToEnd();
                try
                {
                    SqlCommand cmd = new SqlCommand(c, cn);
                    cn.Open();
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                    while (rdr.Read())
                    {
                        Dictionary<string, object> d = new Dictionary<string, object>(rdr.FieldCount);
                        for (int i =0;i < rdr.FieldCount;i++)
                        {
                            d[rdr.GetName(i)] = rdr.GetValue(i);
                        }
                        list.Add(d);
                    }
                    JavaScriptSerializer j = new JavaScriptSerializer();
                    Response.Write(j.Serialize(list.ToArray()));
              
                } catch (Exception e)
                {
                    Response.TrySkipIisCustomErrors = true;
                    Response.StatusCode = 500;
                    Response.Write("Error occurred. Query=" + c + "\n");
                    Response.Write(e.ToString());
              
                }
                Response.End();
            }
        }
    }
</script>
This code depends on System.Web.Extensions.dll.
To use this code you should save it as a file with aspx extension and modify the connection string with your values.
You may need to put System.Web.Extensions.dll in the bin folder (you will get a compilation error when the script runs if it is required and missing).
SS-2011-12-11_16.03.27.png


Before trying to connect from the Basic4android program, it is recommended to test that the script is working by calling it from the browser. It will be easier to read the error message this way. To help with debugging you can send the query as a GET parameter (later it will be sent as the payload).
B4X:
http://www.example.com/test1.aspx?query=select * from table_1

SS-2011-12-11_16.11.33.png


Now for Basic4android program:
B4X:
Sub Process_Globals
   Dim ServerUrl As String
   ServerUrl = "http://www.example.com/test1.aspx"
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
      HttpUtils.CallbackActivity = "Main"
      HttpUtils.CallbackJobDoneSub = "JobDone"
   End If

   HttpUtils.PostString("Job1", ServerUrl, "SELECT col1, col2 FROM Table_1")
End Sub

Sub Activity_Resume
   If HttpUtils.Complete = True Then JobDone(HttpUtils.Job)
End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub JobDone (Job As String)
   If HttpUtils.IsSuccess(ServerUrl) Then
      Dim parser As JSONParser
      Dim response As String
      response = HttpUtils.GetString(ServerUrl)
      parser.Initialize(response)
      Dim rows As List
      rows = parser.NextArray

      'work with result
      'rows is a List. Each item is a Map with the columns names as keys and the db values as the values.
      For i = 0 To rows.Size - 1
         Log("Rows #" & i)
         Dim m As Map
         m = rows.Get(i)
         Log("col1=" & m.Get("col1")) 'log the values of col1 and col2
         Log("col2=" & m.Get("col2"))
      Next
   End If
   HttpUtils.Complete = False
End Sub
This is the main activity code. Calling HttpUtils.PostString sends the request. When the request completes, Sub JobDone will be executed. After parsing the response with JSON parser we get a List. Each item in the list represents a row in the results set. Each item is a Map with the column names as keys and the db values as values.

When running the example, make sure to check the logs. Errors will be posted there.

The logs after a successful run:
B4X:
** Activity (main) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
Rows #0
col1=23
col2=asdasd
Rows #1
col1=12323
col2=dwqd
Rows #2
col1=0
col2=1
Rows #3
col1=0
col2=1
** Service (httputilsservice) Destroy **
 

Attachments

  • MsSQL.zip
    6.3 KB · Views: 3,226
Last edited:

AlexChiu

New Member
Licensed User
Longtime User
Execute me, I used socket method real time to send/receive command,and build a server process function to get ms sql data.Is it right the method?
 
Last edited by a moderator:

svk123

Member
Licensed User
Longtime User
Hello,

I am getting a java.lang.exception on running this application. I called the webserver through the browser and it is returning valid data.

Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:anywheresoftware.b4a.samples.sqlserver
** Activity (main) Create, isFirst = true **
Starting Job: Job1
** Activity (main) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
An error occurred:
(Line: 30) End Sub
java.lang.Exception: Sub service_start signature does not match expected signature.
public static anywheresoftware.b4a.pc.RemoteObject anywheresoftware.b4a.samples.sqlserver.httputilsservice_subs_0._service_start() throws java.lang.Exception
** Activity (main) Resume **

Any help will be appreciated.
Thanks,
SVK
 

lanadmin

Member
Licensed User
Longtime User
Hello,

I am getting a java.lang.exception on running this application. I called the webserver through the browser and it is returning valid data.

Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:anywheresoftware.b4a.samples.sqlserver
** Activity (main) Create, isFirst = true **
Starting Job: Job1
** Activity (main) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
An error occurred:
(Line: 30) End Sub
java.lang.Exception: Sub service_start signature does not match expected signature.
public static anywheresoftware.b4a.pc.RemoteObject anywheresoftware.b4a.samples.sqlserver.httputilsservice_subs_0._service_start() throws java.lang.Exception
** Activity (main) Resume **

Any help will be appreciated.
Thanks,
SVK


Change Sub Service_Start to this
Sub Service_Start (StartingIntent As Intent)

That will solve the issue. May I recommend you check out this solution though. Its much more robust. Its a better method for connecting to SQL.

http://www.b4x.com/android/forum/threads/remote-database-connector-connect-to-any-remote-db.31540/
 
Last edited:

svk123

Member
Licensed User
Longtime User
Change Sub Service_Start to this
Sub Service_Start (StartingIntent As Intent)

That will solve the issue. May I recommend you check out this solution though. Its much more robust. Its a better method for connecting to SQL.

http://www.b4x.com/android/forum/threads/remote-database-connector-connect-to-any-remote-db.31540/

Thank you very much for your help! Applying the change you suggested resolved my issue. Regarding your recommendation, I had actually tested the method that you have recommended and that is also working fine. However, I am trying to build a mobile application that will access enterprise data on the server. Please correct me if my understanding is wrong, however I felt that in RDC, if a user knew the server IP and definition of the query e.g. "select_animal", he could actually run that query on the server from any application and get access to data without any further authentication. I was thinking of passing user name and password as parameters with each query call and getting the user authenticated by my server side code before returning the result. Please advise if this is the correct approach or overkill or if there is another more efficient method. I am a new newbie when it comes to android programming.

Once again, thank you for your help.
 

lanadmin

Member
Licensed User
Longtime User
Thank you very much for your help! Applying the change you suggested resolved my issue. Regarding your recommendation, I had actually tested the method that you have recommended and that is also working fine. However, I am trying to build a mobile application that will access enterprise data on the server. Please correct me if my understanding is wrong, however I felt that in RDC, if a user knew the server IP and definition of the query e.g. "select_animal", he could actually run that query on the server from any application and get access to data without any further authentication. I was thinking of passing user name and password as parameters with each query call and getting the user authenticated by my server side code before returning the result. Please advise if this is the correct approach or overkill or if there is another more efficient method. I am a new newbie when it comes to android programming.

Once again, thank you for your help.


Theoretically yes. If someone knew the server that RDC was running on (a simple port scan would reveal port 17178 open) . Being that the auth is done on the server side (stored in config.properties) when RDC runs. I honestly hadn't thought about that. Id be very interested to see how you would pass the credentials from the client. That would definitely be more secure.
 

svk123

Member
Licensed User
Longtime User
I plan to pass the user name and pswd with every call (HttpUtils.PostString("Job1", ServerUrl, myString)) and return a valid result only after getting the user authenticated in asp.net. I can probably store the user name and password in a .txt on the phone or in a process variable when the user logs in initially. Hope this approach is secure!
 

Gearcam

Active Member
Licensed User
Longtime User
Can this method be used to connect to a ACCESS 2007 database stored on a local network server.

In the ASPX I guess I would need to change the
using (SqlConnection cn = newSqlConnection ......... to something pointing to the ACCESS database

Steve
 

Gearcam

Active Member
Licensed User
Longtime User
Erel

Plan B I have moved the data to a MS SQL database what's the best method to extract & update data connecting to this type of database on local network ?

Steve
 

Gearcam

Active Member
Licensed User
Longtime User
Thanks Erel I will go this way

I have downloaded the RDC on the windows server unpacked it
Also downloaded the JDBC driver from here as you suggest for the MS SQL server I have (Ver 1.3.1)

Tried to run the RunRLC.bat but get an error the system can not find the specified path.
(I have it all on my desktop in a folder)

What have I missed ?

Steve

This is the error !!

B4X:
C:\Users\Administrator.SERVER-2\Desktop\RemoteDatabaseConnector>"C:\Program F
iles (x86)\Java\jdk1.7.0\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheres
oftware.b4a.remotedatabase.RemoteServer
The system cannot find the path specified.

C:\Users\Administrator.SERVER-2\Desktop\RemoteDatabaseConnector>pause
Press any key to continue . . .

I do not have this directory
C:\Program Files (x86)\Java\jdk1.7.0
 
Last edited:

edgsistemi

Member
Licensed User
Longtime User
Hi,

I have a problem using the tutorial Android Connect to MS SQL Server Tutorial;

If I compile in release works, however, if I compile in release obfuscator does not work.

Someone had the same problem?

Thank You
 

edgsistemi

Member
Licensed User
Longtime User
Hi,

executing this code:

Generale.ServerURL = "http://xxxx..../edgcassa.aspx"
HttpUtils.CallbackActivity = "Principale"
HttpUtils.CallbackJobDoneSub = "SQLJobDone"
'
HttpUtils.PostString("Job1", Generale.ServerURL, "SELECT * FROM [Sisco Packing List PV].[dbo].[App Android]")
.
.
.
Sub SQLJobDone (Job AsString)
Dim m AsMap
Dim rows AsList
Dim parser AsJSONParser
Dim response AsString
Dim controllo AsBoolean
If HttpUtils.IsSuccess(Generale.ServerURL) Then
...
Msgbox("HttpUtils.IsSuccess","PROVA")
Else
...
Msgbox("HttpUtils.Is Not Success","PROVA")
End If

in debug and release mode works. in obfuscated mode does not work and does not display the msgbox

Thanks
 
Status
Not open for further replies.
Top