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

Anser · Mar 10, 2015

Things not moving as expected and time running out so needed a push in the right direction and that's the reason for my query in the previous post.

Thanks.

Regards
Anser

Peter Simpson · Mar 10, 2015

Hello @answer,
I can't really help with your previous question. I believe that the MySQL server will automatically close the connection if there is no activity with that said connection in a given time. But I personally do use the following sub to capture if the user has tried to get out of apps by pressing the back key.

B4X:

'Soft back key pressed
Sub Activity_KeyPress (KeyCode As Int) As Boolean 'Return True to consume the event
    Dim KillApp As Int

    If KeyCode = KeyCodes.KEYCODE_BACK Then
        KillApp = Msgbox2("Exit Simply Invoice AE?" ,"Exit?", "Yes", "", "No", Null)
       
        If KillApp = DialogResponse.POSITIVE Then
            MySQL.CloseDatabase
            ExitApplication
            Return False
        Else
            Return True
        End If
    End If   
   
    Return True
End Sub

Anser · Mar 10, 2015

I was also thinking in the same way ie capturing user exit from via Sub Activity_KeyPress

Wanted a confirmation from the experts

Peter Simpson said:
Hello @answer,
I believe that the MySQL server will automatically close the connection if there is no activity with that said connection in a given time.

So how do you ensure that the connectivity with the Database server is active or not before you do some operations on the database ?

Anser said:
My app will have multiple activities. Do I need to create a new Database object in each activity ? While using RDC I was using different DBRequestManager objects for each activity. I did so because, the JobDone was in one of the activities and calling RDC query from another activity had some unexpected results. As I am a newbie I haven't tried using a service module to cater to each activities Database manipulation requests. Even now, I don't know how to handle this with a service module and avoid using multiple instances of the connection object to the same database.

I could not locate any apt sample of using database operation via a service module. So for the time being I will have to use different database connection objects on each activity. When I move from one Activity to other activity, I shall close the database connection of that activity and open a new connection in the new activity. I know it is not an elegant solution. When time permits, and once I have learned the tricks, I shall rewrite the app to use service modules.

Regards
Anser

Peter Simpson · Mar 10, 2015

@Anser use MySQL.check_connection to check the connection to the database first, then run your database query.

B4X:

'Some sub of yours
Sub Test_Connection
   MySQL.check_connection
End Sub

Sub Query_Status(Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)
   If Connected = False then MySQL.Initialize("Query", DBHost, DBUser, UsrPW, DBName)
End Sub

P.S. I'm no expert, nowhere near...

Anser · Mar 10, 2015

May be I am asking a stupid question.

I did not understand the code given in your previous post.
When is this Sub Query_status executed ?

From the Sub Test_Connection you are calling a Function/Method MySQL.check_connection that belong to the LIB. Right ?

Will MySQL.check_connection subsequently raise the event/sub Query_Status ?

Or is it a typo error and the name of the sub is supposed to be

Sub MySQL_Query_Status ie the Sub NameGivenToConnection_Query_Status

I am really sorry to bother you too much, but it is really confusing.

DonManfred · Mar 11, 2015

Basically you should work like this:

- Initialize on Activity_create
- Checkconnection on Activity_Resume
- CloseDatabase on Activity_Pause
If you are working with multiple activities then this apply to all activities.

If you are then working a longer time in one activity; maybe with a longer timerout (>5 min) then - in background - the connection will be closed from the server. If you then do another call then the lib will get an connection lost and will try to reconnect and then executing your query... The first query after such time out period can be last longer cause the lib first needs to get the connectionlost and to reconnect.

BarryW · Mar 11, 2015

Why i cant connect to online MySQL using this library? Is this for local Connection only?

DonManfred · Mar 11, 2015

BarryW said:
Why i cant connect to online MySQL using this library?

Not all Providers allow Access to Databases from "Outside". You need to ask your Provider if they allow remote Access to the DB.

BarryW said:
Is this for local Connection only?

No. The Lib CAN connect to any MySQL Database WHEN THE DATABASE IS ACCESSIBLE FROM OUTSIDE. I´´m using the Lib with my Databases hosted on my Webside but i´m allowed to do so.

Peter Simpson · Mar 11, 2015

If you MySQL provider supplies you cPanel to control your databases then you have to login, goto database management tools and add a wildcard entry for all databases.

@DonManfred is correct though. For some strange unknown reason, some database providers will not allow external access, this is rare but I know that @wonder came across this same issue with a supplier about a week or so ago.

DonManfred · Mar 11, 2015

benji said:
i'm executing batch with MSMySQL, but i haven't a good result. I lost the first and de the last record, but this records are in the list.

You are right. There was an error in both msMySQL and msMariaDB libs. Will update new version in a few minutes on my Dropbox folder

Anser · Mar 12, 2015

What is the difference between check_connection and check_connection2 ?

Which one should we use ?

DonManfred · Mar 12, 2015

the 1st one. If you encounter any problems with it you can try the other one.
Usually the check_connection should be ok.

Anser · Mar 12, 2015

I get the following error at Activity_Resume when the app is opened for the second time.

B4X:

** Activity (main) Create, isFirst = true **
Raising.. anser_status
Connected to Database
** Activity (main) Resume **
Reached CheckLogin Sub. About to execute Login Query
lib:queryasync()
Reached QueryResult Sub. About to execute Login Query
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = false **
** Activity (main) Resume **

MySQL Database not connected!
ReConnecting
java.lang.NullPointerException

   at de.donmanfred.b4a.MariaDB.auto_reconnect(MariaDB.java:1092)
   at de.donmanfred.b4a.MariaDB.check_connection(MariaDB.java:1137)
   at com.popularjcb.emptool.main._activity_resume(main.java:408)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:515)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
   at com.popularjcb.emptool.main.afterFirstLayout(main.java:106)
   at com.popularjcb.emptool.main.access$100(main.java:17)
   at com.popularjcb.emptool.main$WaitForLayout.run(main.java:78)
   at android.os.Handler.handleCallback(Handler.java:733)
   at android.os.Handler.dispatchMessage(Handler.java:95)
   at android.os.Looper.loop(Looper.java:136)
   at android.app.ActivityThread.main(ActivityThread.java:5017)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:515)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
   at dalvik.system.NativeStart.main(Native Method)
java.lang.NullPointerException

The code that I use is given below. It fails at Activity_Resume when I quit the app and open it for the second time. Not sure whether this is the right way to use the lib.

B4X:

Sub Process_Globals

  Dim cDbIP As String :cDbIP="192.168.x.xxx"
  Dim cDataBase As String :cDataBase="MyDbName"
  Dim cDbUserName As String :cDbUserName="MyUserName"
  Dim cDbPassword As String :cDbPassword="MyPassword"

End Sub

Sub Globals
  Private oConnection As MSMaria
End Sub

Sub Activity_Create(FirstTime As Boolean)
  If FirstTime Then
    Try
      oConnection.Initialize("anser",cDbIP,cDbUserName,cDbPassword,cDataBase)
    Catch
      Msgbox("Unable to connect to the server","Server unavailable")
    End Try
  End If
End Sub

Sub Activity_Resume
  oConnection.check_connection
  'I tried the following line too, unfortunately no difference
  'oConnection.check_connection2
End Sub

Sub Activity_Pause (UserClosed As Boolean)
  oConnection.CloseDatabase
End Sub

Sub anser_Status(Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)

  If Connected = False Then
    ToastMessageShow("Connection was Lost. Re-establishing connection now", True)
    oConnection.Initialize("anser",cDbIP,cDbUserName,cDbPassword,cDataBase)
  End If
End Sub

Sub CheckLogin(cUserName As String, cPassword As String)
  Dim cSql As String
  cSql = "SELECT user_id,username FROM users WHERE username = '"&cUserName&"' AND password = '"&cPassword&"';"
  Log( "Reached CheckLogin Sub. About to execute Login Query")
  oConnection.QueryASync(cSql,"LoginQuery")
End Sub

Sub anser_QueryResult(Data As List, Meta As Map)
  Log( "Reached QueryResult Sub. About to execute Login Query")

  ' Login Process
  If Meta.Get("TaskID") = "LoginQuery" Then
     .....
  End If
End Sub

I was trying to follow your advice

DonManfred said:
Basically you should work like this:

- Initialize on Activity_create
- Checkconnection on Activity_Resume
- CloseDatabase on Activity_Pause
If you are working with multiple activities then this apply to all activities.

If you are then working a longer time in one activity; maybe with a longer timerout (>5 min) then - in background - the connection will be closed from the server. If you then do another call then the lib will get an connection lost and will try to reconnect and then executing your query... The first query after such time out period can be last longer cause the lib first needs to get the connectionlost and to reconnect.

Anser · Mar 12, 2015

I think that I understood why it is failing.
Changed the following code

B4X:

Sub Activity_Create(FirstTime As Boolean)
  If FirstTime Then
    Try
      oConnection.Initialize("anser",cDbIP,cDbUserName,cDbPassword,cDataBase)
    Catch
      Msgbox("Unable to connect to the server","Server unavailable")
    End Try
  End If
End Sub

To

B4X:

Sub Activity_Create(FirstTime As Boolean)
      oConnection.Initialize("anser",cDbIP,cDbUserName,cDbPassword,cDataBase)
End Sub

Can you please confirm whether this is the right way of coding ?

Even though, I removed the TRY CATCH and the meaningful message to the user,
I am still puzzled on how to tell the user that it did not connect to the database because Internet is not available etc etc

Regards
Anser

Anser · Mar 12, 2015

If my database runs on a different port (other than the regular port 3306 ), then how do I specify the MySQL Database port using this lib? .

Regards
Anser

DonManfred · Mar 12, 2015

Anser said:
f my database runs on a different port (other than the regular port 3306 ), then how do I specify the MySQL Database port using this lib?

Have you tried to add a ":[portno]" to the hostname?
host: testserver
->
Host: testserver:1234

Anser · Mar 12, 2015

No. Shall try that

Anser · Mar 13, 2015

DonManfred said:
Have you tried to add a ":[portno]" to the hostname?
host: testserver
->
Host: testserver:1234

Yes. Providing port number along with domain name serves the purpose. It is working

Anser · Mar 13, 2015

If I stay inactive on an activity for a long time and then if I try to execute some database routine, then I am getting the following run time error.
How to trap the error OR how to avoid such error.

B4X:

** Activity (main) Create, isFirst = true **
Raising.. anser_status

Connected to Database

** Activity (main) Resume **

lib:executeasync()
lib:queryasync()
Reached QueryResult Sub. About to execute Login Query
Reached CheckLogin Sub. About to execute Login Query
lib:queryasync()
Reached CheckLogin Sub. About to execute Login Query
lib:queryasync()
Reached CheckLogin Sub. About to execute Login Query
lib:queryasync()
Reached CheckLogin Sub. About to execute Login Query
lib:queryasync()


SqlException: java.sql.SQLNonTransientConnectionException: Could not read resultset: unexpected end of stream, read 0bytes from 4
SqlException: java.sql.SQLNonTransientConnectionException: Could not read resultset: unexpected end of stream, read 0bytes from 4
SqlException: java.sql.SQLNonTransientConnectionException: Could not send query: sendto failed: EPIPE (Broken pipe)
SqlException: java.sql.SQLNonTransientConnectionException: Could not read resultset: unexpected end of stream, read 0bytes from 4
Reached QueryResult Sub. About to execute Login Query

main_anser_queryresult (java line: 421)

java.lang.NullPointerException
    at com.popularjcb.emptool.main._anser_queryresult(main.java:421)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
~e:    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)


    at anywheresoftware.b4a.BA$3.run(BA.java:332)
    at android.os.Handler.handleCallback(Handler.java:733)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:136)
    at android.app.ActivityThread.main(ActivityThread.java:5017)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
    at dalvik.system.NativeStart.main(Native Method)
java.lang.NullPointerException

jahswant · Mar 16, 2015

I just donated i'm expecting a link to download the lib.

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

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Active Member

Expert

Expert

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member