Android Question MSSQL-JDBC Minimalistic Example

Charles Biba · Mar 12, 2018

Ok... I am trying to get B4A to connect to an in-house MSSQL server (2014) using a direct JDBC connection. However, things are a bit confusing for an outsider new to B4A and I keep getting "unknown error (14) Can't open database" in the log. This server is running other in-house databases so accepts connections, etc. If I poison the initialize string, I get a massive error, so I am guessing I am on the right track (hopefully). Is there something blatantly wrong with my test? Also, all the examples I found do not have a Boolean parameter in the sql initialize... am I mixing up libraries here?

Thanks!

I have an additional jar in project attributes and have added a reference to SQL in the libraries manager.

#AdditionalJar: mssql-jdbc-6.2.2.jre7.jar

I declared an sql variable in process globals:

Private sql1 As SQL

In the designer I created a button and added a click event. In there i am trying to execute:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433/TEST_CB;user=android;password=andy",False)

Ultimately I would like to execute something like:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433;integratedSecurity=false;user=android;password=andy;databaseName=TEST_CB;", True)

Cursor = sql1.ExecQuery("SELECT item1, item2 FROM testtable")

For i = 0 To Cursor.RowCount - 1
Cursor.Position = i
Log(Cursor.GetString("item1"))
Log(Cursor.GetString("item2"))
'Next

DonManfred · Nov 14, 2019

Jaymer said:
2) I see the benefit of using jRDC2 - very nice, and esp. since its tightly integrated with b4A, that seems the way to go.

It is the suggested/recommended way.

MrKim · Nov 14, 2019

Jaymer said:
hi @MrKim
i need to connect to a local MSSQL db in our warehouse.
I am brand new to b4x.
Am I going to have problems?
Is this still working for you?
Can you send me any code or hints? I'm worried about replying on a 6 year old StackOverflow thread and planning my direction on old information about old drivers.
When "they" call the IT people - have you any idea about what the issue is that they seem to fix?

thx VERY much!

[/QUOTE]
Yes, it is still working using the code above. Everything everyone here says is true. The advantage of the way I did it is we have over 100 customers WHO HAVE THEIR OWN SERVERS AND MIS AND SECURITY, ETC. Trying to set up a new back end for them is untenable. They are already running our SQL server app on a Windows front end so running a tablet app the same way is a no-brainer for us. Install the app on the tablet, figure out the connection string and go.

MrKim · Nov 14, 2019

Jaymer said:
hi @MrKim
i need to connect to a local MSSQL db in our warehouse.
I am brand new to b4x.
Am I going to have problems?
Is this still working for you?
Can you send me any code or hints? I'm worried about replying on a 6 year old StackOverflow thread and planning my direction on old information about old drivers.
When "they" call the IT people - have you any idea about what the issue is that they seem to fix?

thx VERY much!

[/QUOTE]
It is still working fine. The advantage is you don't have to do anything to the server. With over 100 separate customers installing some kind of additional server is untenable. This way everything is in the app. No need to add stuff to the customers server.

See my post https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/#post-694500 for the code that worked for me.

javimac · Nov 14, 2019

Charles Biba said:
No hair left... lol ....but that's why we try to learn new things.

Still no connection, and in unfiltered logs I am getting:

java.lang.RuntimeException: Class not found: com.microsoft.sqlserver.jdbc.SQLServerDriver
Are you missing an #AdditionalJar attribute setting?

In Main I have the additional jar directive,

#AdditionalJar: mssql-jdbc-6.2.2.jre7

Charles Biba said:

No hair left... lol ....but that's why we try to learn new things.

Still no connection, and in unfiltered logs I am getting:

java.lang.RuntimeException: Class not found: com.microsoft.sqlserver.jdbc.SQLServerDriver
Are you missing an #AdditionalJar attribute setting?

In Main I have the additional jar directive,

#AdditionalJar: mssql-jdbc-6.2.2.jre7

and in my paths configuration I have C:\B4A-Projects\ExtraLibraries that contains

JdbcSQL.jar
JdbcSQL.xml
mssql-jdbc-6.2.2.jre7.jar

Click to expand...

and in my paths configuration I have C:\B4A-Projects\ExtraLibraries that contains

JdbcSQL.jar
JdbcSQL.xml
mssql-jdbc-6.2.2.jre7.jar

MrKim · Nov 15, 2019

You don't need/want the microsoft driver. It does not work. Here is the code from that link:

B4X:

#Region Project Attributes

    #AdditionalJar: jtds-1.3.1.jar

#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

    Dim sql1 As JdbcSQL', Str As StringUtils
End Sub

'I use Callback to tell me what sub to return to if the connection fails and I have to re-establish it.
Public Sub Connect(CallBack As String)

        sql1.Close
        sql1.InitializeAsync("Connect", "net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://192.168.1.5:65218;databaseName=MyDBName;user=UserName;password=MyPW;appname=SKMJL;wsid=MyWS;loginTimeout=10", "UserName", "MyPW")  'sql 2012


    Catch
        Msgbox2Async("Connection to the network failed: " & CRLF & LastException.Message , "Network Error", "OK","","", Null, True)
    End Try

End Sub

'I have left an example SQL Select statement in here, ExecQueryAsync
Sub Connect_Ready (Success As Boolean)
    ProgressDialogHide
    If Success = False Then
        MsgboxAsync("Failed connect to the server. Server is not available, database is not availble, connect string is wrong, or you are having network/firewall issues. Contact your system administrator.", "Network/SQL Error")
        MsgboxAsync(LastException.Message,"JAva Error:")
        Return
    End If
    Try
        Connecting = False
        ProgressDialogShow("Getting Configuration Data")
        GetCount 'Check user counts
        Dim SenderFilter As Object = sql1.ExecQueryAsync("Switches", "SELECT  SW17, SW34, SW37, SW39 FROM Switches;", Null)
        Wait For (SenderFilter) Switches_QueryComplete (Success2 As Boolean, Crsr As JdbcResultSet)
        If Success2 = False Then
            ProgressDialogHide
            MsgboxAsync("Failed to get the required Configuration (Switches) data - Cannot continue." & CRLF & LastException.Message, "DB Error")
            Screen = "Home"
            Return
        End If
    Catch
        Log(LastException.Message)
        ToastMessageShow("Connect_Ready" & CRLF & LastException.Message, True)
    End Try
 
End Sub

I can't upload the driver file, it is too large. Search the net for jtds-1.3.1.jar or I found it
HERE.

Put the file in: C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries
or wherever your libs are if it is different.

DonManfred · Nov 15, 2019

MrKim said:
Put the file in: C:\Program Files (x86)\Anywhere Software\Basic4android\Libraries
or wherever your libs are if it is different.

NEVER put additional jars into the internal library folder. They should be in the AdditionalLibs Folder.

MrKim · Nov 15, 2019

DonManfred said:
NEVER put additional jars into the internal library folder. They should be in the AdditionalLibs Folder.

OK, I don't HAVE an AdditionalLibs folder on my computer and it seems to be working fine where it is.

May I ask if there is a recommended location and why is it so important not to put them in the regular library folder?

MrKim · Nov 17, 2019

Charles Biba said:
Ok... I am trying to get B4A to connect to an in-house MSSQL server (2014) using a direct JDBC connection. However, things are a bit confusing for an outsider new to B4A and I keep getting "unknown error (14) Can't open database" in the log. This server is running other in-house databases so accepts connections, etc. If I poison the initialize string, I get a massive error, so I am guessing I am on the right track (hopefully). Is there something blatantly wrong with my test? Also, all the examples I found do not have a Boolean parameter in the sql initialize... am I mixing up libraries here?

Thanks!

I have an additional jar in project attributes and have added a reference to SQL in the libraries manager.

#AdditionalJar: mssql-jdbc-6.2.2.jre7.jar

I declared an sql variable in process globals:

Private sql1 As SQL

In the designer I created a button and added a click event. In there i am trying to execute:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433/TEST_CB;user=android;password=andy",False)

Ultimately I would like to execute something like:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433;integratedSecurity=false;user=android;password=andy;databaseName=TEST_CB;", True)

Cursor = sql1.ExecQuery("SELECT item1, item2 FROM testtable")

For i = 0 To Cursor.RowCount - 1
Cursor.Position = i
Log(Cursor.GetString("item1"))
Log(Cursor.GetString("item2"))
'Next

I wrote a turorial.
https://www.b4x.com/android/forum/t...rver-using-jtds-1-3-1-jar.111446/#post-694957

Android Question MSSQL-JDBC Minimalistic Example

Charles Biba

Member

DonManfred

Expert

MrKim

Well-Known Member

MrKim

Well-Known Member

javimac

Member

MrKim

Well-Known Member

DonManfred

Expert

MrKim

Well-Known Member

MrKim

Well-Known Member

Similar Threads

Android Question MSSQL-JDBC Minimalistic Example

Member

Expert

Well-Known Member

Well-Known Member

Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency