Android Question Q about net.sourceforge.jtds.jdbc.Driver MSSQL

[padvou]

Hello,
I would like to ask why the query operations with this driver cause NetworkOnMainThread exceptions.
I have already tried calling subs from the Main activity using the Threading library and the CSE (CallSubExtended) library and they both cause the same issue.
I have ready the article about disabling Strict mode, but it doesnt serve my purposes.
I show in pseudo code the structure of my code, so that hopefully someone can help me identify what I'm doing wrong:

Main activity start
irrelevant code...

Main activity resume
irrelevant code..'

Button click:
code checks if there is network connectivity
if yes, it async calls a sub that runs the SQL stuff, let's call it "Sub SQLroutine"

Sub SQLroutine:
Runs on main thread a sub that updates a label saying that procedure started
async calls a sub (firstSub) that runs the first SQL stuff

firstSub:
checks if mssql is initialized otherwise initializes it
waits for mssql_ready
runs a query using waitfor so that it returns the results
puts the results of the JdbcResultSet in a list
reads the content of the list one by one and inserts them one by one in sqlite table included in the application
while inserting in runs on main thread a sub that updates a label showing the progress of records inserting.

 

[aeric]

I think you better post the actual code of a small project.

 

[padvou]

ok, here goes:

#AdditionalJar: jtds-1.3.1.jar
Sub Process_Globals
Dim CSE As CallSubExtended
Dim mssql As JdbcSQL
Dim driver As String = "net.sourceforge.jtds.jdbc.Driver"
Dim jdbcUrl As String = "jdbc:jtds:sqlserver://" & SQLServerSTR & "/" & SQLdatabase
End Sub
Sub Button1_Click
CSE.AsyncCallSubx(Me, "Update_SQLiN", Null, 5)
End Sub
Sub Update_SQLiN
CSE.RunOnMainThread("Add_txt",Array As String("Update_SQLiN"))
CSE.AsyncCallSubx(Me, "Get_Customers", Null, 5)
End Sub
Sub Add_txt(txt)
    Label1.Visible=True
    Label1.Text=Label1.Text & CRLF & txt
    Label1.Invalidate
End Sub
Sub Get_Customers
    Dim L3C As List
    L3C.Initialize   
    If mssql.IsInitialized=False Then
        Connect_A
        Wait For mssql_Ready (Success As Boolean)
    End If
        wait for(ReturnRS("select columns from table")) complete (EmpJRS1 As JdbcResultSet)
        Rs2L(EmpJRS1,L3C,False,False)
        Log("Customers_finished")       
End Sub

Sub Connect_A   
    If L.IsInitialized=False Then L.Initialize
    If mssql.IsInitialized=False Then mssql.InitializeAsync("mssql", driver, jdbcUrl, SQLusername, SQLpassword)   
    Log("Connect_A")
End Sub

Sub mssql_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Sub Rs2L(RSJ As JdbcResultSet, JL As List, Simple As Boolean, GetColumns As Boolean)
    Log("Rs2L")
    Try
    If JL.IsInitialized=False Then JL.Initialize
        JL.Clear
    Catch
        Log("Rs2La")
        'CSE.RunOnMainThread("error_do2",Array As String("1a " & LastException.Message))
    End Try
    Try
    If GetColumns=True Then
        Dim Item1 As List
        If Item1.IsInitialized=False Then Item1.Initialize
        Item1.Clear
        For i=0 To RSJ.ColumnCount-1
            Item1.add(RSJ.GetColumnName(i))
        Next
        JL.Add(Item1)       
        End If
    Catch
        Log("Rs2Lb")
        'CSE.RunOnMainThread("error_do2",Array As String("1b " & LastException.Message))
    End Try
    If Simple=True Then
        Try
            Do While RSJ.NextRow
                For f=0 To RSJ.ColumnCount-1
                    JL.Add(RSJ.GetString2(f))
                    Log(RSJ.GetString2(f))
                Next
                Loop
        Catch
            Log("Rs2Lc")
            'CSE.RunOnMainThread("error_do2",Array As String("1c " & LastException.Message))
        End Try
    Else
        Do While RSJ.NextRow
            Try
                Dim Item1 As List
                If Item1.IsInitialized=False Then Item1.Initialize
                Item1.Clear
            Catch
                Log("Rs2Ld")
                'CSE.RunOnMainThread("error_do2",Array As String("1d " & LastException.Message))
            End Try
            Try
                For i=0 To RSJ.ColumnCount-1
                    Item1.add(RSJ.GetString2(i))
                Next
            Catch
                Log("Rs2Le")
                'CSE.RunOnMainThread("error_do2",Array As String("1e " & LastException.Message))
            End Try
            Try
                JL.Add(Item1)   
            Catch
                Log("Rs2Lf")
                'CSE.RunOnMainThread("error_do2",Array As String("1f " & LastException.Message))
            End Try
        Loop
    End If
    RSJ.Close
    Log("Rs2L-finish")
End Sub

 

[aeric]

I am not familiar with CSE. What does it do actually? Why not just use the SQL object?

 

[padvou]

I am not familiar with CSE. What does it do actually? Why not just use the SQL object?

It's a library that runs the sub declared in a separate thread.
Do the SQL stuff run in separate thread already?

 

[aeric]

I would not let android acts as my database server. I would use jRDC2 or jServer. The server will run multithreading.

 

[padvou]

Android doesnt act as a db server. The application connects to a MSSQL server and runs a query to pick up records.

 

[aeric]

Is it a mandatory to run different queries in different threads? I just follow the methods in SQL tutorial,

 

[padvou]

From further debugging it seems that the RS2L sub is causing the NetworkOnMainThread exception. How would I go about solving this?

 

[aeric]

I think RS2L sub can be written in more simpler form.

 

[padvou]

Can you please elaborate?

 

[aeric]

Rs2L(EmpJRS1,L3C,False,False)

The above line seems doesn't return anything outside the sub.

Since Simple = False and GetColumns = False, then I think the sub can be simplified as below:

Sub Rs2L (RSJ As JdbcResultSet, JL As List)
    If Not(JL.IsInitialized) Then
        JL.Initialize
    End If
    Do While RSJ.NextRow
        For i = 0 To RSJ.ColumnCount - 1
            JL.Add(RSJ.GetString2(i))
            Log(RSJ.GetString2(i))
        Next
    Loop
    RSJ.Close
End Sub

If you want to use the List outside the sub then return it as List.