B4J Question JDBC connect to Sql Server using B4J

[Dave G]

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:

    '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]

You can see this tutorial:

💡 JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases

Hello fellow B4X'ers, Here is some example code for connecting your B4J projects to 5 different types of database engines, this tutorial is for learning purposes only and nothing else. Disclaimer: This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do...

www.b4x.com

Then download your prefer version:
Microsoft JDBC Driver 7.4 for SQL Server

Download Microsoft JDBC Driver 7.4 for SQL Server from Official Microsoft Download Center

Download the Microsoft JDBC Driver 7.4 for SQL Server, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Editions.

www.microsoft.com

Microsoft JDBC Driver Version 8.4 SQL Server

Download - JDBC Driver for SQL Server

Download the Microsoft JDBC Driver for SQL Server to develop Java applications that connect to SQL Server and Azure SQL Database.

docs.microsoft.com

    'MS SQL Official Driver
    #AdditionalJar: mssql-jdbc-7.4.0.jre8.jar

    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.Initialize2(driverClass, connectionString, UserName, UserPassword)

 

[EnriqueGonzalez]

hey!
follow the instructions on this link:

config.properties - MS Sql driver for JDBC

I am trying to change the tutorial code for mysql to MS Sql, but I have it wrong. This is my code: DriverClass=com.Drivermicrosoft.sqlserver.jdbc.SQLServerDriver JdbcUrl=jdbc:sqlserver://localhost/test?characterEncoding=utf8 I could not find the correct connection string elsewhere on the...

www.b4x.com

 

[OliverA]

'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://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

 

[Dave G]

hey!
follow the instructions on this link:

config.properties - MS Sql driver for JDBC

I am trying to change the tutorial code for mysql to MS Sql, but I have it wrong. This is my code: DriverClass=com.Drivermicrosoft.sqlserver.jdbc.SQLServerDriver JdbcUrl=jdbc:sqlserver://localhost/test?characterEncoding=utf8 I could not find the correct connection string elsewhere on the...

www.b4x.com

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:

'#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

 

[Dave G]

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

    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.

 

[swamisantosh]

I;m

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

    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.

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}"$, "", "")

 

[EnriqueGonzalez]

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

 

[swamisantosh]

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

 

[Jorge M A]

Tutorial of connecting Android to MS SQL Server using jRDC2

Hi Here is a tutorial that shows the steps of how to make an Android connection to MS SQL Server using jRDC2. Download tutorial here: Attached the Mobile Project in B4A and the JRDC2 Project in B4J Any questions please ask in the forum Bladimir Silva Toro Teacher programming software

www.b4x.com