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
Longtime User
Last edited:
Upvote 0

emexes

Expert
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
 
Upvote 0

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
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
but if i upload to the device i get thousends of error Mgs
It does not help if you just hide the full errors from us. Create a new thread for any issue you have, post the full error and best upload a small project which shows the issue.
 
Upvote 0

emexes

Expert
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
 
Upvote 0

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
 
Upvote 0

emexes

Expert
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 ;-)
 
Upvote 0

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)
 
Upvote 0

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.
 
Upvote 0
Top