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

[behnam_tr]

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.

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

 

[PaulMeuris]

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]

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]

Did you try it without the port number like below?

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

 

[aeric]

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]

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]

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

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]

Hi, maybe this will help:

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

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

Works for me without problems

BR

 

[Chris2]

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]

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

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

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

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

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

With jtds Driver

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