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​

 

[Jorge Santamaria Magana]

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.

Any suggestions for this? Anyone who can guide me?

 

[Star-Dust]

Hi Star-Dust, here the log:

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

I believe the error indicates that the server did not respond to that address / port.

If you do a Google search you see that it is often generated by the firewall or by wrong parameters. I can't help you much as you have to do some tests locally to understand the problem

 

[Alex.G]

I believe the error indicates that the server did not respond to that address / port.

If you do a Google search you see that it is often generated by the firewall or by wrong parameters. I can't help you much as you have to do some tests locally to understand the problem

Why in debug mode it runs ok?

 

[Jorge Santamaria Magana]

with function ExecNonQuery

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

FBSQL.ExecNonQuery(SQL)

Thanks Alex

 

[Star-Dust]

Why in debug mode it runs ok?

I can't give you answers on everything, I don't have enough information I guess it could be a permissions issue.
The error indicates that there is no response from the server because it cannot be reached.

What version of Android do you have?

 

[Alex.G]

I can't give you answers on everything, I don't have enough information I guess it could be a permissions issue.
The error indicates that there is no response from the server because it cannot be reached.

What version of Android do you have?

Thanks Star-Dust, I'm trying and retrying 'cause I've to understand and resolve this problem.

Tested on Android 6, 7 and 11, same result: Debug mode OK, Release mode connection error.
What's the difference between these two mode?
No one had matter this problem?

I can try with an android emulator on Windows, can you advise me one?

 

[Erel]

Tested on Android 6, 7 and 11, same result: Debug mode OK, Release mode connection error.

Try to disable this check: Workaround the NetworkOnMainThread exception
It is only applied in release mode.

 

[Alex.G]

Try to disable this check: Workaround the NetworkOnMainThread exception
It is only applied in release mode.

Hi Erel,
thanks for trick.

Problem solved adding these two rows to Manifest file:

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

Let's hope this can help someone like me.

Now I want to try Erel's Library jRDC2

 

[Star-Dust]

I can't give you answers on everything, I don't have enough information I guess it could be a permissions issue.

As I wrote it could be a permission issue

 

[MrKim]

(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).

I don't know if you are still planning to do B4i, but if you are, I found this:

GitHub - martinrybak/SQLClient: Native Microsoft SQL Server client for iOS

Native Microsoft SQL Server client for iOS. Contribute to martinrybak/SQLClient development by creating an account on GitHub.

github.com

It would be great, then we would have a complete B4x solution for directly connecting to SQL server DBs.

 

[Star-Dust]

It was planned, but I failed several attempts.

Thanks for pointing me to a possible solution. I'll work on it as soon as possible, but I'm not sure I know enough objective-c to be able to port this code to B4x

 

[MrKim]

It was planned, but I failed several attempts.

Thanks for pointing me to a possible solution. I'll work on it as soon as possible, but I'm not sure I know enough objective-c to be able to port this code to B4x

It would be , then we would have a complete B4X solution. I have written a class that can use either direct or jrdc - pass the same request. Then I could complete the circle.

 

[Star-Dust]

Update 0.07

• Added the connect method
• Added the connected field (status)
• Examples updated

To avoid confusion the initialize command is split from the connect command. Some people confused the isInitilized field with isConnected. In addition, a Connected field has been added that allows you to know the status, each connection is equally raised an event that returns the status..

 

[thegremlin]

Hy,
maybe you can help me. When I do this query at SQL Studio ill get one result.
When I try to read this at B4A Ill get a error...

Dim query As String
query = MSSQL.ExecQuery("SELECT fVKNetto FROM dbo.tArtikel WHERE cBarcode='4019111013805' or cArtNr='4019111013805'")
Netto.Text = query
Log(query)

This error I get:

[main=null, nativers=(JtdsResultSet) net.sourceforge.jtds.jdbc.JtdsResultSet@11296b7f, starter=null

Thanks

Steven

 

[DonManfred]

Hy,
maybe you can help me. When I do this query at SQL Studio ill get one result.

1. You are posting to an old thread which is a mistake.
2. You are NOT using the Resultset correctly.

Create a new thread in the questionsforum for any Issue you have.

 

[Star-Dust]

The result of a query must be contained in a ResultSet. Only ExecQuerySingleResult could possibly be contained in a string, but this method has not been included in this library.

 

[amorosik]

1. You are posting to an old thread which is a mistake.
2. You are NOT using the Resultset correctly.

Create a new thread in the questionsforum for any Issue you have.

The question is directed connected with the post argument
If someone were to seek answers to problems relating to the initial post, they would easily find an answer by scrolling through the 3d
Why you wrote "is a mistake" ?

 

[vecino]

Hi, I am trying to display an image that is stored in a mysql blob field and I get this error:

(RuntimeException) java.lang.RuntimeException: Class instance was not initialized (sd_sql)

What am I doing wrong?
The text and numeric fields are displaying fine.
Thank you very much.

    Dim cDirImage As String = File.DirInternal
    Dim cFileImage As String = "tempimage.jpg"

    Dim cSql As String = $"select jpgimg from ${MyTable} where id=${iIDregistroActual}"$

    Dim RS As SD_ResultSet = MYSQL.ExecQuery(cSql)      

    Dim img() As Byte = RS.GetBytes("JPGIMG")   '  ERROR on this line. !!!!!!!!!!

    If img.Length>0 Then
        Dim tempimagepath As String = File.Combine(cDirImage,cFileImage)
        File.WriteBytes(cDirImage,cFileImage,img)
        Dim html As String
        html = "<html><body><img src='file://" & tempimagepath & "' /></body></html>"
        Dim webview As WebView = Web
        webview.LoadHtml(html)  
    End If

 

[vecino]

Sorry, I copied the error line wrong, it is this one:

Error occurred on line: 103 (SD_ResultSet)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4j.object.JavaObject.RunMethod(JavaObject.java:132)
at visor.errores.iofwin.sd_resultset._getbytes(sd_resultset.java:50)
at java.lang.reflect.Method.invoke(Native Method)
...

Surely it is better to create a new thread for more people to see.
Thank you very much.

 

[Star-Dust]

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