B4J Library [B4X] [B4XLib] SD_SQL (direct access to MySQL, 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

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​
 

Attachments

  • jSampleSQL.zip
    2.5 KB · Views: 289
  • aSampleSql.zip
    9.7 KB · Views: 313
  • SD_SQL.b4xlib
    2.5 KB · Views: 348
Last edited:

Star-Dust

Expert
Licensed User
😹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

Member
Licensed User
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.
 
Last edited:

DonManfred

Expert
Licensed User
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.

codetag001.png

codetag002.png

codetag003.png
 

Star-Dust

Expert
Licensed User
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

Well-Known Member
Licensed User
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

Expert
Licensed User
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
 
Top