B4J Question MSSQL Server with B4J

[Pedro Caldeira]

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]

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]

Pedro,

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

 

[Pedro Caldeira]

the jar does not appear in the ide
can you post your initialization call.

mine is like this

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]

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

#AdditionalJar: sqljdbc42.jar

(only one, not both of them)

 

[Pedro Caldeira]

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

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

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

 

[Pedro Caldeira]

the instance is POS2GOSQL
the server name is : POS2GOSRV

 

[Pedro Caldeira]

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]

Being that:

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]

Thanks Henrique,
Going to give it a shot

 

[imbault]

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 :

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]

thanks guys.

 

[Pedro Caldeira]

nothing works
I have even installed MSSQL EXPRESS in the same machine as B4J

MSSQL.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://TOSHIBA-PMSMC\SQLEXPRESS:1433;integratedSecurity=true;user=pmsmc;databaseName=PRITMS;")

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]

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]

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]

Going from a project of mine, the connection string would be something like this (based on jtds):

MSSQL.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://TOSHIBA-PMSMC;integratedSecurity=true;user=pmsmc;instance=SQLEXPRESS;database=PRIIMS")

 

[Pedro Caldeira]

thanks guys.

 

[Zoran Kovacevic]

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

 

[Erel]

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