B4J Question JDBC connect to Sql Server using B4J

Dave G

Active Member
Licensed User
Been working on this for several days. Trying to connect to a Sql Server database that I can connect to with VB.NET with no problem. The database is on same machine as B4J. I either get an error after a short time or the Sql. Initialize2 never returns to Ready.

Server is local (192.168.0.233), instance is SSE2017, database is m4storeDSS or StockrightMMS (both Sql Server dbs), usuall use Integrated Security but providing User/Password doesn't help (see 4th example).

Some example connect strings used. These do not return from sql_connectio.Initialize2.
"jdbc:sqlserver://192.168.0.233\SSE2017;database=StockRightMMS;Integrated Security=SSPI;"
"jdbc:sqlserver://localhost\SSE2017;database=M4storeDSS;Integrated Security=SSPI;"
"jdbc:sqlserver://192.168.0.233\SSE2017;database=M4storeDSS;user=SOSuser1;password=SOSpwd;"

I comment and uncomment lines to try different jdbcUrl:
JDBC code fragments:
    'Private jdbcUrl As String = "jdbc:sqlserver://localhost\SSE2017;database=M4storeDSS;user=SOSuser;password=SOSpwd;"
    'Private jdbcUrl As String = "jdbc:sqlserver://localhost\SSE2017;database=M4storeDSS;Integrated Security=SSPI;"
    'Private jdbcUrl As String = "jdbc:sqlserver://192.168.0.233\SSE2017;database=StockRightMMS;Integrated Security=SSPI;"
    'Private jdbcUrl As String = "jdbc:sqlserver://192.168.0.233\SSE2017;database=StockRightMMS;"
    'Private jdbcUrl As String = "jdbc:sqlserver://localhost;DatabaseName=M4storeDSS;instance=SSE2017;user=SOSuser1;password=SOSpwd;"
    Private jdbcUrl As String = "jdbc:sqlserver://192.168.0.233\SSE2017;database=M4storeDSS;user=SOSuser1;password=SOSpwd;"
End Sub
Sub connectToDB
    'jdbc_connection.InitializeAsync("sql_connection", driver, jdbcUrl, "ISS", "what")
    sql_connection.InitializeAsync("sql_connection", driver, jdbcUrl, "ISS", "what")
    Wait For sql_connection_Ready (Success As Boolean)
    If Success = False Then
        Dim exEx As String = LastException.message
        Log("Ready Exception: " & exEx)
    Else
        Log("Ready Success")
    End If
 

Jorge M A

Well-Known Member
Licensed User
Longtime User
You can see this tutorial:
Then download your prefer version:
Microsoft JDBC Driver 7.4 for SQL Server
Microsoft JDBC Driver Version 8.4 SQL Server

#Region Project Attributes:
    'MS SQL Official Driver
    #AdditionalJar: mssql-jdbc-7.4.0.jre8.jar
B4X:
    Private driverClass As String="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    Private connectionString As String="jdbc:sqlserver://{SERVER};Port={PORT};databaseName={DATABASE}"
   or Private connectionString As String="jdbc:sqlserver://{SERVER}/{INSTANCE};Port={PORT};databaseName={DATABASE}"
    Private connectionStringTrusted As String="jdbc:sqlserver://;servername={SERVER};Port={PORT};databaseName={DATABASE};integratedSecurity=true;"
Replace parameters properly.
Then connect with Initialize2
Connection:
Connection.Initialize2(driverClass, connectionString, UserName, UserPassword)
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
hey!
follow the instructions on this link:
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Private jdbcUrl As String = "jdbc:sqlserver://192.168.0.233\SSE2017;database=M4storeDSS;user=SOSuser1;password=SOSpwd;"

All of these are wrong, since database is not a valid property for the MSSQL JDBC driver. It is databaseName

'Private jdbcUrl As String = "jdbc:sqlserver://localhost;DatabaseName=M4storeDSS;instance=SSE2017;user=SOSuser1;password=SOSpwd;"

This one is wrong, since instance is not a valid property for the MSSQL JDBC driver. It does not exist. You need to use the [servername|serveraddress]\[instancename] approach that your previous incorrect examples used (and yes, the backslash is valid - I was wrong in the previous thread)

For proper URL building for MSSQL JDBC driver, please see https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15
 
Upvote 0

Dave G

Active Member
Licensed User
Thanks for the response. Sql Server was setup properly and used by VB.NET socket servers. The issue turned out to be which JDBC .jar it used.
Here's a summary of what I found:
Various JDBC jars:
'#AdditionalJar: mssql-jdbc-6.2.2.jre7 doesn't return
'#AdditionalJar: mssql-jdbc-6.2.2.jre8 doesn't return
'#AdditionalJar: mssql-jdbc-7.4.1.jre8
'#AdditionalJar: mssql-jdbc-7.4.1.jre11
'#AdditionalJar: mssql-jdbc-7.4.1.jre12 doesn't return
'#AdditionalJar: mssql-jdbc-8.4.1.jre8
#AdditionalJar: mssql-jdbc-8.4.1.jre11
'#AdditionalJar: mssql-jdbc-8.4.1.jre14 doesn't return
jre 8/11 worked, others didn't
 
Upvote 0

Dave G

Active Member
Licensed User
Here're are some of my results.

1) Using wrong jar leads to hours of useless diversion (see response to Enrique). They don't return Ready with Success = false. Not cool.
2) Using InitializeSync ignores parameters in JdbcUrl, which means there doesn't appear there is a way to set Integrated Security.
3) Using Initialize (no user/passward) doesn't see Integrated Security parameter in jdbcUrl and tries to login with blank user.
4) Using 'database' or 'databasename' in jdbcUrl are fine.
5) Using 'instance=' causes Jdbc to not return

The winning jdbcUrl is::
    Private jdbcUrl As String = "jdbc:sqlserver://192.168.0.233\SSE2017;Database=M4storeDSS;user=yourUser;password=yourPassword;"

So, Integrated Security doesn't seem to be supported. I use that feature because my mobile socket server runs on the server with the database, so I don't have to create Logins. 'jars' don't parse jdbcUrl parameters and return Ready with success = false if a parameter is invalid, just doesn't return at all!

Thanks to all that provided feedback. Now on to creating a database server for mobile devices like I had created with VB.NET.
 
Upvote 0

swamisantosh

Member
Licensed User
Longtime User
I;m

I'm unable to connect to mssql database , i tried all major combination

Dim sql1 As JdbcSQL
Private MSLocation As String = "192.168.43.191\ULTIMATE:1433;databaseName=global;user=sa;password=solution.123;"

sql1.InitializeAsync("Connect", "net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://${MSLocation}"$, "", "")
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
Don't use jtds
What is ultimate? Get rid of that part
Don't write the port if it is the default

Don't add parameters, better try with the initialize2 to add the user and password, later add them if needed

follow the instrucctions i linked above
 
Upvote 0

swamisantosh

Member
Licensed User
Longtime User
Don't use jtds
What is ultimate? Get rid of that part
Don't write the port if it is the default

Don't add parameters, better try with the initialize2 to add the user and password, later add them if needed

follow the instrucctions i linked above

Code works fine with B4J, it does not work with B4A
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…