B4J Question (solved)how Connect to mssql with InstanceName ??

behnam_tr · Mar 6, 2025

hi
Is there a way to connect to the mssql database using the instance name with JdbcSQL ?
Now with this code my connection is ok but I need to connect to a specific instance.

B4X:

#AdditionalJar: mssql-jdbc-12.8.1.jre8
Private SQL1 As JdbcSQL
Dim DBLoaction As String = "localhost"
Dim InstanceName As String = "HAMTA"
Dim DBUser As String = "sa"
Dim DBPass As String = "12345"
Dim DBName As String = "test1"

SQL1.Initialize2("net.sourceforge.jtds.jdbc.Driver", _
    $"jdbc:jtds:sqlserver://${DBLoaction}:1335;databaseName=${DBName};"$, DBUser, DBPass)
Log("Connected : " & SQL1.IsInitialized)

behnam_tr · Mar 7, 2025

teddybear said:
The sql server‘s port you are using is 1335. but by default, the first instance of SQL Server Express, uses TCP port 1433 for data communication

If you want to connect to server without port, the server port should be 1433

So far, the following results have been tested and were successful.

With jtds Driver

Connect with tcp port:

 #AdditionalJar: jtds-1.3.2.jar
 Private MSSQL As SQL
Private DBLocation As String = "localhost"
Private InstanceName As String="SQLEXPRESS"
Private SqlPort As Int = 1335
Private DBName As String = "test1"
Private DBUsername As String = "sa"
Private DBPassword As String = "12345"
MSSQL.Initialize2("net.sourceforge.jtds.jdbc.Driver"...

PaulMeuris · Mar 6, 2025

https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16
jdbc:sqlserver://[serverName[\instanceName] [ : portNumber]][;property=value[;property=value]]
(remove the spaces around the colon and portNumber, it produces a smiley [ )

For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

behnam_tr · Mar 6, 2025

PaulMeuris said:
https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16
jdbc:sqlserver://[serverName[\instanceName] [ : portNumber]][;property=value[;property=value]]
(remove the spaces around the colon and portNumber, it produces a smiley [ )

Thank you for your attention
But my goal is to connect without using a port
Is this possible??
Because my app users install the Express version(sql server 2008) which is lighter but TCP is disabled by default and must be manually enabled and configured, I want the connection process to be done automatically and without any extra work.
If I want to set the port, I won't need the instance name anymore.

PaulMeuris · Mar 6, 2025

Did you try it without the port number like below?

$"jdbc:jtds:sqlserver://${DBLoaction}\${InstanceName};databaseName=${DBName};"$

aeric · Mar 6, 2025

Try:

JRDC2 connect to MSSQLLocalDb

# MS SQL Server Express Edition DriverClass=net.sourceforge.jtds.jdbc.Driver JdbcUrl=jdbc:jtds:sqlserver://./DatabaseName;instance=LOCALDB#AC2F9943;namedPipe=true;domain=DomainName You need to use jTDS 1.3.2 Reference...

www.b4x.com

behnam_tr · Mar 6, 2025

PaulMeuris said:
Did you try it without the port number like below?

$"jdbc:jtds:sqlserver://${DBLoaction}\${InstanceName};databaseName=${DBName};"$

Yes, I tested it but it gives an error.

teddybear · Mar 6, 2025

behnam_tr said:
SQL1.Initialize2("net.sourceforge.jtds.jdbc.Driver", _
$"jdbc:jtds:sqlserver://${DBLoaction}:1335;databaseName=${DBName};"$, DBUser, DBPass)
Log("Connected : " & SQL1.IsInitialized)[/CODE]

The sql server‘s port you are using is 1335. but by default, the first instance of SQL Server Express, uses TCP port 1433 for data communication

behnam_tr said:
Yes, I tested it but it gives an error.

If you want to connect to server without port, the server port should be 1433

besoft · Mar 7, 2025

Hi, maybe this will help:

connction string:

sqlMS.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://name\MSSQLSERVER;databaseName=dbase;user=user;password=pass;encrypt=true;trustServerCertificate=true")

lib:

    #AdditionalJar: mssql-jdbc-12.4.1.jre11.jar

Works for me without problems

BR

Chris2 · Mar 7, 2025

behnam_tr said:
I want the connection process to be done automatically and without any extra work.

Just to throw another possible option at you, assuming the app is running under Windows, have you looked at using Windows Authentication?

I haven't done it with JdbcSQL, but have with the connection pool in jRDC2 (using the MIcrosoft MSSQL JDBC). I imagine it would work similarly with JdbcSQL

behnam_tr · Mar 7, 2025

teddybear said:
The sql server‘s port you are using is 1335. but by default, the first instance of SQL Server Express, uses TCP port 1433 for data communication

If you want to connect to server without port, the server port should be 1433

So far, the following results have been tested and were successful.

With jtds Driver

Connect with tcp port:

 #AdditionalJar: jtds-1.3.2.jar
 Private MSSQL As SQL
Private DBLocation As String = "localhost"
Private InstanceName As String="SQLEXPRESS"
Private SqlPort As Int = 1335
Private DBName As String = "test1"
Private DBUsername As String = "sa"
Private DBPassword As String = "12345"
MSSQL.Initialize2("net.sourceforge.jtds.jdbc.Driver", $"jdbc:jtds:sqlserver://${DBLocation}:${SqlPort}/${DBName};instance=${InstanceName};"$, DBUsername, DBPassword)

Connect With InstanceName:

MSSQL.Initialize2("net.sourceforge.jtds.jdbc.Driver", $"jdbc:jtds:sqlserver://./${DBName};instance=${InstanceName};namedPipe=true;domain=${DBLocation}"$, DBUsername, DBPassword)

~~If you want to connect to server without port, the server port should be~~ ~~1433~~ : Nor Correct
I even disabled the port and the connection was OK. I even enabled it and changed the port and it was still OK. Of course, I think it only works on the local network, right? When the connection is via a remote and internet server, we have to enable and use the port, and it will not be possible to connect with just the instance name.
-----------------------------------------------------------------------------------------------------
With MicroSoft jdbc Driver

B4X:

#AdditionalJar : mssql-jdbc-12.4.1.jre8.jar
(tested with jdk14)
Dim driverclass As String = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   
with port :
    MSSQL.Initialize2(driverclass, _
     $"jdbc:sqlserver://localhost:1335;databaseName=${DBName};instanceName=${InstanceName};encrypt=true;trustServerCertificate=true;sslProtocol=TLSv1.2;"$,DBUsername,DBPassword)
   
with instanceName :
MSSQL.Initialize2(driverclass, _
     $"jdbc:sqlserver://localhost;databaseName=${DBName};instanceName=${InstanceName};encrypt=true;trustServerCertificate=true;sslProtocol=TLSv1.2;"$,DBUsername,DBPassword)

MrKim · Apr 30, 2025

behnam_tr said:
So far, the following results have been tested and were successful.

With jtds Driver

Connect with tcp port:

#AdditionalJar: jtds-1.3.2.jar Private MSSQL As SQL Private DBLocation As String = "localhost"

"localhost" is your current machine. If you try to connect to the db from a remote computer you will get different results. If you are doing it wireless you will get different results than if you are on ethernet. If you are running wireless I have found the only thing that works with the jtds driver is ///ipaddressort. With the Microsoft driver if you are not using the default sql instance I have found the port is required. Even if it is 1433. You will also need the computer name so you would need //COMPUTERNAME\SQLINSTANCEORT (and yes, TWO / and one OR 2 \). If wireless I have not found enabling sql server browser useful in spite of documentation to the contrary. Read this carefully and you will save yourself the substantial number of hours I have spent learning these tings the hard way.

B4J Question (solved)how Connect to mssql with InstanceName ??

behnam_tr

Active Member

behnam_tr

PaulMeuris

Well-Known Member

behnam_tr

Active Member

PaulMeuris

Well-Known Member

aeric

Expert

JRDC2 connect to MSSQLLocalDb

behnam_tr

Active Member

teddybear

Well-Known Member

besoft

Active Member

Chris2

Active Member

behnam_tr

Active Member

MrKim

Well-Known Member

Similar Threads

B4J Question (solved)how Connect to mssql with InstanceName ??

Active Member

Well-Known Member

Active Member

Well-Known Member

Expert

Active Member

Well-Known Member

Active Member

Active Member

Active Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency