Android Tutorial Connect Android to MS SQL Server Tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 11, 2011.

Thread Status:
Not open for further replies.
  1. askez

    askez Member Licensed User

    this is what i get


    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.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Add: Log(Response).

    It will print the server response to the logs.
     
  3. Gearcam

    Gearcam Active Member Licensed User

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

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

    askez Member Licensed User

    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":"askez@zahav.net.il"}]
    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.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

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

    askez Member Licensed User

    solved!!
    thanX!!!
     
  8. Gearcam

    Gearcam Active Member Licensed User

    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
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    The query string doesn't look correct to me.

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

    Gearcam Active Member Licensed User

    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
     
    Last edited: Jul 9, 2012
  11. Gearcam

    Gearcam Active Member Licensed User

    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
     
    Last edited: Jul 9, 2012
  12. Erel

    Erel Administrator Staff Member Licensed User

    You should change your code to:
    Code:
    job2.PostString(ServerUrl, "Insert INTO test ([test1],[test2]) VALUES ('" & dataA & "' ,'" & dataB & "2')")
     
  13. Gearcam

    Gearcam Active Member Licensed User

    Solved thanks Erel i had just about got theire

    Great support as always

    :sign0098:
     
  14. jeronimovilar2

    jeronimovilar2 Member Licensed User

    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.
     
  15. jeronimovilar2

    jeronimovilar2 Member Licensed User

    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
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  17. jeronimovilar2

    jeronimovilar2 Member Licensed User

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

    Erel Administrator Staff Member Licensed User

    You should check the logs and find the error message.
     
  19. jeronimovilar2

    jeronimovilar2 Member Licensed User

    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
    %>
     
  20. vb1992

    vb1992 Well-Known Member Licensed User

    Maybe change this line:

    Set rs = dbc.Execute(sql)


    to


    dbc.Execute(sql)
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice