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

A

Anser

Well-Known Member
Licensed User
Longtime User
Hi Peter Simpson,

Peter Simpson said:
Hello @Anser,
I've just checked this library version 0.36 with Stored Procedures and I found that it works 100% perfect with both inserting and extracting of data to and from one of my MySQL databases with the Call statement.

I hope that this helps you...
Click to expand...

What I am talking here is NOT about executing/calling a stored procedure to insert data to the table OR calling a stored procedure to retrieve some records from a table. It's about using the OUT parameter in stored procedure and accessing its value from B4A app.

Are you able to get/read the OUT parameter value of Stored Procedure from B4A app ?
B4X: 
CALL GetCustomerLevel(103,@level);
SELECT @level AS level;
If you don't mind, can you show a sample code using this lib
  • Showing how to call a stored procedure with an OUT parameter using this lib
  • How the OUT value is retrieved after executing the stored procedure.
In post number 76 in this thread, I have posted a sample code showing how this is done using VB.

There are various usage for the OUT parameter in a stored procedure. One among other uses of this can be for eg:-

A user try insert data to the table. The stored procedure based on certain conditions may OR may not insert the data to the table. The OUT parameter can be used to read the status of the operation ie you can send a string for eg. "Did not insert the data to the table because the customer is a defaulter in payments" OR a message "Did not add because this customer belongs to Platinum segment" OR "Added the customer successfully".

So in the front end application we don't have to complicate the code. We just need to make a call to the storeprocedure CALL Test("CustomerID", "CustomerName",@Result)

We just have to read the the value of the variable "Result" to know the status of the operation.

Hope you understand.

Regards
Anser
 
Last edited:
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
Hello @Anser,
Below is a simple SP (Stored Procedure) that I created.
B4X: 
DELIMITER //
CREATE PROCEDURE CountCountry(
    IN `Countries` VARCHAR(20),
    OUT `Total` INT)
BEGIN
    SELECT COUNT(*)
    INTO `Total`
    FROM `Country`
    WHERE `Name`
    LIKE CONCAT('%', Countries ,'%');
END //
DELIMITER ;
Lines in B4A that I use to query the SP above.
B4X: 
'Code in B4A
   MySQL.ExecuteASync("Call CountCountry('unit', @Total)")
   MySQL.QueryASync2("SELECT @Total AS `Countries`")
By the way, the result is 4 and that's exactly what I get in B4A.
Please let me know if I've still not understood what you are trying to do...
 
Last edited:
A

Anser

Well-Known Member
Licensed User
Longtime User
Hi Peter Simpson,

This is what I was looking for.

Your sample is perfectly fine and I understood how a stored procedure with an OUT parameter is called and how the OUT parameter 's value is retrieved and read from B4A.

Good to know that this lib supports all the features of a stored procedures in all perspective and not just merely calling/executing a stored procedure.

Thanks & Regards

Anser
 
A

Anser

Well-Known Member
Licensed User
Longtime User
Can I get a working sample to test your lib

The sample included in the lib errors at line 39. Compilation error.
Error_1.png


I understand that db.QuerySync() expects a second parameter ie Task which is missing in the sample.

Error_2.png


I corrected the code to
B4X: 
db.QueryASync("select * from b4alog ORDER BY log_id ASC;","mytest")

but then I get the following error
Error_Emulator.png


The following is the error log

B4X: 
** Service (newinst2) Start **

-- AppUpdating.NewInst2: processing service_start

** Activity (main) Create, isFirst = true **

Sub NOT FOUND: 'mysql_status

Connected to Database

** Activity (main) Resume **

Activity_Resume()
SqlException: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '1.421930164762E12' in column '3' is outside valid range for the datatype INTEGER.

MySQL_QueryResult(null)

main_mysql_queryresult (java line: 437)
java.lang.NullPointerException
    at b4a.example.main._mysql_queryresult(main.java:437)
    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$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
Regards
Anser
 
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
Hello @Anser, I always use QueryASync2, try using QueryASync2 instead...

You are also missing a sub, please add the following to your code.
B4X: 
Sub mysql_Status(Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)
    Log("Connected = " & Connected)
End Sub
The sample included in the lib
Click to expand...
What sample???
 
Last edited:
A

Anser

Well-Known Member
Licensed User
Longtime User
Peter Simpson said:
Hello @Anser, I always use QueryASync2, try using QueryASync2 instead...

You are also missing a sub, please add the following to your code.

What sample???
Click to expand...
There was a sample project included in the zip file which came along with this lib. I simply tried to compile and run that sample without making any changes to it. Unfortunately it throwed the errors that I specified in my previous post.

By the way what is he difference between QueryASync and QueryASync2 ?. Anyway, I shall follow your advice.

Regards
Anser
 
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
Hiya @Anser,
I didn't realise that @DonManfred had added a sample project.
By the way what is he difference between QueryASync and QueryASync2
Click to expand...
QueryASync returns = {Name=Aruba, Continent=North America, Region=Caribbean, Population=103000, LocalName=Aruba}
QueryASync2 returns = [Aruba,North America,Caribbean,103000,Aruba]
 
Last edited:
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
You're correct it does not matter @DonManfred. But I find it annoying having anything appearing in the Upper and Lower log windows that can easily be remedied. Adding Sub MySQL_Status just takes away that annoying nagging message that appears again and again and again and again and again and again and again and again and again and again and again and again and again and again every time app is assembled.

Adding the sub just cheers me up :p
 
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
No, PLEEEEEEEEEEEEEEEEEASE do not remove the nagging message. I think that it's a great sub that really comes in handy and should be there as standard. It help me remember to check the connection status and without that nagging message I would never had added that sub. No Leave the nagging message there, I think that it will help other users of your great library too :)
 
JakeBullet70

JakeBullet70

Well-Known Member
Licensed User
Longtime User
Peter Simpson said:
No, PLEEEEEEEEEEEEEEEEEASE do not remove the nagging message. I think that it's a great sub that really comes in handy and should be there as standard. It help me remember to check the connection status and without that nagging message I would never had added that sub. No Leave the nagging message there, I think that it will help other users of your great library too :)
Click to expand...

Same for me.
 
A

Anser

Well-Known Member
Licensed User
Longtime User
Hi,

I am puzzled regarding closing the database connection. At which point should I close the connection with the DB ? Obviously it should be when the user quits the application, but how do I capture the event when the user quits ? Need to know from the experts, so that I don't want to guess and try and practice mistakes or wrong ways of coding.

I used RDC earlier, RDC was a connection less process, so I never had to bother about closing the connection.

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.

So I used the following entries in each activity
B4X: 
Sub Globals
  Private reqManager As DBRequestManager
End Sub
Sub Activity_Create(FirstTime As Boolean)
  If FirstTime Then
    reqManager.Initialize(Me, RdcServerIP)
  End If
End Sub

Any simple sample which handle the above said issues will be appreciated.

Regards
Anser
 
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Search the forum about httputils and multiple activitys. You will find much samples and suggestions to use a service for the job handling which then delegates the answers to the activity which does the call.

The same will append here with the library

Use a Service. The Service will have the only one Databaseconnection. From your activities you call a method in your service to do the request. The service will do the request and call a sub in your activity to deliver the results.
 
You must log in or register to reply here.
Top