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

Anser · Mar 7, 2015

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

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

Peter Simpson · Mar 8, 2015

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

Anser · Mar 9, 2015

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

Anser · Mar 9, 2015

Can I get a working sample to test your lib

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

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

I corrected the code to

B4X:

db.QueryASync("select * from b4alog ORDER BY log_id ASC;","mytest")

but then I get the following error

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 · Mar 9, 2015

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

What sample???

Anser · Mar 9, 2015

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???

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 · Mar 9, 2015

Hiya @Anser,
I didn't realise that @DonManfred had added a sample project.

By the way what is he difference between QueryASync and QueryASync2

QueryASync returns = {Name=Aruba, Continent=North America, Region=Caribbean, Population=103000, LocalName=Aruba}
QueryASync2 returns = [Aruba,North America,Caribbean,103000,Aruba]

Anser · Mar 9, 2015

Thank you.

DonManfred · Mar 9, 2015

In fact
QueryASync returns a List of Maps. Each Databaseentry is one Map in this List.
QueryASync2 returns a List of String. Each Databaseentry is one String like described above in this List.

QueryASync2 was born due to the needs of Peter

Peter Simpson · Mar 9, 2015

That's it, blame me lol

JakeBullet70 · Mar 9, 2015

Peter Simpson said:
That's it, blame me lol

Yes, blame him!! And by the way @Peter Simpson... Thanks. handy for me too.

DonManfred · Mar 9, 2015

Peter Simpson said:
You are also missing a sub, please add the following to your code.

It does not matter if the sub does not exist. The event will just not raise then.

Peter Simpson · Mar 9, 2015

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

DonManfred · Mar 9, 2015

You are right. I should remove that nagging log-output

Peter Simpson · Mar 9, 2015

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

DonManfred · Mar 9, 2015

Maybe.
Maybe they feel disturbed about it

JakeBullet70 · Mar 9, 2015

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

Same for me.

JakeBullet70 · Mar 9, 2015

Keep it. Have a Verbose setting.

Anser · Mar 10, 2015

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 · Mar 10, 2015

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.

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

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Privacy & Transparency

Privacy & Transparency