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:

jeronimovilar2

Member
Licensed User
Longtime User
Update?

no :(

------------------------------------------------
new ASP (JASON_UTIL.ASP) code - SELECT is ok:
<%
Function QueryToJSON(dbc, sql)
Dim rs, jsa
Set dbc = Server.CreateObject("adodb.connection")
dbc.CursorLocation = 3
dbc.open "provider=SQLOLEDB;server=(local);Database=db","sa","pwd"
Set jsa = jsArray()
If mid(sql,1,6) = "update" then
dbc.Execute(sql)
else
Set rs = dbc.Execute(sql)
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
End If
dbc.close
Set QueryToJSON = jsa
End Function
%>
--------------------------
browser (ok):
http://127.0.0.1/base3.asp?query=update table set J3=1 Where Numero=1
the table is updated

---------------------------------
B4a (no error), but not updated:
SQL = "query=update wCadBateria set J3=1 Where Numero=1"
HttpUtils.PostString("UPDATE", ServerUrl, SQL)

If Job = "UPDATE" Then
UpDate = True
End If
---------------------------------

:sign0085:
 

jeronimovilar2

Member
Licensed User
Longtime User
UPDATE ok

sorry.

The error is in another string with blanks

" ...where n=1 and F = 'Fase 1' "

i´ll change the " " for "%20".

thanks
 

ThePuiu

Active Member
Licensed User
Longtime User
i try to use this code (from post #1), but after I added the two files (HttpUtils and HttpUtilsService) to my project and tried to compile it I received the following error:
Compiling code. 0.04
Compiling layouts code. 0.00
Generating R file. 0.22
Compiling generated Java code. Error
B4A line: 60
HttpUtilsService.PostBytes = Data
javac 1.6.0_27
src\natura2000\mmare\httputils.java:137: incompatible types
found : natura2000.mmare.data
required: byte[]
mostCurrent._httputilsservice._postbytes = mostCurrent._data;
^
1 error

What am I doing wrong?
 

ThePuiu

Active Member
Licensed User
Longtime User
I had an activity called Data ... but after I renamed it everything is ok!
Seem to be mutually each other...
 

begale

Member
Licensed User
Longtime User
Connection MS SQL

It is possible to make a connection from Android device to a Microsoft SQL server without using ASP, but with a library ?

Thank you
 

yamohsen

New Member
Database Error:

Hey Guys, I just copied the asp.net code and pasted in a file with my Database credentials. However I get the following error:

:sign0085:

Error occurred. Query=select * from table_1
System.Data.SqlClient.SqlException: Login failed for user 'uact'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at ASP.test_aspx.Page_Load(Object sender, EventArgs ec)

-------------------------------------------------------------------------

Any idea what can I do about it ??? :sign0104:
 

ashiqjinnah

New Member
Licensed User
Longtime User
NWs

I am an absolute beginner of android devolopment now i need to connect sql server to basic4android last day i wl try with the code as shown above but it have some errors and also i try to connect sql server using mssql library mssql library got from this website with mssql library some post are there they ask try the code following below for connecting sql server

basic4android code :

Dim a As MSSQL
Dim L As List
a.setDatabase("server ipnumber (not name)","databasename","username","password")
L=a.Query("select * from tablename where keyname='xx'")

but the progrm pause on the line begin with L=a.Query.....

friends any one can other idea to connect sql server pls post

or

Attach a sample webservise file
pls help friends.....
 

kamalkishor

Member
Licensed User
Longtime User
Problem While creating login Page

Hi,


i have used the following code

'*********************************************************

Sub BtnLogin_Click
Dim tempEmailstring As String
Dim ExecQuery As String
Dim sqlQuery As String
Dim Md As MessageDigest
Dim ByteCon As ByteConverter
Dim PassWordHash() As Byte
Dim IntLName As Int
Dim IntPasswordConfirmation As String
Dim tempstringofemail As String
Dim tempstring0fpass As String
Dim Strngemail As String
Dim stringpassword As String
Dim sqlstring As String
Dim qurystring As String
Dim loopstring As String
Dim cursorcheck As Cursor

tempEmailstring = EdtTxtEmailAdress.Text

If EdtTxtEmailAdress.Text = "" Then
Msgbox("Please Enter Your Email Id","Message")
Return
End If

If EdtTxtEmailAdress.Text <> "" Then
Dim matcher1 As Matcher
matcher1 = Regex.Matcher("\w+@\w+\.\w+", EdtTxtEmailAdress.Text)
If matcher1.Find = False Then
Msgbox("invalid format","email")
Return
End If
End If

Dim temppwdstring As String
temppwdstring = EdtTxtPassword.Text

If temppwdstring = "" Then
Msgbox("Please Enter Your Password","Message")
Return
End If

IntLName = temppwdstring.Length()

PassWordHash = Md.GetMessageDigest(temppwdstring.GetBytes("UTF8"),"MD5")
Dim md5string As String
md5string = ByteCon.HexFromBytes(PassWordHash)

ExecuteRemoteQuery("Select customers_email_address,customers_password FROM cust where customers_email_address ='" & tempEmailstring & "' and customers_password ='" & md5string &"' ",taskiddd)

End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
Dim req As HttpRequest
req.InitializePost2("http://www.siliconsoftwares.in/store/admin/tt.php?user=silicons_store&pass=modem123pentium&db_name=silicons_store", Query.GetBytes("UTF8"))
HttpCl.Execute(req, TaskId)


End Sub
Sub HttpCl_ResponseSuccess (Response As HttpResponse, TaskId As Int)
Dim res As String
res = Response.GetString("UTF8")
Dim parser As JSONParser
parser.Initialize(res)
Dim l As List
l = parser.NextArray
If l.Size > 0 Then

Msgbox("login successfully","Message")
End If
Else
Msgbox("Invalid username or password","Message")

Response.Release
End Sub
Sub HttpCl_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)


If Response <> Null Then
Response.Release
Else
Msgbox("invalid password or Email","Message")
End If

End Sub

'*******************************************************
i want to add check on login page .whether the user registered or not .so let me know how it possible by using webservices and mysql database on server. any help or sugestion is appriciated..:sign0085:
 

qawi

New Member
Please help my error

Hi,

I stuck here to make my project.... please help me to solve my error :BangHead:

Error occurred. Query=
System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at ASP.test1_aspx.Page_Load(Object sender, EventArgs ec)

please help me :sign0085:
i not good in english :sign0089:
 

jack007

New Member
Licensed User
Longtime User
Well done

After appropriate modifications, the example code can run very well.
Look forward to more and better.
 

jalle007

Active Member
Licensed User
Longtime User
I have web service up and running on MSSQL database.
Tested in web browser and its working
http://localhost:61343/Default.aspx

Problem is with Android app since it always returns

HttpUtils.IsSuccess(ServerUrl) = false

and logcat contains this
Error. Url=http://localhost:61343/Default.aspx Message=org.apache.http.conn.HttpHostConnectException: Connection to http://localhost:61343 refused
What should I do ?
 

jalle007

Active Member
Licensed User
Longtime User
I tried both.
The device is on the same netowrk as localhost (PC)
I tried entering IP on my device and yes it opens my IIS homepage.

but whenever i try to call eg:
http://localhost:59459/ParseTable.aspx
from code , httputils response with NoSuccess (because it cant see localhost)

I would really like to test the app working with localhost rather then uploafing the page to remote server...
 
Status
Not open for further replies.
Top