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 · Jan 1, 2021

K_Kai said:
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:

Code:

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

and HOW I modify my db
like:

Code:

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

can you HELP me？
thank YOU very much！

Use SQL commands, in this case INSERT INTO

swamisantosh · Jan 1, 2021

Star-Dust said:
Make sure you used the correct mar 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

sorry for HUGE FONT...

Thanks you very much the issue is resolved.

Dim MSSQL As SD_SQL
Private MSLocation As String = "192.168.43.191/global;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}"$, MSUsername, MSPassword)

may it will benefit other.

DonManfred · Jan 2, 2021

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

Please use [CODE]code here...[/CODE] tags when posting code.

Mashiane · Mar 9, 2021

Hi, can you also include an example for other backends for example SQLite or it will work with the current implementation?

Thanks

Star-Dust · Mar 9, 2021

Mashiane said:
Hi, can you also include an example for other backends for example SQLite or it will work with the current implementation?

Thanks

It is exclusive to MS SQL, MySQL

asales · Mar 15, 2021

Star-Dust said:
It is exclusive to MS SQL, MySQL

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

B4X:

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

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

Star-Dust · Mar 15, 2021

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

B4X:

Dim FBSQL As SD_SQL Private FBUsername As String = "SYSDBA" Private FBPassword As String = "masterkey" FBSQL.Initialize(Me,"FBSQL", "org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:127.0.0.1/3050:" & "E:\Projetos\Firebird\B4X.FDB", FBUsername, FBPassword)

Do my libraries also work for purposes I didn't design them for? Good to know

Peter Simpson · Apr 8, 2021

Hey Salvatore,
Your title should actually say MySQL/MariaDB

Cheers...

Luca1967 · Nov 6, 2021

The ROWCOUNT is missing.
How do I know if the SELECT has not returned anything to me?

MicroDrie · Nov 6, 2021

You can try it on two ways. The result of the first routine has count the row in the table and the second routine gives the row count based on a element in the table.

Test for table and table row count:

'    --- Tests whether the given table exists
Public Sub TableExists(SQL As SQL, TableName As String) As Boolean
    Dim count As Int = SQL.ExecQuerySingleResult2("SELECT count(name) FROM sqlite_master WHERE type='table' AND name=? COLLATE NOCASE", Array As String(TableName))
    Return count > 0
End Sub

'    --- Get table row count
Public Sub getTableExists(SQL As SQL, TableName As String) As Int
    Dim Length As Int = SQL As SQL.ExecQuerySingleResult($"SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='${TableName}'"$)
    Log($"${TableName} Length = ${Length}"$)
    Return Length
End Sub

Star-Dust · Nov 6, 2021

Luca1967 said:
The ROWCOUNT is missing.
How do I know if the SELECT has not returned anything to me?

There is no RowCount in the Java ResultSet. The only way is to count the lines by spelling them or with a Query that counts the results as @MicroDrie suggested.

Mashiane · Apr 20, 2022

@Star-Dust , this I assume does not use connection pools or does it? Just curious in terms of it handle a large number of concurrent users.

Star-Dust · Apr 20, 2022

Mashiane said:
@Star-Dust , this I assume does not use connection pools or does it? Just curious in terms of it handle a large number of concurrent users.

It's just a connector that uses jar drivers.
I believe the pool is about the server

amorosik · May 26, 2022

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

B4X:

Dim FBSQL As SD_SQL Private FBUsername As String = "SYSDBA" Private FBPassword As String = "masterkey" FBSQL.Initialize(Me,"FBSQL", "org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:127.0.0.1/3050:" & "E:\Projetos\Firebird\B4X.FDB", FBUsername, FBPassword)

For B4J also jaybird-full-4.0.0.java8.jar is ok

Jorge Santamaria Magana · Oct 19, 2022

Hello first of all thank you very much for this contribution I am amazed with the way of connection to the database especially MySQL and Firebird, but how can we use the statements for INSERT, UPDATE, DELETE? I would greatly appreciate a contribution on this.

Alex.G · Oct 19, 2022

Hi guys,
I'm Alex from Italy.
I'm a Delphi, C++ and Java coder and two months ago I discovered this wonderful community. I am fascinated by the enormous potential of the B4X environment and I thank Erel for what he has done and what he does.
I've no a jRDC server (for now), so I tryed this nice library.

I've a connection problem with SD_SQL.

If I compile in debug mode all runs OK.
If I compile in release mode I receve connection error:

Not connected: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544721. Unable to complete network request to host "192.168.0.99".

Where am I doing wrong?

B4X:

'In Main module:

'FirebirdSQL Connector/J Driver
#AdditionalJar: Jaybird_2_2_12.aar

'In B4XMainPage:

Private Sub B4XPage_Created (Root1 As B4XView)
    Dim FBSQL      As SD_SQL
    Dim FBUsername As String = <my username>
    Dim FBPassword As String = <my password>
    Dim FBDriver   As String = "org.firebirdsql.jdbc.FBDriver"
    Dim FBPath As String = "jdbc:firebirdsql:192.168.0.99/3050:C:\winwork\Cerbero\DB\CER00014.GDB"

    Try
        Main.FBSQL.Initialize(Me,"FBSQL", FBDriver, FBPath, FBUsername, FBPassword)
    Catch
        MsgboxAsync("LastException: "&LastException, "")
    End Try

End Sub

Sub FBSQL_Ready (Success As Boolean)

    If Success Then
        Log("OK")
    Else
        Log("...KO")
    End If
    
End Sub

Alex.G · Oct 19, 2022

Jorge Santamaria Magana said:
Hello first of all thank you very much for this contribution I am amazed with the way of connection to the database especially MySQL and Firebird, but how can we use the statements for INSERT, UPDATE, DELETE? I would greatly appreciate a contribution on this.

with function ExecNonQuery

B4X:

Dim SQL as String = $"UPDATE <table name> SET <field1> = '${<string1>}', <field2> = '${<string2>}' WHERE <field3> = '${<string3>}'"$

FBSQL.ExecNonQuery(SQL)

Star-Dust · Oct 19, 2022

Alex.G said:
Hi guys,
I'm Alex from Italy.
I'm a Delphi, C++ and Java coder and two months ago I discovered this wonderful community. I am fascinated by the enormous potential of the B4X environment and I thank Erel for what he has done and what he does.
I've no a jRDC server (for now), so I tryed this nice library.

I've a connection problem with SD_SQL.

If I compile in debug mode all runs OK.
If I compile in release mode I receve connection error:

Not connected: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544721. Unable to complete network request to host "192.168.0.99".

Where am I doing wrong?

B4X:

'In Main module: 'FirebirdSQL Connector/J Driver #AdditionalJar: Jaybird_2_2_12.aar 'In B4XMainPage: Private Sub B4XPage_Created (Root1 As B4XView) Dim FBSQL As SD_SQL Dim FBUsername As String = <my username> Dim FBPassword As String = <my password> Dim FBDriver As String = "org.firebirdsql.jdbc.FBDriver" Dim FBPath As String = "jdbc:firebirdsql:192.168.0.99/3050:C:\winwork\Cerbero\DB\CER00014.GDB" Try Main.FBSQL.Initialize(Me,"FBSQL", FBDriver, FBPath, FBUsername, FBPassword) Catch MsgboxAsync("LastException: "&LastException, "") End Try End Sub Sub FBSQL_Ready (Success As Boolean) If Success Then Log("OK") Else Log("...KO") End If End Sub

What kind of error do you get? Did you make a log?

Alex.G · Oct 19, 2022

Star-Dust said:
What kind of error do you get? Did you make a log?

Hi Star-Dust, here the log:

B4X:

Logger connesso a: 57U7N17915028375
--------- beginning of crash
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **
Not connected: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544721. Unable to complete network request to host "192.168.0.99".

Alex.G · Oct 19, 2022

correction at may post #36
at row 16 (after Try)

B4X:

FBSQL.Initialize(Me,"FBSQL", FBDriver, FBPath, FBUsername, FBPassword)

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

Attachments

Expert

Member

Expert

Expert

Expert

Expert

Expert

Expert

Active Member

Well-Known Member

Expert

Expert

Expert

Expert

Member

Member

Member

Expert

Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency