Android Tutorial Connect Android to MS SQL Server Tutorial

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:

<%@ 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).

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).

http://www.example.com/test1.aspx?query=select * from table_1

Now for Basic4android program:

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:

** 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 **

 

[askez]

this is what i get

Check the logs and see the response you get from the server.

LogCat connected to: emulator-5556
** Activity (main) Create, isFirst = true **


Starting Job: Job1


** Activity (main) Resume **
** Service (httputilsservice) Create **


** Service (httputilsservice) Start **
main_jobdone (B4A line: 34)


rows = parser.NextArray



java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at camera.a.main._jobdone(main.java:339)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:105)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:89)
at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:772)
at anywheresoftware.b4a.keywords.Common.CallSub2(Common.java:759)
at camera.a.httputilsservice._processnexttask(httputilsservice.java:244)
at camera.a.httputilsservice._response_streamfinish(httputilsservice.java:345)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:105)
at anywheresoftware.b4a.BA$1.run(BA.java:210)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:3683)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

 

[Erel]

Add: Log(Response).

It will print the server response to the logs.

 

[Gearcam]

dual activity modules

Erel
I now have the conection running fine to my database
Also the B4A app is running ok for the Main Module

But i also need a second Module with the ability to connect to the same database but different table which works fine and i can get the data i want.

Exept when the job is finished getting the data
HttpUtils.Complete = True Then JobDone(HttpUtils.Job) trys to always run the JobDone sub in the main module not the one in my second module

If i let the device go into standby then bring it back with powerbutton it works because it runs the Activity_Resume in my new module

Have i done this in the correct way for 2 modules as the section in 'work with result is completley different for both pieces of code and in two diferent modules ?

Steve

 

[Erel]

You may want to use HttpUtils2 instead of HttpUtils. It will be simpler to work with it from different modules.

 

[askez]

did it

i've changed the example a little and i'm bringing custid, mail from my DB but as u can see it comes like it should be in an array.

thnax!!

** Activity (main) Create, isFirst = true **


Starting Job: Job1


** Activity (main) Resume **


** Service (httputilsservice) Create **


** Service (httputilsservice) Start **


<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; CHARSET=UTF-8">



<meta http-equiv="Content-Language" content="he">

[{"CustId":1,"email":"[email protected]"}]
main_jobdone (B4A line: 35)


rows = parser.NextArray



java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at camera.a.main._jobdone(main.java:342)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:105)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:89)
at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:772)
at anywheresoftware.b4a.keywords.Common.CallSub2(Common.java:759)
at camera.a.httputilsservice._processnexttask(httputilsservice.java:244)
at camera.a.httputilsservice._response_streamfinish(httputilsservice.java:345)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:105)
at anywheresoftware.b4a.BA$1.run(BA.java:210)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:3683)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

 

[Erel]

Make sure that the lines above the JSON string are not part of the response.

 

[askez]

Make sure that the lines above the JSON string are not part of the response.

solved!!
thanX!!!

 

[Gearcam]

help with "Insert into"

i have been trying to post data back to my sql database with

job2.PostString(ServerUrl, "Insert INTO test [{[test1],[test2], & VALUES & "1" & "2" }])

But i can not get it to work any help ?

Steve

 

[Erel]

The query string doesn't look correct to me.

Which error do you get in the logs? What is the server response?

 

[Gearcam]

it will not compile


Compiling code. Error
Error parsing program.
Error description: Length cannot be less than zero.
Parameter name: length
Occurred on line: 149
job2.PostString(ServerUrl, "Insert INTO test [{[test1],[test2], & VALUES & "1" & "2" }])



This compiles
job2.PostString(ServerUrl, "Insert INTO test [test1],[test2] VALUES 1, 2")

but this is the log error

Error occurred. Query=Insert INTO test [test1],[test2] VALUES 1, 2

 

[Gearcam]

just a little to keen

this works

job2.PostString(ServerUrl, "Insert INTO test ([test1],[test2]) VALUES (" & dataA & " ," & dataB & "2)")


dataA="1"
dataB="2"

all is ok

dataA="thisworks" or dataA="this works"
does not

from log
System.Data.SqlClient.SqlException: The name "thisworks" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


Any ideas ?

Steve

 

[Erel]

You should change your code to:

job2.PostString(ServerUrl, "Insert INTO test ([test1],[test2]) VALUES ('" & dataA & "' ,'" & dataB & "2')")

 

[Gearcam]

You should change your code to:

job2.PostString(ServerUrl, "Insert INTO test ([test1],[test2]) VALUES ('" & dataA & "' ,'" & dataB & "2')")

Solved thanks Erel i had just about got theire

Great support as always

:sign0098:

 

[jeronimovilar2]

not found the .ASPX (or ASP)

i change to test:
ServerUrl = "http://10.0.2.2/base3.asp"

and the "HttpUtils.IsSuccess(ServerUrl)" is false. why?

the "base3.asp" exists.

 

[jeronimovilar2]

ASP not found. why?

when i use the brower is ok: http://127.0.0.1/base3.asp?query=Select col1, col2 from table

but not ok using b4a. Why????

this my code:

'Activity module
Sub Process_Globals
Dim ServerUrl As String
ServerUrl = "http://10.0.2.2/base3.asp"
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, "?query=SELECT * FROM table")
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
' not here!!!!!
Dim parser As JSONParser
Dim response As String
response = HttpUtils.GetString(ServerUrl)
parser.Initialize(response)
Dim rows As List

rows = parser.NextArray

For i = 0 To rows.Size - 1
Msgbox(Job,"")
Log("Rows #" & i)
Dim m As Map
m = rows.Get(i)
Log("col1=" & m.Get("Col1"))
Log("col2=" & m.Get("Col2"))
Next
Else
' here!!!
Msgbox(ServerUrl,"not found")
End If
HttpUtils.Complete = False
End Sub

 

[Erel]

The error should be printed in the logs. The problem is probably with the spaces in the URL. You should encode the URL with StringUtils or better switch to HttpUtils2 and use Job.Download2 which automatically escapes the url.

 

[jeronimovilar2]

Nothing

Sorry erel, but i can´t to send my SQL string to .ASP

code .ASP
<!--#include file="JSON.asp"-->
<!--#include file="JSON_UTIL.asp"-->
<script language="vbscript" runat="server">
QueryToJSON("", request("query")).Flush
</script>

code B4a
HttpUtils.PostString("Job1", ServerUrl, "?query=select%20*%20from%20wcadcamisa")

what is the wrong? :sign0085:

sorry my english (from Brazil)

 

[Erel]

You should check the logs and find the error message.

 

[jeronimovilar2]

Update?

SELECT command is ok, but UPDATE not.

-----------------------------------------
code B4a:
SQL "query=update table set J3=1 Where N=1 and C=1 and F=5"
' this line in the SQL is correct (test.asp?query)'

HttpUtils.PostString("update", ServerUrl, SQL)
---------------------------------------------
log (error in JSON_UTIL.asp):
error:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/JSON_UTIL.asp, line 10
------------------------------------------

JSON_Util.asp code:
<%
Function QueryToJSON(dbc, sql)
Dim rs, jsa
Set dbc = Server.CreateObject("adodb.connection")
dbc.open "provider=SQLOLEDB;server=.;Database=base","sa","pwd"

Set jsa = jsArray()
Set rs = dbc.Execute(sql)

'line 10 While Not (rs.EOF Or rs.BOF)
Set jsa(Null) = jsObject()
For Each col In rs.Fields
jsa(Null)(col.Name) = col.Value
Next
rs.MoveNext
Wend

dbc.close
Set QueryToJSON = jsa
End Function
%>

 

[vb1992]

Maybe change this line:

Set rs = dbc.Execute(sql)


to


dbc.Execute(sql)