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

Star-Dust · Aug 22, 2020

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:

B4X:

<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 · Oct 3, 2024

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

B4X:

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")

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

Star-Dust · Oct 4, 2024

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 · Oct 6, 2024

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 · Oct 10, 2024

Star-Dust said:
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 · Oct 10, 2024

MrKim said:
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.

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

Attachments

Star-Dust

Expert

Star-Dust

Expert

Star-Dust

Expert

MrKim

Well-Known Member

Star-Dust

Expert

Similar Threads

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

Attachments

Star-Dust

Expert

Star-Dust

Expert

Star-Dust

Expert

MrKim

Well-Known Member

Star-Dust

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency