B4J Library [B4X] [B4XLib] SD_SQL (direct access to MySQL, MariaDB, MS SQL, FireBird)

Based on @Peter Simpson's code (here), I developed a library that allows you to connect directly to MySql, MsSql (for the moment) databases with B4A and B4J. To be clear, you don't need JRDC to connect to the database located on a server

(I am working on a version for b4i, but it is only obtainable with a wrap. it will take a long time because I have never written a line in objective-c).
SQL library already exists for B4J and it would not be necessary to create a new library. But to maintain compliance in writing the code I preferred to develop a B4XLib that works for both B4i and B4j

Being a B4XLib class, the code is perfectly reusable. It suffices on these two jar files jtds-1.3.1.jar and mysql-connector-java-5.1.47-bin.jar. (You can find these files in the @Peter Simpson thread or on the internet). With both B4A and B4J the jar files must be copied to the libreries folder.
To connect to the Firebird database read post 26

It is not a wrap, it does not depend on the internal SQL library. It is written entirely in B4X
You can use it to access DataBases that allow direct access both in a local network and through the internet. you cannot access databases of external services that allow access only through PHP or ASP

NOTE: You can use this library for personal and commercial use. Include it in your projects.. Attention, even if it is a B4XLib library, it is not allowed to decompress it, modify it, change its name or redistribute it without the permission of the author
For B4A Add this on Manifest:

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

SD_SQL

Author:
Version: 0.07

• SD_ResultSet
  • Functions:
    • Close
    • first As Boolean
    • GetBytes (ColumnName As String) As Byte()
      You can use it like GetBlob
    • GetBytes2 (index As Int) As Byte()
      You can use it like GetBlob
    • GetColumnName (index As Int) As String
    • GetDouble (ColumnName As String) As Double
    • GetDouble2 (index As Int) As Double
    • GetFloat (ColumnName As String) As Float
    • GetFloat2 (index As Int) As Float
    • GetInt (ColumnName As String) As Int
    • GetInt2 (index As Int) As Int
    • GetLong (ColumnName As String) As Long
    • GetLong2 (index As Int) As Long
    • GetRow As Int
    • GetShort (ColumnName As String) As Short
    • GetShort2 (index As Int) As Short
    • GetString (ColumnName As String) As String
    • GetString2 (index As Int) As String
    • Initialize (OriginalResultSet As JavaObject)
      Initializes the object. You can add parameters to this method if needed.
    • isClosed As Boolean
    • last As Boolean
    • NextRow As Boolean
    • PreviousRow As Boolean
    • relativeRow (row As Int) As Boolean
      is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
  • Properties:
    • ColumnCount As Int [read only]
• SD_SQL
  • Events:
    • Ready (Success As Boolean)
  • Fields:
    • MyConnection As Object
  • Functions:
    • Close
    • Connect (DriverClass As String, JDBCurl As String, DBUser As String, DBPassword As String)
    • connected As Boolean
    • ExecNonQuery (Statement As String) As Boolean
    • ExecQuery (Query As String) As SD_ResultSet
      Return resultSet object
    • ExecQueryResutSet (Query As String) As Object
    • Initialize (CallBack As Object, Event As String)
      Initializes the object. Insert row with #AdditionalJar
      MySQL Driver <code> #AdditionalJar: mysql-connector-java-5.1.47-bin.jar</code>
      MSSQL Driver <code> #AdditionalJar: jtds-1.3.1.jar</code>
      PostgreSQL Driver <code> #AdditionalJar: postgresql-42.2.6.jar</code>
      MariaDB Driver <code> #AdditionalJar: mariadb-java-client-2.4.2.jar</code>
      Oracle Driver <code> #AdditionalJar: ojdbc8.jar</code>

---

Update 0.02

Add: GetRow, first, last, isClosed, GetShort, GetShort2​

​

Update 0.03

Add: PreviousRow, relativeRow​

​

Update 0.04

ExecNonQuery return boolean success value​

​

Update 0.06

Fix Bugs​

​

Update 0.07

Added the connect method, Added the connected field. Examples updated​

 

[Star-Dust]

    Dim MSSQL As SD_SQL
    Private MSLocation As String = "192.168.0.147"
    '.......
    MSSQL.Initialize(Me,"MSSQL")
    MSSQL.Connect("net.sourceforge.jtds.jdbc.Driver", $"jdbc:jtds:sqlserver://${MSLocation}/test"$, MSUsername, MSPassword)
    Wait For MSSQL_Ready(Success As Boolean)
    If Success Then
        Dim RS As SD_ResultSet = MSSQL.ExecQuery("SELECT * FROM SYS.TABLES")
        Do While RS.NextRow
            Log(RS.GetString2(0))
        Loop
    End If

    Dim MYSQL As SD_SQL
    Private MyLocation As String = "192.168.1.102"
    '.......
    MYSQL.Initialize(Me,"MYSQL")
    MYSQL.Connect( "com.mysql.jdbc.Driver", $"jdbc:mysql://${MyLocation}/register_Northwind_MySQL?useSSL=false"$,  MyUsername, MyPassword)
    Wait For MYSQL_Ready(Success As Boolean)
    If Success Then
        Dim RS As SD_ResultSet = MYSQL.ExecQuery("SHOW TABLES")
        Do While RS.NextRow
            Log(RS.GetString2(0))
        Loop
    End If

Tested with Firebird database and works fine:
- B4J: jaybird-full-3.0.8.jar
- B4A: Jaybird_2_2_12.aar

Private FBSQL As SD_SQL
Private FBUsername As String = "SYSDBA"
Private FBPassword As String = "masterkey"

FBSQL.Initialize(Me,"FBSQL")
FBSQL.Connect( "org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:127.0.0.1/3050:" & "E:\Projetos\Firebird\B4X.FDB", FBUsername, FBPassword)

 

[tpachhai]

Thank You

 

[ibs06t]

hi
im try this lib but not working and no error return success is false only
can i use this lib for connect remote mysql server

 

[Star-Dust]

Hello,

Did you download the additional jar libraries from @Peter Simpson's page as I indicated? (Here)

Give us more details, are you using B4A or b4J? What do you want to connect to?

 

[ibs06t]

hi
i need connect b4a via remote mysql server its possible (sd)

 

[Star-Dust]

hi
i need connect b4a via remote mysql server its possible (sd)

This method is for connecting to a database locally, then on the same local network or on the same machine.

If you want to connect your device to a database hosted on a server, you need to use the methods offered by the service, usually php, asp or other (see here). Because for security reasons it is not allowed to directly access the Database within a hosted server.

 

[ibs06t]

tnks sd

 

[Erel]

If you want to connect your device to a database hosted on a server, you need to use the methods offered by the service, usually php, asp or other

Why? Use jRDC2. Simpler and better.

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

 

[Star-Dust]

Why? Use jRDC2. Simpler and better.

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

Yes, I know the solution you are showing me.
This is a workaround for those who for some reason cannot install JRDC on the server

 

[K_Kai]

HI Star-Dust
thanks for you lib
but have some wrong when I try changed it,
I Try change this:
Dim rs As SD_ResultSet= MSSQL.ExecQuery("SELECT * FROM app where id='"& EditText1 &"'")

Sub MSSQL_Ready (Success As Boolean)
    ListViewMs.SingleLineLayout.Label.TextColor=Colors.Black
    ListViewMs.SingleLineLayout.Label.Textsize=13
    ListViewMs.Clear
    If Success Then
       ' Dim rs As SD_ResultSet= MSSQL.ExecQuery("SELECT * FROM app where id=2")''It's Right
        Dim rs As SD_ResultSet= MSSQL.ExecQuery("SELECT * FROM app where id='"& EditText1 &"'")'It's wrong,But I Need this，Can you help me ,thank you very much

        Do While rs.NextRow
            ListViewMs.AddSingleLine(rs.GetString2(1))

        Loop
    Else
    End If
End Sub

 

[K_Kai]

The problem is solved. I made a simple mistake
EditText1.text

 

[Erel]

There are several mistakes in your code:

1. Making a synchronous query to a remote database. This is very bad.
2. Making non-parameterized query. Same as above.
3. Not closing ResultSet.

 

[K_Kai]

?Sorry I‘m a Novice
I dont know how to change，but I’m Very happy to connect my mssql

if I want write to database,How do I write
like VB6:

rs.addnew
rs("xxx")=text1.text
rs("yyy")=text2.text
rs.update

and HOW I modify my db
like:

Conn.Execute "update db1 set student= '" & text1.text & "' where id = " & Label1.caption & ""

can you HELP me？
thank YOU very much！

 

[Erel]

Watch the SQL video tutorial: https://www.b4x.com/etp.html
I'm not sure whether this library supports async methods and parameterized queries. If not then you can use JdbcSQL.

 

[Star-Dust]

Watch the SQL video tutorial: https://www.b4x.com/etp.html
I'm not sure whether this library supports async methods and parameterized queries. If not then you can use JdbcSQL.

It does not support them, although it is possible to implement them.

Simply because it is a solution born for b4a and specific for rare cases that it is not possible to use jRDC.

The b4j version is not necessary (already exist JdbcSQL), but it was created to maintain compatibility with the b4a version in the case of creating a cross-platform project

 

[Erel]

JdbcSQL is actually a B4A + B4J library.

 

[swamisantosh]

Based on @Peter Simpson's code (here), I developed a library that allows you to connect to MySql, MsSql (for the moment) databases with B4A and B4J. (I am working on a version for b4i, but it is only obtainable with a wrap. it will take a long time because I have never written a line in objective-c).
SQL library already exists for B4J and it would not be necessary to create a new library. But to maintain compliance in writing the code I preferred to develop a B4XLib that works for both B4i and B4j

Being a B4XLib class, the code is perfectly reusable. It suffices on these two jar files jtds-1.3.1.jar and mysql-connector-java-5.1.47-bin.jar. (You can find these files in the @Peter Simpson thread or on the internet). With both B4A and B4J the jar files must be copied to the libreries folder.

It is not a wrap, it does not depend on the internal SQL library. It is written entirely in B4X

NOTE: You can use this library for personal and commercial use. Include it in your projects.. Attention, even if it is a B4XLib library, it is not allowed to decompress it, modify it, change its name or redistribute it without the permission of the author

SD_SQL

Author:
Version: 0.03

• SD_ResultSet
  • Functions:
    • Close
    • first As Boolean
    • GetBytes (ColumnName As String) As Byte()
      You can use it like GetBlob
    • GetBytes2 (index As Int) As Byte()
      You can use it like GetBlob
    • GetColumnName (index As Int) As String
    • GetDouble (ColumnName As String) As Double
    • GetDouble2 (index As Int) As Double
    • GetFloat (ColumnName As String) As Float
    • GetFloat2 (index As Int) As Float
    • GetInt (ColumnName As String) As Int
    • GetInt2 (index As Int) As Int
    • GetLong (ColumnName As String) As Long
    • GetLong2 (index As Int) As Long
    • GetRow As Int
    • GetShort (ColumnName As String) As Short
    • GetShort2 (index As Int) As Short
    • GetString (ColumnName As String) As String
    • GetString2 (index As Int) As String
    • Initialize (OriginalResultSet As JavaObject)
      Initializes the object. You can add parameters to this method if needed.
    • isClosed As Boolean
    • last As Boolean
    • NextRow As Boolean
    • PreviousRow As Boolean
    • relativeRow (row As Int) As Boolean
      is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
  • Properties:
    • ColumnCount As Int [read only]
• SD_SQL
  • Events:
    • Ready (Success As Boolean)
  • Fields:
    • MyConnection As Object
  • Functions:
    • Close
    • ExecNonQuery (Statement As String)
    • ExecQuery (Query As String) As SD_ResultSet
      Return resultSet object
    • ExecQueryResutSet (Query As String) As Object
    • Initialize (CallBack As Object, Event As String, DriverClass As String, JDBCurl As String, DBUser As String, DBPassword As String)
      Initializes the object. You can add parameters to this method if needed.

Update 0.02

Add: GetRow, first, last, isClosed, GetShort, GetShort2​

​

Update 0.03

Add: PreviousRow, relativeRow​

With B4J Code i able to connect to both MYSQL & MSSQL database

with B4A Code i can connect to MYSQL but i'm getting error message for MSSQL Database
Error : Not connected: java.sql.SQLException: Network error IOException: failed to connect to /192.168.43.191 (port 1433) from /:: (port 39914): connect failed: ETIMEDOUT (Connection timed out)


Dim MSSQL As SD_SQL
Private MSLocation As String = "192.168.43.191/global:1433;instanceName=ULTIMATE;databaseName=global;user=sa;password=solution.123;encrypt=true;trustServerCertificate=false;loginTimeout=30;"
Private MSUsername As String = "sa"
Private MSPassword As String = "solution.123"


Log("---------- NorthWind Database (MSSQL) ----------")
MSSQL.Initialize(Me,"MSSQL","net.sourceforge.jtds.jdbc.Driver", $"jdbc:jtds:sqlserver://${MSLocation}/global"$, MSUsername, MSPassword)

Please guide.

 

[swamisantosh]

Code works fine with B4J, but it code does not work B4A

 

[Star-Dust]

Make sure you used the correct jar files (jtds-1.3.1.jar and mysql-connector-java-5.1.47-bin.jar).
The library has been tested on both platforms.
At the moment I cannot assist you on this project because I am off site. Next week I will be able to review.

PS. Kindly don't write with huge font, it's the same as screaming