B4J Question MSSQL Server with B4J

Pedro Caldeira

Active Member
Licensed User
Longtime User
Hello All,
I have a old VB6 application that connects to a local MSSQL Server.
I wanted to try to change to B4J, but i don't find any reliable way to work with MSSQL and B4J.
To my knowledge the jSQL work only with MySQL, correct ?
Is there any way to work with MSSQL (without those HTTP Calls and similar stuff :) )

Regards
 

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
Hi!
I my self have to do it yet have not the time to begin testing, but JDBC is standard connection so it should not be that difficult.

You should start here:
https://github.com/Microsoft/mssql-jdbc

Download the driver, add it to your additional libraries folder, put it in the ide and make the correspondent initialization call with JSQL
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
Pedro,

I can confirm that I could connect to a Win2008+SQL2008 combo and read out some data.
 
Upvote 0

Pedro Caldeira

Active Member
Licensed User
Longtime User
the jar does not appear in the ide
can you post your initialization call.

mine is like this
B4X:
MSSQL.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:SQLSERVER://SERVER/DATABASE;user=user;password=pass")

the libs i got from microsoft are
sqljdbc41.jar
sqljdbc42.jar
and i have place them in the additional lbs folder
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
jtds is an open source driver, and if you are using the microsoft drivers your connection string should be like this:

jdbc:sqlserver://;servername=server_name;integratedSecurity=false;authenticationScheme=JavaKerberos
more info: https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url

to add them to your IDE, you must add this on the Project Attributes region

B4X:
#AdditionalJar: sqljdbc42.jar
(only one, not both of them)
 
Upvote 0

Pedro Caldeira

Active Member
Licensed User
Longtime User
B4X:
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: sqljdbc42.jar
#End Region

MSSQL.Initialize("sqljdbc42","jdbc:POS2GOSQL://POS2GOSRV:1433;user=sa;password=somepass;databaseName=PRITMS;")

nothing :(
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
almost there. try

the first part is not the the name of the jar, but the driversClass, then change
POS2GOSQL to jdbc:sqlserver

which is the name of the instance?
POS2GOSQL or POS2GOSRV
(either is after the sqlserver://)

B4X:
MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://POS2GOSRV:1433;user=sa;password=somepass;databaseName=PRITMS;")
 
Upvote 0

Pedro Caldeira

Active Member
Licensed User
Longtime User
Just another question.
instead of specifying a machine name can I use a ddns to access the SQL remotely?
(from a guy that can't even connect locally)
:p
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
Being that:

B4X:
MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://POS2GOSRV\POS2GOSQL:1433;integratedSecurity=false;user=sa;password=somepass;databaseName=PRITMS;")

Yes, instead of using POS2GOSQL you will use your no-ip or ddns, you may need to configure your server for that.
 
Upvote 0

imbault

Well-Known Member
Licensed User
Longtime User
Correct, refer to JDBC documentation and drivers, using MS or sourceforge ones, MS are doing well for Sql Server

Below to access an MS Azure SQL Server :

B4X:
MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://xxxyyy.database.windows.net:1433;database=xxxx;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30")
 
Upvote 0

Pedro Caldeira

Active Member
Licensed User
Longtime User
nothing works :(
I have even installed MSSQL EXPRESS in the same machine as B4J
B4X:
MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://TOSHIBA-PMSMC\SQLEXPRESS:1433;integratedSecurity=true;user=pmsmc;databaseName=PRITMS;")

B4X:
Sub OpenSQL
    Try
      MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://TOSHIBA-PMSMC\SQLEXPRESS;integratedSecurity=true;user=pmsmc;databaseName=PRITMS;")
    Catch
        Log("Error connecting to SQL")
    End Try
   
End Sub
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
for the integrated security feature you need to add some other stuff as the page of microsoft says, it may be better (for the pourpuse of the testing) to keep it easier with integratedsecurity = false and using a user and a password.

Please post your uncatched error to see what may be the problem.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
Upvote 0

Lahksman

Active Member
Licensed User
Longtime User
Going from a project of mine, the connection string would be something like this (based on jtds):
B4X:
MSSQL.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://TOSHIBA-PMSMC;integratedSecurity=true;user=pmsmc;instance=SQLEXPRESS;database=PRIIMS")
 
Upvote 0
Top