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]

Well, I'll give a quick summary.
To access MySql you had to import a driver or connector in C which is impossible for me because I ignore the language and compile with Host Builder.

So I decided to create a collector/driver from scratch in B4X... and it seems to work... I still have to improve and finalize ResultSet and it will take a week or more.... but it works, logs in, authenticates and reads the data

db.Initialize(Me,"db")
db.Connect("192.168.1.103","root","password")
  
Wait For db_Connected (Success As Boolean, Message As String)
If Success Then    db.executeQuery("SHOW DATABASES")

[CODE lang="b4x" title="A little piece of code"]Sub Class_Globals
Private host As String
Private port As Int
Private client As Socket
Private aStream As AsyncStreams

Private mCakkBack As Object
Private mEventName As String
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(CallBack As Object, EventName As String)
mCakkBack=CallBack
mEventName=EventName & "_"
port=3306
End Sub

Public Sub Connect(Address As String,username As String, password As String)
host=Address
user=username
pass=password
client.Initialize("client")
client.Connect(host,port,3000)
End Sub[/CODE]

 

[Star-Dust]

Given the many requests I have received to develop a version for iOS I imagined a more enthusiastic welcome from the forum.

I realized that I understand IT well but not people well ?

 

[Star-Dust]

Update.

Completed the MySql connector written entirely in B4X. Tried with all types and with different tables and databases. It seems to work correctly.
I just need to improve the resultSet to not keep the data in the ram memory as I receive it. But I have time to work on it...

 

[MrKim]

Work has started on the MySQL connector for iOS.... Anyone interested?

I don't need My SQL but I am very interested in MSSQL Server...

 

[Star-Dust]

I don't need My SQL but I am very interested in MSSQL Server...

It will be my next attempt. I hope to have as much luck.

 

[mikhatri]

Hi, how to get error on Connection and display in msgbox. already error show in log(). but i want to show it in msgbox.

[CODE lang="b4x" title="MYSQL Connection" highlight="Wait For MYSQL_Ready(Success As Boolean)"] MYSQL.Connect("org.mariadb.jdbc.Driver", _
$"jdbc:mariadb://${MyLocation}/bhairav_mall_db"$, _
DBUsername, _
DBPassword)

Wait For MYSQL_Ready(Success As Boolean)
If Success Then
Log("Cnnection Success")
Else
Log(LastException)
MsgboxAsync("Error in Connection. Please setup your host ip address properly.","")
End If[/CODE]

i want to "Wait For MYSQL_Error(Message As String)" somethink like this...

 

[Julio Quijas]

Hi, I am using a translator to ask my question, I am new and I hope I am doing the query correctly and thanks in advance for the help, I hope it is understood correctly. I have two functions that are called from Activity_Create(), the Cargar_Servicios() function and the Cargar_Mesas() function, each function makes a different query to the DB. When the Cargar_Servicios() function is being executed and it reaches the line wait for MYSQL_Ready(Success As Boolean) it jumps to the Cargar_Mesas() function without finishing the Cargar_Servicios() function, how can I make it wait and finish the Cargar_Servicios() function before starting the Cargar_Mesas() function?

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    
    If FirstTime Then
        formatter.Initialize
        Dim DefaultFormat As B4XFormatData = formatter.GetDefaultFormat
        DefaultFormat.MaximumFractions = 2
        DefaultFormat.MinimumFractions = 2
        DefaultFormat.Prefix = "$ "
        Dim NegativeFormat As B4XFormatData = formatter.CopyFormatData(DefaultFormat)
        NegativeFormat.TextColor = xui.Color_Red
        NegativeFormat.Prefix = "$ ("
        NegativeFormat.Postfix = ")"
        NegativeFormat.FormatFont = xui.CreateDefaultBoldFont(15)
        formatter.AddFormatData(NegativeFormat, formatter.MIN_VALUE, 0, False)
    End If
    
    phone1.SetScreenOrientation(0)
    Activity.LoadLayout("Principal")
    'PnlFondo.Visible=False
    
    pv=True
    
    scServicios.ChipPropertiesGlobal.xFont=xui.CreateDefaultBoldFont(18)
    scServicios.ChipPropertiesGlobal.Height=60
    scServicios.ChipPropertiesGlobal.TextGap=10
    
    slvMesas.ButtonsGridListVisible=False
    slvMesas.ButtonAddVisible=False
    slvMesas.ButtonExitVisible=False
    slvMesas.FilterVisible=False
    slvMesas.TurnToList
    
    Cargar_Servicios
    Cargar_Mesas(0)
End Sub

Sub Cargar_Servicios() As ResumableSub
    Dim MYSQL As SD_SQL
    MYSQL.Initialize(Me,"MYSQL")
    MYSQL.Connect("com.mysql.jdbc.Driver", $"jdbc:mysql://${Main.Configuracion_Red.serv}:${Main.Configuracion_Red.port}/${Main.Configuracion_Red.db}?useSSL=false"$,Main.Configuracion_Red.usr,Main.Configuracion_Red.pwd)
    wait for MYSQL_Ready(Success As Boolean)
    If Success=True Then
        If pv=True Then
            Dim RS As SD_ResultSet = MYSQL.ExecQuery("SELECT id,den,imga FROM v_usuarios_servicios WHERE dk=0 AND usrid=1")
            Do While RS.NextRow
                If (RS.GetBytes("imga")<>Null) Then
                    ImagenServicio(RS.GetBytes("imga"))
                Else
                    tmpimg=LoadBitmap(File.DirAssets,"vacioC.jpg")
                End If
                
                scServicios.AddChip(RS.GetString("den"),tmpimg,RS.GetString("id"))
            Loop   
            RS.Close
            
            'Dim RS As SD_ResultSet = MYSQL.ExecQuery("SELECT imga FROM t_cat_servicios WHERE dk=0 and id=" & scServicios.GetChip(0).Tag)
            'Do While RS.NextRow
            '    If (RS.GetBytes("imga")<>Null) Then
            '        ImagenServicio(RS.GetBytes("imga"))
            '    Else
            '        tmpimg=LoadBitmap(File.DirInternal,"vacioC.jpg")
            '    End If
            'Loop   
            'RS.Close
        End If
        'PnlFondo.Visible=True
    Else
        Msgbox("No se pudo conectar PRINCIPAL","FALLO")
    End If
    Return Null
End Sub

Sub Cargar_Mesas(ns As Int)
    Dim MYSQL2 As SD_SQL
    MYSQL2.Initialize(Me,"MYSQL")
    MYSQL2.Connect("com.mysql.jdbc.Driver", $"jdbc:mysql://${Main.Configuracion_Red.serv}:${Main.Configuracion_Red.port}/${Main.Configuracion_Red.db}?useSSL=false"$,Main.Configuracion_Red.usr,Main.Configuracion_Red.pwd)
    wait for MYSQL2_Ready (Success As Boolean)
    If Success=True Then
        slvMesas.ClearAll
        Dim RS As SD_ResultSet = MYSQL2.ExecQuery("SELECT * FROM v_mesa_activa WHERE idserv=" & scServicios.GetChip(ns).Tag)
        Do While RS.NextRow
            slvMesas.AddItem(RS.GetString("idmesa"),scServicios.GetChip(ns).Icon,RS.GetString("nomm") & " " & RS.GetString("idmesa"),RS.GetString("idcon"),RS.GetString("idconmesa"))
            'slvMesas.AddItemNoImage(RS.GetString("idmesa"),RS.GetString("nomm") & " " & RS.GetString("idmesa"),RS.GetString("idcon"),RS.GetString("idconmesa"))
        Loop
        RS.Close
        slvMesas.Invalidate
        If pv=True Then
            scServicios.SetSelections(Array As Int(0))
            pv=False
        End If
    End If
End Sub

 

[Star-Dust]

This question is not related to the functioning of the library, so you should ask in the main forum next time. Here you can ask questions related to the library's internal engine or its operation.

The answer to your question is related to the use of WAIT FOR. Below you will find the correct code. The correct steps are colored

[CODE lang="b4x" highlight="34, 71"]Sub Activity_Create(FirstTime As Boolean)
'Do not forget to load the layout file created with the visual designer. For example:

If FirstTime Then
formatter.Initialize
Dim DefaultFormat As B4XFormatData = formatter.GetDefaultFormat
DefaultFormat.MaximumFractions = 2
DefaultFormat.MinimumFractions = 2
DefaultFormat.Prefix = "$ "
Dim NegativeFormat As B4XFormatData = formatter.CopyFormatData(DefaultFormat)
NegativeFormat.TextColor = xui.Color_Red
NegativeFormat.Prefix = "$ ("
NegativeFormat.Postfix = ")"
NegativeFormat.FormatFont = xui.CreateDefaultBoldFont(15)
formatter.AddFormatData(NegativeFormat, formatter.MIN_VALUE, 0, False)
End If

phone1.SetScreenOrientation(0)
Activity.LoadLayout("Principal")
'PnlFondo.Visible=False

pv=True

scServicios.ChipPropertiesGlobal.xFont=xui.CreateDefaultBoldFont(18)
scServicios.ChipPropertiesGlobal.Height=60
scServicios.ChipPropertiesGlobal.TextGap=10

slvMesas.ButtonsGridListVisible=False
slvMesas.ButtonAddVisible=False
slvMesas.ButtonExitVisible=False
slvMesas.FilterVisible=False
slvMesas.TurnToList

WAIT FOR (Cargar_Servicios) COMPLETE (Success As Boolean)
Cargar_Mesas(0)
End Sub

Sub Cargar_Servicios() As ResumableSub
Dim MYSQL As SD_SQL
MYSQL.Initialize(Me,"MYSQL")
MYSQL.Connect("com.mysql.jdbc.Driver", $"jdbc:mysql://${Main.Configuracion_Red.serv}:${Main.Configuracion_Red.port}/${Main.Configuracion_Red.db}?useSSL=false"$,Main.Configuracion_Red.usr,Main.Configuracion_Red.pwd)
wait for MYSQL_Ready(Success As Boolean)
If Success=True Then
If pv=True Then
Dim RS As SD_ResultSet = MYSQL.ExecQuery("SELECT id,den,imga FROM v_usuarios_servicios WHERE dk=0 AND usrid=1")
Do While RS.NextRow
If (RS.GetBytes("imga")<>Null) Then
ImagenServicio(RS.GetBytes("imga"))
Else
tmpimg=LoadBitmap(File.DirAssets,"vacioC.jpg")
End If

scServicios.AddChip(RS.GetString("den"),tmpimg,RS.GetString("id"))
Loop
RS.Close

'Dim RS As SD_ResultSet = MYSQL.ExecQuery("SELECT imga FROM t_cat_servicios WHERE dk=0 and id=" & scServicios.GetChip(0).Tag)
'Do While RS.NextRow
' If (RS.GetBytes("imga")<>Null) Then
' ImagenServicio(RS.GetBytes("imga"))
' Else
' tmpimg=LoadBitmap(File.DirInternal,"vacioC.jpg")
' End If
'Loop
'RS.Close
End If
'PnlFondo.Visible=True
Else
Msgbox("No se pudo conectar PRINCIPAL","FALLO")
End If
Return True
End Sub

Sub Cargar_Mesas(ns As Int)
Dim MYSQL2 As SD_SQL
MYSQL2.Initialize(Me,"MYSQL")
MYSQL2.Connect("com.mysql.jdbc.Driver", $"jdbc:mysql://${Main.Configuracion_Red.serv}:${Main.Configuracion_Red.port}/${Main.Configuracion_Red.db}?useSSL=false"$,Main.Configuracion_Red.usr,Main.Configuracion_Red.pwd)
wait for MYSQL2_Ready (Success As Boolean)
If Success=True Then
slvMesas.ClearAll
Dim RS As SD_ResultSet = MYSQL2.ExecQuery("SELECT * FROM v_mesa_activa WHERE idserv=" & scServicios.GetChip(ns).Tag)
Do While RS.NextRow
slvMesas.AddItem(RS.GetString("idmesa"),scServicios.GetChip(ns).Icon,RS.GetString("nomm") & " " & RS.GetString("idmesa"),RS.GetString("idcon"),RS.GetString("idconmesa"))
'slvMesas.AddItemNoImage(RS.GetString("idmesa"),RS.GetString("nomm") & " " & RS.GetString("idmesa"),RS.GetString("idcon"),RS.GetString("idconmesa"))
Loop
RS.Close
slvMesas.Invalidate
If pv=True Then
scServicios.SetSelections(Array As Int(0))
pv=False
End If
End If
End Sub[/CODE]

 

[Mashiane]

Hi

Im trying this, however I get errors.

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

and

Not connected: java.sql.SQLException: No suitable driver found for jdbc:mysql:33068//179.x.x.x/dbname?useSSL=false

I'm using port 33068 for MySQL and, #AdditionalJar: mysql-connector-java-8.4.0.jar with mysql 8.

I would appreciate some advise. Thanks.

PS:

MYSQL.Connect("com.mysql.jdbc.Driver", $"jdbc:mysql:33068//${MyLocation}/${MyDataBaseName}?useSSL=false"$,  MyUsername, MyPassword)