Other MSSQL database Connection: inline-Java version

J

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
 
Sort by date Sort by votes
Erel said:
Why not use jSQL? There are several mistakes in this code and 0 advantages over jSQL.
Click to expand...
Can you please refer to these mistakes, because it runs smoothly without any mistake even on a remote website with MSSQL server.
 
Upvote 0
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
This code make things more complicated.
How do you handle parameterized statements?
How to read the resultset?
Use B4X with jSQL is much simpler.
 
Upvote 0
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"
Click to expand...
 
Last edited:
Upvote 0
aeric said:
This code make things more complicated.
How do you handle parameterized statements?
How to read the resultset?
Use B4X with jSQL is much simpler.
Click to expand...
I demonstrated the code run by video .. It is simple and light-weight
 
Upvote 0
Star-Dust said:
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.
Click to expand...
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
jkhazraji said:
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.
Click to expand...
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
Star-Dust said:
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
Click to expand...
I appreciate all of your comments. Thanks a lot.
 
Upvote 0
EnriqueGonzalez said:
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
Click to expand...
It is not obsolete, then why should it be the worst error. See this:

jtds jdbc driver with standalone package

1. Download the attached zip. Put the jar file in the additional libraries folder and Charsets.properties in your project folder. 2. jtds-1.3.1-a.jar replaces jtds-1.3.1.jar. #AdditionalJar: jtds-1.3.1-a #CustomBuildAction: After Packager, %WINDIR%\System32\robocopy.exe, ..\ temp\build\bin\...
www.b4x.com www.b4x.com
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
there are plenty:
jkhazraji said:
It is not obsolete,
Click to expand...
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
[CODE lang="b4x" title="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[/CODE]

[CODE lang="b4x" title="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[/CODE]

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

Upvote 0
aeric said:
[CODE lang="b4x" title="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[/CODE]

[CODE lang="b4x" title="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[/CODE]

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
Click to expand...
Great .. thanks
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Erel
  • Article Article
B4A Library [B4X] jRDC3 - remote database connector
Replies
3
Views
4K
Erel
Erel
Mashiane
  • Article Article
Share My Creation [BANanoVuetifyAD3] BANanoServer - DirectMSSQL CRUD
Replies
0
Views
4K
Mashiane
Mashiane
Peter Simpson
  • Article Article
B4J Tutorial ✅ JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases
Replies
12
Views
24K
Peter Simpson
Peter Simpson
Bladimir Carrillo
  • Question Question
B4J Question Web app with MsSQl database
Replies
18
Views
11K
Erel
Erel
Erel
  • Locked
  • Article Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
139
Views
206K
Erel
Erel
Back
Top