B4J Question MSSQL Server with B4J

Pedro Caldeira · Mar 24, 2017

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 · Mar 24, 2017

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

sorex · Mar 24, 2017

Pedro,

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

Pedro Caldeira · Mar 24, 2017

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

EnriqueGonzalez · Mar 24, 2017

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)

Pedro Caldeira · Mar 24, 2017

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

EnriqueGonzalez · Mar 24, 2017

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;")

Pedro Caldeira · Mar 24, 2017

the instance is POS2GOSQL
the server name is : POS2GOSRV

Pedro Caldeira · Mar 24, 2017

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)

EnriqueGonzalez · Mar 24, 2017

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.

Pedro Caldeira · Mar 24, 2017

Thanks Henrique,
Going to give it a shot

imbault · Mar 24, 2017

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")

Pedro Caldeira · Mar 24, 2017

thanks guys.

Pedro Caldeira · Mar 25, 2017

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

EnriqueGonzalez · Mar 26, 2017

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.

Erel · Mar 26, 2017

Pedro Caldeira said:
To my knowledge the jSQL work only with MySQL, correct ?

No. It works with any SQL database that provides a JDBC driver (all popular databases are supported).

You can use jtds driver to connect to SQL server: https://www.b4x.com/android/forum/threads/sql-tutorial.35185/page-3#post-383462

Once you get the connection working it is better to switch to ConnectionPool from jServer library.

Lahksman · Mar 26, 2017

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")

Pedro Caldeira · Mar 27, 2017

thanks guys.

Zoran Kovacevic · Dec 4, 2017

I try tu use sqljdbc41,jar bat do not know how to declare MSSQL ( I see sqljdbc41 i library manager )
pleas help

Erel · Dec 5, 2017

Please start a new thread for your question. You don't need to use any library other than jSQL and jtds driver.

B4J Question MSSQL Server with B4J

Active Member

Well-Known Member

Expert

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Well-Known Member

B4X founder

Active Member

Active Member

New Member

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency