Other MSSQL database Connection: inline-Java version

jkhazraji

Active Member
Licensed User
Longtime User
Although this may have been mentioned before here, It is worth mentioning again :
B4X:
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region
#AdditionalJar: jtds-1.3.1.jar

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private xui As XUI


End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Layout1")
    MainForm.Show
    Dim MyJO As JavaObject = Me
    Dim server As String ="//dbHost\sqlexpress";
    Dim userid As String="sa"
    Dim password As String="secret"
    Dim sqlStr As String ="select * from users where id > 20"
    Dim s As String = MyJO.RunMethod("DBConnect",Array(server,userid,password,sqlStr))
    Log(s)
End Sub
#if Java
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
 
public static String DBConnect(String server, String userid, String password, String sqlStr) throws SQLException, ClassNotFoundException {
 
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://"+ server, userid,password); 
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sqlStr);
        String result="";
        while (rs.next()) {
            result = result.concat(rs.getString("id") + ":" + rs.getString("usrname"));
       }   
        return result;   
}
#End If
 

jkhazraji

Active Member
Licensed User
Longtime User
Why not use jSQL? There are several mistakes in this code and 0 advantages over jSQL.
Can you please refer to these mistakes, because it runs smoothly without any mistake even on a remote website with MSSQL server.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
There are too many for me to list them all.
Here are a few:

1. Connecting to a remote database on the main thread was valid up until 1990. Not anymore.
2. You are not closing the SQL connection.
3. You are not using parameterized queries (this was valid up until ~2000).
4. There is no way to use this snippet in a generic manner.
5. You have chosen to use such hack when there is a very good alternative which is 100 times more powerful and simpler to use.
 
Upvote 0

Star-Dust

Expert
Licensed User
Longtime User
Read all fields as if they were strings. You haven't considered the other types. (Real, Blob, Data)

Concatenate all fields on a string. The separator character can no longer be contained within the field or is interpreted as a separator.

You can have fields after separating them only with a numeric index and recalling the field name
B4X:
Dim Field() Ad String = Regex.Split(":",Result)
Dim Id As String  = Field(0)

If you want to select only some answer fields in the query, you must also modify the Java function which would become specific only for that query.
"select user, image from users where id > 20"
 
Last edited:
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
This code make things more complicated.
How do you handle parameterized statements?
How to read the resultset?
Use B4X with jSQL is much simpler.
I demonstrated the code run by video .. It is simple and light-weight
 
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
Read all fields as if they were strings. You haven't considered the other types.

Concatenate all fields on a string. The separator character can no longer be contained within the field or is interpreted as a separator.

You can have fields after separating them only with a numeric index and recalling the field name

If you want to select only some answer fields in the query, you must also modify the Java function which would become specific only for that query.
So it is a matter of string concatenation. I can read int values as well not string only.. Concatenation is for the purpose of display. You can manipulate the result the way you want.
 
Upvote 0

Star-Dust

Expert
Licensed User
Longtime User
So it is a matter of string concatenation. I can read int values as well not string only.. Concatenation is for the purpose of display. You can manipulate the result the way you want.
My observation is more complex. It is in the code that you have shown it does not appear to respond to all of my and others' comments.

I really appreciate those who want to add new features by inserting code in Java. Here perhaps the idea needs to be developed further to be considered an improvement or addition to what b4a already offers
 
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
My observation is more complex. It is in the code that you have shown it does not appear to respond to all of my and others' comments.

I really appreciate those who want to add new features by inserting code in Java. Here perhaps the idea needs to be developed further to be considered an improvement or addition to what b4a already offers
I appreciate all of your comments. Thanks a lot.
 
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
Worst error so far is the use of JTDS, last update was on 2013, The official miscrosoft drivers are the best and most stable, also they are updated at least several times per year
It is not obsolete, then why should it be the worst error. See this:
jtds-1.3.1-a.jar can be used.
I will be grateful if you show me an example in this forum that uses Microsoft drivers.
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
there are plenty:
It is not obsolete,
it is JTDS only supports JDBC 2.1 while Microsoft drivers support JDBC 4.0 +, there are several improvements between both versions, including performance.
why use something updated when for the same amout of effort you can have top notch?
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Main (MSSQL.b4j):
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: jtds-1.3.1.jar
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Public SQL1 As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.Show
    Dim PagesManager As B4XPagesManager
    PagesManager.Initialize(MainForm)
    SQL1.Initialize2("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://Aeric-HP;databaseName=MyDatabase;instance=SQLEXPRESS;integratedSecurity=true", "sa", "password")
End Sub

'Template version: B4J-1.0
#Region Delegates
Sub MainForm_FocusChanged (HasFocus As Boolean)
    B4XPages.Delegate.MainForm_FocusChanged(HasFocus)
End Sub

Sub MainForm_Resize (Width As Double, Height As Double)
    B4XPages.Delegate.MainForm_Resize(Width, Height)
End Sub

Sub MainForm_Closed
    B4XPages.Delegate.MainForm_Closed
End Sub

Sub MainForm_CloseRequest (EventData As Event)
    B4XPages.Delegate.MainForm_CloseRequest(EventData)
End Sub

Public Sub MainForm_IconifiedChanged (Iconified As Boolean)
    B4XPages.Delegate.MainForm_IconifiedChanged(Iconified)
End Sub
#End Region

B4XMainPage (B4XMainPage.bas):
#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=MSSQL.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
End Sub

'You can see the list of page related events in the B4XPagesManager object. The event name is B4XPage.

Private Sub Button1_Click
    GetData
End Sub

Private Sub GetData
    Dim strSQL As String = $"SELECT TOP 10 * FROM [MyDatabase].[dbo].[Users] WHERE id > ?"$
    Dim strResult As String
    Dim RS As ResultSet = Main.SQL1.ExecQuery2(strSQL, Array As String(1))
    Do While RS.NextRow
        strResult = strResult & RS.GetString("id") & ":" & RS.GetString("usrname") & CRLF
    Loop
    RS.Close
    Log(strResult)
End Sub

SQL:
USE [MyDatabase]
GO
/****** Object:  Table [dbo].[users]    Script Date: 8/11/2021 1:15:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [usrname] [varchar](50) NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[users] ON

GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (1, N'Speman tab')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (2, N'Proximol Ganules')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (3, N'Renanive caps')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (4, N'Uvamin retard')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (5, N'Melatonin')
GO
SET IDENTITY_INSERT [dbo].[users] OFF
GO
 

Attachments

  • MSSQL.zip
    7.7 KB · Views: 160
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
Main (MSSQL.b4j):
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: jtds-1.3.1.jar
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Public SQL1 As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.Show
    Dim PagesManager As B4XPagesManager
    PagesManager.Initialize(MainForm)
    SQL1.Initialize2("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://Aeric-HP;databaseName=MyDatabase;instance=SQLEXPRESS;integratedSecurity=true", "sa", "password")
End Sub

'Template version: B4J-1.0
#Region Delegates
Sub MainForm_FocusChanged (HasFocus As Boolean)
    B4XPages.Delegate.MainForm_FocusChanged(HasFocus)
End Sub

Sub MainForm_Resize (Width As Double, Height As Double)
    B4XPages.Delegate.MainForm_Resize(Width, Height)
End Sub

Sub MainForm_Closed
    B4XPages.Delegate.MainForm_Closed
End Sub

Sub MainForm_CloseRequest (EventData As Event)
    B4XPages.Delegate.MainForm_CloseRequest(EventData)
End Sub

Public Sub MainForm_IconifiedChanged (Iconified As Boolean)
    B4XPages.Delegate.MainForm_IconifiedChanged(Iconified)
End Sub
#End Region

B4XMainPage (B4XMainPage.bas):
#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=MSSQL.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
End Sub

'You can see the list of page related events in the B4XPagesManager object. The event name is B4XPage.

Private Sub Button1_Click
    GetData
End Sub

Private Sub GetData
    Dim strSQL As String = $"SELECT TOP 10 * FROM [MyDatabase].[dbo].[Users] WHERE id > ?"$
    Dim strResult As String
    Dim RS As ResultSet = Main.SQL1.ExecQuery2(strSQL, Array As String(1))
    Do While RS.NextRow
        strResult = strResult & RS.GetString("id") & ":" & RS.GetString("usrname") & CRLF
    Loop
    RS.Close
    Log(strResult)
End Sub

SQL:
USE [MyDatabase]
GO
/****** Object:  Table [dbo].[users]    Script Date: 8/11/2021 1:15:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [usrname] [varchar](50) NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[users] ON

GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (1, N'Speman tab')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (2, N'Proximol Ganules')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (3, N'Renanive caps')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (4, N'Uvamin retard')
GO
INSERT [dbo].[users] ([id], [usrname]) VALUES (5, N'Melatonin')
GO
SET IDENTITY_INSERT [dbo].[users] OFF
GO
Great .. thanks
 
Upvote 0
Top