B4J Question Microsoft SQL Query

[Ronald Berardinis]

I am confused on the best method to access Microsoft SQL database.
I see there is a library jSQL reference.
however, I see jtds-1.3.1.jar discussions.
What is recommended for best solution for remote server based MS SQL database.

 

[OliverA]

I see there is a library jSQL reference.

That's what you'll use (direct or indirectly).

however, I see jtds-1.3.1.jar discussions.

Don't. Use Microsoft's JDBC drivers (https://docs.microsoft.com/en-us/sq...bc-driver-for-sql-server?view=sql-server-2017). The current version is 7.4.xxx. Note, they are JDK specific (JRE 8 will work with JRE/JDK 11/12, but not vice versa):

• mssql-jdbc-7.4.1.0.jre8.jar requires a JRE of 8 and supports the JDBC 4.2 API
• mssql-jdbc-7.4.1.0.jre11.jar requires a JRE of 11 and supports the JDBC 4.3 API
• mssql-jdbc-7.4.1.0.jre12.jar requires a JRE of 12 and supports the JDBC 4.3 API

What is recommended for best solution for remote server based MS SQL database.

Either direct via jSQL or indirect via jRDC2 (jRDC2 uses jSQL), depending on what you are trying to do.

 

[Ronald Berardinis]

wow. that's direct. thanks.

So I am new to this.
Using the Microsoft drivers make the most sense.
I am just doing Select, inserts and updates.
When you say direct/indirect, I don't know what that means?
When I VS .NET, I create a dataset for select query with dataadapter.
what method would duplicate the .NET method.

.NET code
Cmd = Conn.CreateCommand
Cmd.CommandText = SQL
DataAdapter.SelectCommand = Cmd
DataAdapter.Fill(ds)

 

[DonManfred]

When you say direct/indirect, I don't know what that means?

Direct: you app does a direct connection to your database

Indirect: you are using a jRDC2 Server in the middle.
Your app connect to the jRDC2 which is connecting to the Database, fetching the data and return it back to your app.
Search for jRDC2 tutorials...

 

[Peter Simpson]

@Ronald Berardinis, this is how I connect to multiple databases.

PLEASE NOTE: There are multiple ways to connect to databases, I hope that this helps you to reach your goals.

CLICK HERE for more information...

 

[OliverA]

Cmd = Conn.CreateCommand
Cmd.CommandText = SQL
DataAdapter.SelectCommand = Cmd
DataAdapter.Fill(ds)

Looking at the sample that @Peter Simpson provides, all this boils down to this one line:

Dim RS As ResultSet = SQL.ExecQuery("SELECT * FROM SYS.TABLES")

Where Net's Conn ~ B4J's SQL object
CreateCommand, CommandText, SelectCommand and Fill are wrapped into B4J's ExecQuery of the SQL object

Something else you want to familiarize yourself with is
0) jSQL documentation https://www.b4x.com/b4j/help/jsql.html
1) Wait For https://www.b4x.com/android/forum/threads/b4x-resumable-subs-sleep-wait-for.78601/#content
2) Wait For with return values https://www.b4x.com/android/forum/t...hat-return-values-resumablesub.82670/#content
2) SQL with Wait For: https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/

 

[OliverA]

Here's a condensed version of @Peter Simpson code (condensed, since I'm only accessing MSSQL). It's been adapted to be used by a GUI application. Note the use of Wait For. You will need to download Microsoft's JDBC driver, extract it and place it into the Additional Libraries folder that you should have configured under Tools->Configure Paths. You need to adapt the DBLocation, DBUsername and DBPassword variables to match up with your MSSQL install. Since we are performing a SELECT * FROM SYS.TABLES, you may want to make sure that the user you pick has proper rights for this query. If you are getting issues with connecting to the DB, make sure the DBLocation string is set up properly (correct domain/IP, correct port, DB name (if required) and any required parameters are set correctly).

#Region Project Attributes
   #MainFormWidth: 600
   #MainFormHeight: 600
   
   #AdditionalJar: mssql-jdbc-7.4.1.jre8.jar
   
#End Region

Sub Process_Globals
   Private fx As JFX
   Private MainForm As Form
   
   Private DBDriver As String = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   Private DBLocation As String = "jdbc:sqlserver://DomainName||IPAddress:PortNumber[/OptionalDBName][;Optional1stParameter][;Optional2ndParamter][;OptionalNthParameter]"
   'Note: the above could be as simple as
   '"jdbc:sqlserver://127.0.0.1:1433"
   'or even something like this
   '"jdbc:sqlserver://subdomain.acme.com:1000/secretdb;sslProtocol=TLS;trustServerCertificate=true"
   Private DBUsername As String = "DBUsername"
   Private DBPassword As String = "DBPassword"
   
   Private Conn As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
   MainForm = Form1
   'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
   MainForm.Show
   
   Conn.InitializeAsync("conn1",DBDriver,DBLocation,DBUsername,DBPassword)
   Wait For conn1_Ready(Success As Boolean)
   If Not(Success) Then
       Log("Failed to connect to database. Bye!")
       ExitApplication
   End If
   
   Log("Successfully connected!")
   Wait For (Conn.ExecQueryAsync("sql","SELECT * FROM SYS.TABLES", Null)) sql_QueryComplete(Success As Boolean, rs As ResultSet)
   If (Success) Then
       Do While rs.NextRow
           Log(rs.GetString2(0))
       Loop
       rs.Close 'Do not forget to close your result set!
   Else
       Log("Query was not successful!")
   End If
   
   Conn.Close 'Closing the DB connection
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
   Return True
End Sub

 

[Ronald Berardinis]

thank you