B4J Question MS SQL server 2014 using MS JDBC

[Nokia]

I am reading some of these threads and not getting a lot out of connecting to MS SQL server. for what I can get.. I have downloaded the JDBC from mocrosoft's website and it has 2 jar files..

1. mssql-jdbc-6.2.2.jre7.jar
2. mssql-jdbc-6.2.2.jre8.jar

I have read through the SQL tutorial but it seems like a jumbled mess.. I can connect to mysql database no problem.. just trying to connect to a MS SQL 2014 server over an internal network.

I have added this line:

#AdditionalJar: mssql-jdbc-6.2.2.jre8.jar

but unsure what I need to put in initialize section:

Dim sqlMS As SQL
sqlMS.Initialize()

after this am I reading the records the same as mySQL connection?

also there is some .Dll's that came with the JDBC.. do I need to do anything with these or not?

https://www.microsoft.com/en-us/download/details.aspx?id=55539

 

[Nokia]

I have a successful connection and data read with this code:

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

Sub Process_Globals
   Private fx As JFX
   Private MainForm As Form
   Dim sqlMS As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
   MainForm = Form1
   MainForm.SetFormStyle("UNIFIED")
   'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
   MainForm.Show
   
   sqlMS.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://<server name>:1433;databaseName=<Database name>;user=<user name>;password=<user password>;")
   Log(sqlMS.IsInitialized)
   sqlMS.BeginTransaction
   Try
       Dim rs As ResultSet = sqlMS.ExecQuery("SELECT first_name, last_name FROM user_mstr ORDER BY first_name")
       Do While rs.NextRow
           Log(rs.GetString("first_name") & " " & rs.GetString("last_name"))
       Loop
       sqlMS.Close
       
   sqlMS.TransactionSuccessful
   Catch
       Log(LastException.Message) 'no changes will be made
   End Try
   
End Sub

is there anything that I can do better or missing?
especially in the sqlMS.Initialize() section?

I did not do anything with the .Dll is the file just left them where I extracted them. The .exe file download from Microsoft is just a winzip self extractor..

 

[Erel]

It is better to call InitializeAsync and Wait For the Ready event. Also better to call ExecQueryAsync:
https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

 

[Nokia]

It is better to call InitializeAsync and Wait For the Ready event. Also better to call ExecQueryAsync:
https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

thanks for the info.. I keep getting connection is closed if I try to run 2 queries.

    sqlMS.Initialize(mdlShared.GetDriverClass,mdlShared.GetJDBC_urs(workingFolder, strConfigFile))
   
   If sqlMS.IsInitialized = True Then
       '-------------------------------------------------------------------------------------------
       Dim sql As String = "select top 1 enterprise_name, enterprise_id from enterprise where delete_ind = 'N' order by enterprise_id"
       Dim SenderFilter As Object = sqlMS.ExecQueryAsync("SQL", sql, Null)
       Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
       If Success Then
           sqlMS.BeginTransaction
               Do While rs.NextRow
                   cboEnt.Value =    rs.GetString("enterprise_name")
                   strDefaultEntName = rs.GetString("enterprise_name")
                   strDefaultEntID = rs.GetString("enterprise_id")
               Loop
               rs.Close
               
           sqlMS.TransactionSuccessful
       Else
           Log(LastException)
           mdlShared.LogErrorToFile("loadSQLEntPrac.1", LastException.Message)
           sqlMS.Rollback
       End If
       '--------------------------------------------------------------------------------------------
       sql = "select enterprise_name, enterprise_id from enterprise where delete_ind = 'N' order by enterprise_id"
       Dim SenderFilter As Object = sqlMS.ExecQueryAsync("SQL", sql, Null)
       Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
       If Success Then
           sqlMS.BeginTransaction
           Do While rs.NextRow
               cboEnt.Items.Add(rs.GetString("enterprise_name"))
           Loop
           rs.Close
               
           sqlMS.TransactionSuccessful
       Else
           Log(LastException)
           mdlShared.LogErrorToFile("loadSQLEntPrac.2", LastException.Message)
           sqlMS.Rollback
       End If
       '--------------------------------------------------------------------------------------------

       sqlMS.Close
   End If

 

[Nokia]

I figured out the error was coming from:

sqlMS.BeginTransaction

I took them out and it works..