Android Question MSSQL Query

DataProtec

Member
Licensed User
Hello
After days Reading ALL Threads of the Forum and checking the Internet finaly i can connect to a MSSQL DB :)
But it is imposible to make a query and get a Result back
my code

B4X:
#AdditionalJar: mysql-connector-java-5.1.34-bin.jar

Sub Process_Globals
    Public msSQL As JdbcSQL
    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.5.21:1433/DB_Datas"
    Private Username As String = "user"
    Private Password As String = "1234"
end sub


Sub cmdConnect_Click

msSQL.InitializeAsync("msSQL", driver, jdbcUrl, Username, Password)
Wait For msSQL_Ready (Success As Boolean)
    If Success = True Then
        Log("Connected")         '***Get the message is connected
    End If
    
    Log(msSQL.IsInitialized)   '****get the message is Initialized
    
        '***** here i do NOT get any Error
    Dim sf As Object = msSQL.ExecQuery("SELECT UserID, Name FROM UserInfo_Table WHERE UserID = '5'")
    
    '***** But here it stucks and NO way to make it running, no Error Msg
    Wait For (sf) msSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
    If Success Then
        Do While Crsr.NextRow
            Log($"Name: ${Crsr.GetInt("Name")}"$)
        Loop
        Crsr.Close
    End If
end sub
Please can you provide the proper code, is NOT to make my job, i usesd all i could do
Please give me a help, searching the Forum or the Internet is done more then a few times.
With C, C#, VB, Delphi no problem, but V4A not working and i have no idea what i can do
thanks
Uli - Dataprotec
 

DonManfred

Expert
Licensed User
Last edited:

emexes

Well-Known Member
Licensed User
I am no SQL expert, but what I did spot is that in this tutorial here:

https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/

the sample query code is using ExecQueryAsync whereas your code uses ExecQuery, which presumably does not generate a QueryComplete event:
B4X:
Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", "SELECT * FROM table1", Null)
Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
 

DataProtec

Member
Licensed User
I did what was recommended but won't work

B4X:
I added #AdditionalJar: jtds-1.3.1.jar but if i upload to the device i get thousends of error Mgs, but the file is in the Libary Folder

'*** This have no error
Dim sf As Object = msSQL.ExecQueryAsync("msSQL", "SELECT UserID, Name FROM UserInfo_Table WHERE UserID = '5'", Null)

'*** but this is red underlined ad say "Unknown Type" why
Wait For (sf) msSQL_ExecQueryAsync(Success As Boolean, rs As ResultSet)

'*** I have to use instead only msSQL_QueryComplete and not msSQL.ExecQueryAsync
Wait For (sf) msSQL_QueryCompleteAsync (Success As Boolean, Crsr As JdbcResultSet)
If Success Then
        Do While Crsr.NextRow
              Log($"Name: ${Crsr.GetInt("Name")}"$)  '*** but don't get nothing
      Loop
Crsr.Close

End If
 

emexes

Well-Known Member
Licensed User
I did what was recommended but won't work
Is this the actual code you ran?
B4X:
'*** I have to use instead only msSQL_QueryComplete and not msSQL.ExecQueryAsync
Wait For (sf) msSQL_QueryCompleteAsync (Success As Boolean, Crsr As JdbcResultSet)
If Success Then
What's with the QueryCompleteAsync?

It's like you took a good idea and ran with it :) ... too far
 

DataProtec

Member
Licensed User
i don't hide any errors, the one with #AdditionalJar: jtds-1.3.1.jar is gone, i restarted the B4A now have without errors
What i'm doing wrong ????
Why this want give me a Result, where is the mistake

i changed
B4X:
'****here the full code again
#AdditionalJar: jtds-1.3.1.jar

Sub Process_Globals
    Public msSQL As JdbcSQL
    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.5.215:1433/DB_Garbaman"
    Private Username As String = "sa"
    Private Password As String = "@Xmallahi"
End Sub

    '*** Works without any Error, but no Result ***
Sub cmdConnect_Click
    msSQL.Close
    msSQL.InitializeAsync("msSQL", driver, jdbcUrl, Username, Password)
    
        Dim sf As Object = msSQL.ExecQueryAsync("msSQL", "SELECT UserID, Name FROM UserInfo_Table WHERE UserID = '5'", Null)
        Wait For (sf) msSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
        
    If Success Then
        Do While Crsr.NextRow
            Log($"Name: ${Crsr.GetInt("Name")}"$)
        Loop
        Crsr.Close
    End If
End sub
 

emexes

Well-Known Member
Licensed User
i changed
B4X:
'*** Works without any Error, but no Result ***
Sub cmdConnect_Click
    msSQL.Close
    msSQL.InitializeAsync("msSQL", driver, jdbcUrl, Username, Password)
   
        Dim sf As Object = msSQL.ExecQueryAsync("msSQL", "SELECT UserID, Name FROM UserInfo_Table WHERE UserID = '5'", Null)
        Wait For (sf) msSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
What happened to waiting/checking the msSQL_Ready "return" status from the msSQL.InitializeAsync call??? eg from post #1
B4X:
msSQL.InitializeAsync("msSQL", driver, jdbcUrl, Username, Password)
Wait For msSQL_Ready (Success As Boolean)
    If Success = True Then
        Log("Connected")         '***Get the message is connected
    End If
I think this might be another case of one-step-forward-one-step-back. Then again, we've all done stuff like this when under pressure ;-)
 

OliverA

Expert
Licensed User
What i'm doing wrong ????
If you get no errors, then
B4X:
Dim sf As Object = msSQL.ExecQueryAsync("msSQL", "SELECT UserID, Name FROM UserInfo_Table WHERE UserID = '5'", Null)
Wait For (sf) msSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
Just happens to return with an empty result set! To verify, change
B4X:
    If Success Then
        Do While Crsr.NextRow
            Log($"Name: ${Crsr.GetInt("Name")}"$)
        Loop
        Crsr.Close
    End If
to
B4X:
    If Success Then
        Dim count as int = 0
        Do While Crsr.NextRow
            Log($"Name: ${Crsr.GetInt("Name")}"$)
            count = count + 1
        Loop
        If count = 0 Then Log("Nothing was returned!!!!")
        Crsr.Close
    End If
Update: I finger fudged something and posted this before completing my code!!! Make sure you see this update, and the code should be all there (untested, but pretty sure it works)
 

OliverA

Expert
Licensed User
Oh yeah, you also should read my mini-rant on this whole B4a and MSSQL subject: https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/#post-659321. It's really prevalent to this situation (since it's pretty much the same situation that evoked the rant to begin with).
With C, C#, VB, Delphi no problem, but V4A not working and i have no idea what i can do
thanks
You cannot compare Android to a Desktop environment and the tools that are available on a Desktop environment, so the comparison is bunk. Just saying.
 
Top