[chargeable] MSMySQL - Yet another MySQL-Library (but a FAST one :-))

[Peter Simpson]

That's not bad at all @keirS.
Example of this library is as follows via my laptop using WiFi then connecting to my Scotland database server. I don't live in Scotland.

3 MySQL queries, querying 4 tables goes as follows.

Invoice details(invoice table) and cross reference contact details(contact table) = 2 tables
Invoice items(Invoice parts table) = 1 table
Category list(category table) = 1

Average time to retrieve the data shown below(3 queries) is around 230 ms(sometimes more, sometimes less). Please note that my database server is in Scotland, and I'm in the middle of England, so that's about 300 miles away from the server, the results are coming from over the internet. Also even though I didn't mention it in post #17, those results are coming from my server hosted on the Isles of man (data centre owned by Netcetera) which is over seas from my location

By the way my example database which is used in the above screen shot is 22 MB in size.

Average query time about 230 ms for 3 queries from 4 tables, the database is miles away from my location.

 

[DonManfred]

I´ve made a test with your library using the same Northwind-Database like in my Testproject getting the employees.
mariadb 355ms
msmysql: MySQL_QueryResult(Columns 19, Records 9,218ms,Employees

I have not tested to use the blobs

 

[keirS]

I´ve made a test with your library using the same Northwind-Database like in my Testproject getting the employees.
mariadb 355ms
msmysql: MySQL_QueryResult(Columns 19, Records 9,218ms,Employees

A couple of things. I wouldn't be testing on anything other than my ow LAN or my clients LAN. That's because my library doesn't support SSL so using it outside of that environment is not secure. Connections to the DB are either on the local LAN or via VPN so they are secure. Outside of that environment I use a REST API with SSL so it is secure. I assume your library doesn't use SSL either as I don't see any options to do with java.security.keystore or specifying a certificate to use. with your library.

As regards to speed. There is a fundamental difference between the two libraries in that you fetch the whole result set from the server in one go. That's fine for small result sets but try it on larger result sets. You will find at least 3 issues. The first issue is that your user will be waiting a long time for the results to start displaying. . Secondly you will have problems when there is a flaky internet/ WiFi connection because your connection will time out and you will have to run the whole query again. Thirdly for large result sets you app will crash with out of memory issues because of the limited heap size allocated to the app.

My library is written to overcome those issues. If I change the test program I posted earlier.

Sub Activity_Create(FirstTime As Boolean)
     If FirstTime Then 
            MySQLConnection.Initialize(MYSQLIP,MYSQLDBNAME,MYSQLUSER, MySQLPASS,MYSQLPORT)
    End If
    'ExecQuery all result values returned as strings
   
    catgCount = 0
    empsCount = 0
    catgTmStart  =DateTime.Now
    MySQLConnection.ExecQuery("catg","select * from categories")
    empsTmStart =  DateTime.Now
    MySQLConnection.ExecQuery("emps","select * from employees")
   

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub emps_update(emp As Map)
    empsCount = empsCount  + 1
   
    If empsCount = 1 Then
     Log("Employees start display In " & (DateTime.Now - empsTmStart) & "ms")
    End If 
   
   
End Sub


Sub emps_complete(finished As Boolean)
  Log("Employees: " & empsCount & " Records In " & (DateTime.Now - empsTmStart) & "ms")

End Sub


Sub catg_update(catg As Map)
     catgCount = catgCount + 1
   
    If catgCount = 1 Then
        Log("Categories start display In " & (DateTime.Now - empsTmStart) & "ms")
    End If
End Sub


Sub catg_complete(finished As Boolean)
   Log("Categories: " & catgCount & " Records In "  &  (DateTime.Now - catgTmStart) & "ms")
End Sub 
Sub query_error(trace As String)
  Log(trace)
End Sub

Sub catg_error( Trace As String )

   
End Sub

Sub emps_error( Trace As String )

   
End Sub

On my laptop I get

Categories start display In 65ms
Categories: 8 Records In 71ms
Employees start display In 71ms
Employees: 9 Records In 74ms

My library streams the the result set. This means the query is performed on the server and the result set is stored on the MySQL server. The library fetches the result set one record at a time from the server and passes it to the update routine. If the connection fails at any time some of the results. have been returned. It can be determined from the trace passed to the error sub that the connection has failed and a a new query can be generated to return the remaining results. It's not necessary to run the original query to return the whole result set again. It doesn't matter how big the result set is because only one record is being fetched at a time so the results can be cached to a file or more usually in my case a SQLite DB and can be reliably used to return result sets with millions of records. The down side of this is that if you have a high latency connection it will be considerably slower than your library because I am sending far more requests to the server and receiving far more replies . Since it wasn't designed for high latency connections because it doesn't support SSL so is not secure this has not been an issue.

 

[Peter Simpson]

I now believe that this person is actually trying to pick a fight with you and is deliberately looking to argue with you Manfred, just ignore them as I am. Your library does exactly what it says it will do and that's all that really matters. At least you answer questions about your libraries (thinking about it, you answer questions about other developers libraries too ). He or she has not even answered a simple question from December 5th about their own MySQL library, they have some nerve. Now that's ignorance to the maximum, considering how much interaction he or she is having with yourself about your library. They go around other MySQL library promoting their own library and then not answering questions about it, give it a rest.

It's now time to try a new 3D trick with my 450 heli, if I crash it I don't really care as it's only my practice heli and not my main carbon fibre heli

 

[BarryW]

How to insert blob (image) using this library? Tnx...

 

[Peter Simpson]

Hello @BarryW, I personally convert an images into a Base64 string then I save the data into the database field. When I want to view the Image in the database field, I just read the string out of the database field and then use Base64 to convert the string back into a viewable image again into an ImageView. You can use my base64 encode/decode image converter library to convert your images to a Base64 string and back to an image again. Here is my library https://www.b4x.com/android/forum/threads/base64-encode-decode-image-library.31031/#content.

Item image is being held in the database. Top right image below is about 5k in size...

 

[BarryW]

Is there any other way sir? This library can read a blob so maybe there is a way to insert it. But i dont know how to do. Your advice is good. Tnx

 

[Peter Simpson]

@BarryW all the libraries read BLOBS. I've just given you the way that I do it, it works perfect for me. You do not have to do it my way, good luck.
Maybe this library developer can help you out with your enquiry.

 

[DonManfred]

How to insert blob (image) using this library? Tnx...

This is actually not possible

But i found a maybe suiteable java code

public class InsertPictureToMySql {
  public static void main(String[] args) throws Exception, IOException, SQLException {
    Class.forName("org.gjt.mm.mysql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root");
    String INSERT_PICTURE = "insert into MyPictures(id, name, photo) values (?, ?, ?)";

    FileInputStream fis = null;
    PreparedStatement ps = null;
    try {
      conn.setAutoCommit(false);
      File file = new File("myPhoto.png");
      fis = new FileInputStream(file);
      ps = conn.prepareStatement(INSERT_PICTURE);
      ps.setString(1, "001");
      ps.setString(2, "name");
      ps.setBinaryStream(3, fis, (int) file.length());
      ps.executeUpdate();
      conn.commit();
    } finally {
      ps.close();
      fis.close();
    }
  }
}

I now need to figure out on how to build this into the library.
But i think this should be possible. Be patient please... Need some time i think

 

[keirS]

I now believe that this person is actually trying to pick a fight with you and is deliberately looking to argue with you Manfred, just ignore them as I am. Your library does exactly what it says it will do and that's all that really matters. At least you answer questions about your libraries (thinking about it, you answer questions about other developers libraries too ). He or she has not even answered a simple question from December 5th about their own MySQL library, they have some nerve. Now that's ignorance to the maximum, considering how much interaction he or she is having with yourself about your library. They go around other MySQL library promoting their own library and then not answering questions about it, give it a rest.

It's now time to try a new 3D trick with my 450 heli, if I crash it I don't really care as it's only my practice heli and not my main carbon fibre heli

Firstly I am not trying to pick fight and I have no interest in promoting my own library.If you had actually read the post about my library you would see it says I will not offer any support on it. Under it's licensing anyone is free to request the source code for the library.

All I was doing is giving a technical explanation for the speed differences and some of the problems that may be encountered when not using streaming. That's from personal experience of encountering all the issues I mentioned when not using streaming.

How else are developers supposed to know about these issues if no one tells them? I suspect most of the developers on here are one man bands and don't have the resources to do extensive testing in different environments. I would have thought the lack of SSL support would be a major concern to any developer because it limits the environments the library can be used in; unless they have no concern for the security of their own / clients data. I have certainly found it to be a problem and spent ages working out how to create VPN connections in Android and ensuring that devices would only connect via VPN as although we could get SSL working for MariaDB we couldn't get it working for MySQL.

 

[BarryW]

Hello @BarryW, I personally convert an images into a Base64 string then I save the data into the database field. When I want to view the Image in the database field, I just read the string out of the database field and then use Base64 to convert the string back into a viewable image again into an ImageView. You can use my base64 encode/decode image converter library to convert your images to a Base64 string and back to an image again. Here is my library https://www.b4x.com/android/forum/threads/base64-encode-decode-image-library.31031/#content.

Item image is being held in the database. Top right image below is about 5k in size...

@BarryW all the libraries read BLOBS. I've just given you the way that I do it, it works perfect for me. You do not have to do it my way, good luck.
Maybe this library developer can help you out with your enquiry.

Your library is only converting from files to base64. How to convert from imageview or canvas to base64?

 

[Peter Simpson]

@BarryW it works both ways, image must be a bitmap. Convert imageview or canvas to bitmap then to Base64 string, save to database. Please ask questions on that library post, thank you.

Instructions:
In Globals add the following line
Dim Base64Con As Base64Image

To get an encoded base64 data stream, and return it as an bitmap ready to load into an image view for example, use the following line.
Base64Con.DecodeToImage(ImgStrAsString) AsBitmap

Or

To get an image file, and encode it into an base64 data stream, ready to save into a database or file for example, use the following line.
Base64Con.EncodeFromImage(FolderPathAsString, FilenameAsString) AsString

 

[BarryW]

How to check the connection in Android not in B4a Logs . . .

 

[Peter Simpson]

How to check the connection in Android not in B4a Logs . . .

You need to select Network in the library tab.

Dim  Server As ServerSocket
     Server.Initialize(0, Null)

If Server.GetMyIP = "127.0.0.1" Then 'Test for internet connection
     'There is no internet
Else
     'There is internet
End If

 

[BarryW]

You need to select Network in the library tab.

Dim  Server As ServerSocket
     Server.Initialize(0, Null)

If Server.GetMyIP = "127.0.0.1" Then 'Test for internet connection
     'There is no internet
Else
     'There is internet
End If

This is for the connection on the wifi / internet... I mean how to test the connection on the MySql Database not on b4a logs but on the android it self.

 

[hotspring]

I have a problem. No method will raise its events and so the sub is not called.

Sub Activity_Create(FirstTime As Boolean)
'Do not forget to load the layout file created with the visual designer. For example:
Activity.LoadLayout("User")

db.Initialize("sql",Main.MYSQLIP,Main.MYSQLUSER,Main.MySQLPASS,Main.MYSQLDBNAME)
db.QueryASync2("select * FROM PEOPLE;")
End Sub


Sub MySQL_QueryResult2(data As List, meta As Map)
Log("MySQL_QueryResult2("&meta&")")
For i=0 To data.Size-1
Log("r:"&data.Get(i))
Next
End Sub

 

[Peter Simpson]

Hello @hotspring
Please remember to encapsulate you code between [ Code] and [ /Code] but without the space.

Please change the following line.
From:

Sub MySQL_QueryResult2(data As List, meta As Map)

To:

Sub sql_QueryResult2(data As List, meta As Map)

Change from 'MySQL' to 'sql' for all your subs. You Initialize "sql" as the Event name...

 

[hotspring]

THX This was my error

 

[DonManfred]

THX This was my error

Can you please tell me your name under you register the lib? I get no reference to "hotspring" in my registrations!?

 

[Jaames]

Can you please tell me your name under you register the lib? I get no reference to "hotspring" in my registrations!?

It would be stupid that someone shared or decompiled your lib... it's a recipe for losing many of great library coders here ...