-
Welcome to B4X forum!
B4X is a set of simple and powerful cross platform RAD tools:
- B4A (free) - Android development
- B4J (free) - Desktop and Server development
- B4i - iOS development
- B4R (free) - Arduino, ESP8266 and ESP32 development
[chargeable] MSMySQL - Yet another MySQL-Library (but a FAST one :-))
- Thread starter DonManfred
- Start date
- Similar Threads Similar Threads
You can use the batch. For each command in the given batch you´ll get an entry in a result-list.
Thanks, will check that out and revert back to you, Ta!
Don't worry, checked it, it Replace works however the batch results are 2 records per entry because Replace firsts deletes the record if there is a matching primarykey and then inserts a new record.
Hi
How do you handle the "MySQL" events in multiple form apps where you have different crud functionality for different forms where you have to trap results of queries/batches? Do you have to initialize the connection to create the "MySQL" events per form like below?
db.Initialize("MySQL",hst,usr,pwd,dbN)
How do you handle the "MySQL" events in multiple form apps where you have different crud functionality for different forms where you have to trap results of queries/batches? Do you have to initialize the connection to create the "MySQL" events per form like below?
db.Initialize("MySQL",hst,usr,pwd,dbN)
Method with a parameter TASK can be used. Set the task to a string you can remember when getting the result. The TASK will be submitted to the event sub inside the metadata
you can see TASK as kind of "Tag" you know from objects.
you can see TASK as kind of "Tag" you know from objects.
Hi Don
I'm sorry if I seem to be taking your time, perhaps what I would like to see is a MSMySQL example that has multiple forms with some CRUD functionality in them to understand how everything works / is supposed to work. For now this is what I have done...
1. I have a Public db as MySQL declared in a code module to be accessible anywhere in my app.
2. I initialize db in my Main activity module with "MySQL" event like this db.Initialize("MySQL",hst,usr,pwd,dbN)
3. The Main activity starts another activity after a timer hides its Splash screen, frmLogin. In frmLogin a user is able to enter an email and password.
4. After entering an email and password, I run a query to read a users MySQL table that stores users to validate the entered details, I run for example,
db.QueryASync(iQry, "validateUser"), iQry being a select command for the username and password.
5. Whilst I have not initialized MySQL anywhere in the frmLogin activity module, I have Sub MySQL_QueryResult(data As List, meta As Map) in it, reading both data and the meta details, getting the record count to find how many returned records are there. Whilst this is working perfectly, is it the way that this has to be done? The only time that the MySQL event is initialized is the Main Activity module and the rest of my forms have MySQL_ events in them.
Is this the correct approach for this?
it does not sound bad! If it is working then you are fine... No need to change anything... Your approach sounds good for me.
And well designed i must say (using eventsubs in your activitys where you do requests from the db)
I did donate this friday Agust 21 2015. Please send de link to download library connect Mysql. Thanks
This Library can be used to connect your Device to a MySQL database. The Library use a direct connection to the MySQL databaseserver.
Requisites: The Database must be accessible from "outside"
DisAdvantages: Due to the app needs the Databasecredentials (including username and password). There Credentials must be included in your App. I suggest using this Library only for private or company intern use.
MSMySQL
Version: 1.06
- MySQL
Events:Methods:
- BatchResult (batch As Map)
- ExecResult (meta As Map)
- ListTables (tables As List, ms as Long As )
- QueryResult (data as List As , meta As Map)
- QueryResult2 (data as List As , meta As Map)
- Status (Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)
Properties:
- CloseDatabase
Closes the database
Example:<code>
db.closedatabase
</code>- DisableReconnect
Disable automatic auto_reconnect if the MySQL Database Connection is lost
By default this is enabled- EnableReconnect
Enable automatic auto_reconnect if the MySQL Database Connection is lost
By default this is enabled.- ExecuteASync (query As String, Task As String)
executes ONE SQL-Commands (insert, update, delete)
Example:<code>
db.executeasync("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
</code>- ExecuteBatchASync (batch As List, Task As String)
executes a batch of SQL-Commands (insert, update, delete)
Example:<code>
Dim batch As List
For i=1 To 100
batch.Add("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
Next
db.executebatchasync(batch)
</code>- ExecutePeparedStatement
- Initialize (event As String, host As String, user As String, password As String, Database As String)
Initialize the Library
the url to your database. You dont need to prefix it with
jdbc:mysql:// as this will be done automatically
Example:<code>
db.Initialize("eventname","mydbdomain.com","dbusername","dbpassword","dbname")</code>- ListTablesAsync
Get a list of all tables inside this catalog (database)
The event listtables will be raised
Example:<code>
db.ListTablesAsync
Sub sql_listtables(tables As List)
Log("sql_listtables()")
For i=0 To tables.Size-1
Log("Table "&tables.Get(i))
Next
End Sub
</code>- PeparedStatement (sql As String)
- QueryASync (query As String, Task As String)
Query the Database. When the Method finishes the event QueryResult
will be raised
QueryResult gets two values. A "List of Maps" for the results. Each
Item in the List contains a Map holding the Values from on Resultrow
The Second value is a Map containing some informations:
ColumnCount, RecordCount and time elapsed in ms for the query
Example:<code>
db.queryasync("select * from members LIMIT 0,1 ;")</code>- QueryASync2 (query As String, Task As String)
Query the Database. When the Method finishes the event QueryResult2
will be raised
QueryResult gets two values. A "List of Strings" for the results. Each
Item in the List contains a String holding the Values from on Resultrow
in the format "["+field1+","+field2+"]"
The Second value is a Map containing some informations:
ColumnCount, RecordCount and time elapsed in ms for the query
Example:<code>
db.queryasync2("select * from members LIMIT 0,1 ;")</code>- SelectDB (database As String) As Boolean
Manually select the database to Query.- SetPeparedBlob (parameterIndex As Int, imagepath As String)
- SetPeparedInt (parameterIndex As Int, x As Int)
- SetPeparedLong (parameterIndex As Int, x As Long)
- SetPeparedString (parameterIndex As Int, x As String)
- check_connection
- isReconnectEnabled As Boolean
Test whether or not automatic reconnect is currently enabled.
By default automatic auto_reconnect is enabled
Return type: @return:true if automatic auto_reconnect is enabled, false if it is disabled
- ReconnectNumRetry As Int
Returns the maximum number of automatic reconnection attempts before giving
up and throwing an exception.
If this value was not changed with {@link #setReconnectNumRetry(int)} the default
number of attempts is 15- ReconnectTime As Int
Returns the waiting time before attempting to auto_reconnect to the MySQL
Database server.
If this value was not changed with {@link #setReconnectTime(int)} the default
waiting time is 5 seconds
The library is not free. You need to donate as low as 5$ to get the library.
I did donate this friday Agust 21 2015. Please send de link to download library connect Mysql. Thanks
mail: inteligencia_2000@yahoo.com
mail: inteligencia_2000@yahoo.com
This Library can be used to connect your Device to a MySQL database. The Library use a direct connection to the MySQL databaseserver.
Requisites: The Database must be accessible from "outside"
DisAdvantages: Due to the app needs the Databasecredentials (including username and password). There Credentials must be included in your App. I suggest using this Library only for private or company intern use.
MSMySQL
Version: 1.06
- MySQL
Events:Methods:
- BatchResult (batch As Map)
- ExecResult (meta As Map)
- ListTables (tables As List, ms as Long As )
- QueryResult (data as List As , meta As Map)
- QueryResult2 (data as List As , meta As Map)
- Status (Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)
Properties:
- CloseDatabase
Closes the database
Example:<code>
db.closedatabase
</code>- DisableReconnect
Disable automatic auto_reconnect if the MySQL Database Connection is lost
By default this is enabled- EnableReconnect
Enable automatic auto_reconnect if the MySQL Database Connection is lost
By default this is enabled.- ExecuteASync (query As String, Task As String)
executes ONE SQL-Commands (insert, update, delete)
Example:<code>
db.executeasync("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
</code>- ExecuteBatchASync (batch As List, Task As String)
executes a batch of SQL-Commands (insert, update, delete)
Example:<code>
Dim batch As List
For i=1 To 100
batch.Add("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
Next
db.executebatchasync(batch)
</code>- ExecutePeparedStatement
- Initialize (event As String, host As String, user As String, password As String, Database As String)
Initialize the Library
the url to your database. You dont need to prefix it with
jdbc:mysql:// as this will be done automatically
Example:<code>
db.Initialize("eventname","mydbdomain.com","dbusername","dbpassword","dbname")</code>- ListTablesAsync
Get a list of all tables inside this catalog (database)
The event listtables will be raised
Example:<code>
db.ListTablesAsync
Sub sql_listtables(tables As List)
Log("sql_listtables()")
For i=0 To tables.Size-1
Log("Table "&tables.Get(i))
Next
End Sub
</code>- PeparedStatement (sql As String)
- QueryASync (query As String, Task As String)
Query the Database. When the Method finishes the event QueryResult
will be raised
QueryResult gets two values. A "List of Maps" for the results. Each
Item in the List contains a Map holding the Values from on Resultrow
The Second value is a Map containing some informations:
ColumnCount, RecordCount and time elapsed in ms for the query
Example:<code>
db.queryasync("select * from members LIMIT 0,1 ;")</code>- QueryASync2 (query As String, Task As String)
Query the Database. When the Method finishes the event QueryResult2
will be raised
QueryResult gets two values. A "List of Strings" for the results. Each
Item in the List contains a String holding the Values from on Resultrow
in the format "["+field1+","+field2+"]"
The Second value is a Map containing some informations:
ColumnCount, RecordCount and time elapsed in ms for the query
Example:<code>
db.queryasync2("select * from members LIMIT 0,1 ;")</code>- SelectDB (database As String) As Boolean
Manually select the database to Query.- SetPeparedBlob (parameterIndex As Int, imagepath As String)
- SetPeparedInt (parameterIndex As Int, x As Int)
- SetPeparedLong (parameterIndex As Int, x As Long)
- SetPeparedString (parameterIndex As Int, x As String)
- check_connection
- isReconnectEnabled As Boolean
Test whether or not automatic reconnect is currently enabled.
By default automatic auto_reconnect is enabled
Return type: @return:true if automatic auto_reconnect is enabled, false if it is disabled
- ReconnectNumRetry As Int
Returns the maximum number of automatic reconnection attempts before giving
up and throwing an exception.
If this value was not changed with {@link #setReconnectNumRetry(int)} the default
number of attempts is 15- ReconnectTime As Int
Returns the waiting time before attempting to auto_reconnect to the MySQL
Database server.
If this value was not changed with {@link #setReconnectTime(int)} the default
waiting time is 5 seconds
The library is not free. You need to donate as low as 5$ to get the library.
I have a problem with your example with library MsMySql
I've tryed to run your example, but I have an error
LogCat connected to: emulator-5554
main_globals (java line: 434)
java.lang.NoClassDefFoundError: de.donmanfred.b4a.MySQL
at com.localhost.MySql.main._globals(main.java:434)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at com.localhost.MySql.main.initializeGlobals(main.java:263)
at com.localhost.MySql.main.afterFirstLayout(main.java:95)
at com.localhost.MySql.main.access$100(main.java:16)
at com.localhost.MySql.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:132)
at android.app.ActivityThread.main(ActivityThread.java:4123)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
at dalvik.system.NativeStart.main(Native Method)
Can you tell me what is wrong and how can I fix it ?
I've tryed to run your example, but I have an error
LogCat connected to: emulator-5554
main_globals (java line: 434)
java.lang.NoClassDefFoundError: de.donmanfred.b4a.MySQL
at com.localhost.MySql.main._globals(main.java:434)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at com.localhost.MySql.main.initializeGlobals(main.java:263)
at com.localhost.MySql.main.afterFirstLayout(main.java:95)
at com.localhost.MySql.main.access$100(main.java:16)
at com.localhost.MySql.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:132)
at android.app.ActivityThread.main(ActivityThread.java:4123)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
at dalvik.system.NativeStart.main(Native Method)
Can you tell me what is wrong and how can I fix it ?
did you copy all three files to the additional libraries folder?
What is the code you are using? (you initialize-command; mark out your password but show the rest...
What is the code you are using? (you initialize-command; mark out your password but show the rest...
No. I dont have/use B4i
Don, I've read the first few pages and the last couple, so please excuse if I'm asking an obvious question.
I've been running RDC on my local Windows server with a SQLite DB successfully for some time now. BUT it's getting to the stage where I need to put my data on a Hosting site obviously using MySQL - for obvious reasons - crashes, power outages that last longer than a UBS power supply lasts etc, etc
Is your library the way to go. There's nothing too sophisticated about my database, straight tables, I always keep my data simple.
I need to Insert and Update records to keep the DB up to date and obviously SELECT as per what you can do in RDC. I currently do my Db updates with a B4J program because my server is connected on the same network as my PC - I don't use RDC for that.
Also in a post above you mention there isn't a B4i version yet, but is there a B4J version. Sorry if that's cover elsewhere in the post but it's long for a first reading if the library isn't going to be suitable but it sounds excellent
I've been running RDC on my local Windows server with a SQLite DB successfully for some time now. BUT it's getting to the stage where I need to put my data on a Hosting site obviously using MySQL - for obvious reasons - crashes, power outages that last longer than a UBS power supply lasts etc, etc
Is your library the way to go. There's nothing too sophisticated about my database, straight tables, I always keep my data simple.
I need to Insert and Update records to keep the DB up to date and obviously SELECT as per what you can do in RDC. I currently do my Db updates with a B4J program because my server is connected on the same network as my PC - I don't use RDC for that.
Also in a post above you mention there isn't a B4i version yet, but is there a B4J version. Sorry if that's cover elsewhere in the post but it's long for a first reading if the library isn't going to be suitable but it sounds excellent
It is one way yes
You can do this all with my library
I´m not sure. But. I dont use any android specific code there if i remember correctly. It SHOULD work i would say.
PS: I´ll send a link to download the library to you as you already have done a lot of donations to me. I would be pleased if you want to use them.
I´ll answer you donation from today soon (Thanx for the donation (again
)!! )
Don, if I can use it I have no problem with a donation, not only because of the help you've given me in the past with other issues but you've obviously put a lot of work into this library and I suspect it will make life a lot easier for me in transferring to a hosting service