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

Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
You go and you create an app absolutely brilliant app now :) the rest is up to you. decide what you want the app to look like and just take it from there.

Here are the MySQL subs, they might come in handy for you...
B4X: 
#Region  MYSQL RESPONSES

Sub MySQL_BatchResult(batch As Map) 'Not tested
    Log(batch.Size)
End Sub

Sub MySQL_ExecResult(meta As Map)
    Log(meta.Size)
End Sub

Sub MySQL_ListTables(tables As List, ms As Long)
    For i = 0 To tables.Size - 1
        Log(tables.Get(i))
    Next
    Log(tables.Size)
End Sub

Sub MySQL_QueryResult(data As List, meta As Map)
    ToastMessageShow(meta.Get("RecordCount") & " rows retrieved in " & meta.Get("ms") & " milliseconds", True)

    For i = 0 To data.Size - 1
        Log(data.Get(i))
    Next
    Log(data.Size)  
    Log(meta.Size)  
End Sub

Sub MySQL_QueryResult2(data As List, meta As Map)
    ToastMessageShow(meta.Get("RecordCount") & " rows retrieved in " & meta.Get("ms") & " milliseconds", True)

    For i = 0 To data.Size - 1
        Log(data.Get(i))
    Next
    Log(data.Size)
    Log(meta.Size)
End Sub

#End Region
 
B

benji

Active Member
Licensed User
Longtime User
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.
This is the array in the log
(ArrayList) [INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='19:05',valor=1000,estado='Abierto',tipo='Normal',folio=1000001,lat=-33.4855,lon=-70.5515;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='19:06',valor=1000,estado='Abierto',tipo='Normal',folio=1000002,lat=-33.4854,lon=-70.5514;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='19:06',valor=1000,estado='Abierto',tipo='Normal',folio=1000003,lat=-33.4854,lon=-70.5514;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='19:47',valor=1000,estado='Abierto',tipo='Normal',folio=1000004,lat=-33.4854,lon=-70.5514;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='19:55',valor=1000,estado='Abierto',tipo='Normal',folio=1000005,lat=-33.4852,lon=-70.5514;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='20:04',valor=1000,estado='Abierto',tipo='Normal',folio=1000006,lat=-33.4852,lon=-70.5514;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='20:08',valor=1000,estado='Abierto',tipo='Normal',folio=1000007,lat=0,lon=0;, INSERT INTO damirvta SET maquina=060,fecha='2015-02-26',horario='19:05',hora='20:13',valor=1000,estado='Abierto',tipo='Normal',folio=1000008,lat=-33.4855,lon=-70.5513;]
Click to expand...

this is my code
B4X: 
    batch1.Initialize
    CurVentas = DBVenta.ExecQuery("Select * from Venta where Up = 'NO'")
    If CurVentas.RowCount > 0 Then
        For i = 0 To CurVentas.RowCount-1
            CurVentas.Position = i
            FechaVta = CurVentas.GetString("Fecha")
            HorarioVta = CurVentas.GetString("Horario")
            HoraVta = CurVentas.GetString("Hora")
            ValorVta = CurVentas.GetString("Valor")
            TipoVta = CurVentas.GetString("Tipo")
            FolioVta = CurVentas.GetString("Folio")
            latVta = CurVentas.GetString("lat")
            lonVta = CurVentas.GetString("lon")
            batch1.Add("INSERT INTO damirvta SET maquina=" & AuxMaquina & ",fecha='" & FechaVta & "',horario='" & HorarioVta & "',hora='" & HoraVta & "',valor=" & btn300.Text & ",estado='Abierto',tipo='Normal',folio=" & FolioVta & ",lat=" & latVta & ",lon=" & lonVta & ";")
            Log(batch1)       
        Next
    End If
    dbM.ExecuteBatchASync(batch1)
    Msgbox(CurVentas.RowCount,"SQL")

this is the result in my DB, with a clean table
x9bsttrv515mhenlxi8j_s.jpg


this happens with MariaDB an MSMySQL, when i insert one record, no problem.
another problem that i have, how i know if the executebatch is ok or wrong?
i try to use this
B4X: 
Sub MySQL_BatchResult(batch As Map)
    Log(batch)
    For j=0 To batch.Size-1
          Log(batch.GetKeyAt(j)& "=" & batch.GetValueAt(j))
    Next
End Sub

or only log(batch), but i have this error "java.lang.Exception: Sub mysql_batchresult signature does not match expected signature."

any help is appreciated, thanks in advance.
 
K

keirS

Well-Known Member
Licensed User
Longtime User
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.any help is appreciated, thanks in advance.
Click to expand...

If you are going to do a lot of batch inserts then take a look at LOAD DATA LOCAL INFILE. Basically you create a CSV file and then pass the file to the MySQL server. The statement is executed just like any other command and the JDBC driver handles the uploading of the file to the server. MySQL then reads the file and handles the inserts. It's massively faster than using multiple insert statements.
 
BarryW

BarryW

Active Member
Licensed User
Longtime User
Hi Don, I was trying your codes and i got an error....

B4X: 
db.PeparedStatement("INSERT INTO pictures Set image=?;")
    db.SetPeparedBlob(1,pathtojpg)
    db.ExecutePeparedStatement

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

at java.lang.reflect.Constructor.constructNative(Native Method)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:445)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2071)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at de.donmanfred.b4a.MySQL.ExecutePeparedStatement(MySQL.java:297)
at ex.escalera.mysql.main._button3_click(main.java:392)

at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:163)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:159)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
at android.view.View.performClick(View.java:4478)
at android.view.View$PerformClick.run(View.java:18698)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5257)
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:793)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:609)
at dalvik.system.NativeStart.main(Native Method)
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
 
BarryW

BarryW

Active Member
Licensed User
Longtime User
I got an another error sir. String only without blob.

B4X: 
DB.PeparedStatement("INSERT INTO test SET text_ = ?")
        DB.SetPeparedString(1, EditText1.Text)
        DB.ExecutePeparedStatement

Connected to Database
java.sql.SQLException: Can't call commit when autocommit=true

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1599)
at de.donmanfred.b4a.MySQL.ExecutePeparedStatement(MySQL.java:298)
at ex.escalera.mysql.main._button3_click(main.java:390)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:163)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:159)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
at android.view.View.performClick(View.java:4478)
at android.view.View$PerformClick.run(View.java:18698)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)

at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5257)
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:793)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:609)
at dalvik.system.NativeStart.main(Native Method)
java.sql.SQLException: Can't call commit when autocommit=true
 
B

benji

Active Member
Licensed User
Longtime User
something like this, doesn't works

batch.Initialize
For i=1 To 100
batch.Add("INSERT INTO test SET maquina=" &i& ",valor=" &i& ";")
Next
dbM.executebatchasync(batch)

i lost the first and the last....
:(
 
B

benji

Active Member
Licensed User
Longtime User
what would be the code to update?
db.ExecuteASync("UPDATE .......
 
A

Anser

Well-Known Member
Licensed User
Longtime User
Hi,

Reading the posts in this thread, I understand that this is a great lib and performs much fast. I really appreciate you for creating a good MySQL lib.

I need few clarifications regarding this library.

  1. Does this library support calling a MySQL stored procedure ?
  2. Does this library support calling a MySQL stored procedure with an OUT parameter ?
  3. I read that there is a MariaDB Lib too from you. I don't understand why a different lib ? According to my understanding, in Windows, the regular ODBC connector from MySQL will work to connect to a MariaDB database. Is there any specific reason that one should use the MariaDB Lib ?
  4. You have pointed out in your 1st post in this thread about the DisAdvantages of this lib ie "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.". What is your opinion about storing the credentials in an encrypted form in some files in the asset folder? Then the app reads the encrypted credentials and then decrypt it and then connect to the database. I hope that there are functions available in B4A to encrypt and decrypt string values. I know that this is not a perfect solution, but trying to put some extra effort for the safety of the credentials. May be what I saying is a big blunder. If someone can decompile the code, then he get the code used to encrypt and decrypt too. No idea. I am a newbie in android and B4A.


Edit : I got the answer for point number 1. Yes its possible
I got answer for point number 3 too. ie Avoid Oracle Licence fee for commercial application if I use MAriaDB database and MariaDB Connector Lib.


Regards

Anser
 
Last edited:
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Anser said:
  • Does this library support calling a MySQL stored procedure ?
Click to expand...
I have no stored procedures but it should work.....
Anser said:
  • Does this library support calling a MySQL stored procedure with an OUT parameter ?
Click to expand...
Should work, yes. But also here; as i dont have any stored procedures i havn´t tested it actually.
Anser said:
I read that there is a MariaDB Lib too from you. I don't understand why a different lib ? According to my understanding, in Windows, the regular ODBC connector from MySQL will work to connect to a MariaDB database. Is there any specific reason that one should use the MariaDB Lib ?
Click to expand...
The MariaDB connector you can use even in commercial projects without need for a Oracle MySQL-Licence
MSMySQL you can only use for private use. If you want to use it in a commercial field you need to have a MySQL-Licence from oracle.

MariaDB connector has the additional plus that it is a real light weight odbc connector (around 200k)
 
Last edited:
A

Anser

Well-Known Member
Licensed User
Longtime User
My Question
Does this library support calling a MySQL stored procedure with an OUT parameter ?
Click to expand...
Your answer
Should work, yes. But also here; as i dont have any stored procedures i havn´t tested it actually.
Click to expand...


Is it possible for you to test and confirm whether this feature is supported in your lib ?.
Please find below a simple MySQL stored procedure with an OUT parameter
B4X: 
DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11),
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
    IF creditlim > 50000 THEN
    SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
END$$
The above stored procedure just returns the CustomerLevel (String) based on the Customer's Credit Limit

In MySQL, we can call the GetCustomerLevel() stored procedure as follows:
B4X: 
CALL GetCustomerLevel(103,@level);
SELECT @level AS level;
I need to know the value of the variable "level" in my android app


In VB or similar windows app the same is achieved via ADO by using the following code
B4X: 
oCmd=CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConnection 'Connection Object
oCmd.CommandType:=adCmdStoredProc
oCmd.CommandText:='GetCustomerLevel' 'Stored Procedure name

nCustomerNumber=103 'Int Type
'Preparing the First parameter (Input Parameter) to the Stored Procedure
oParam = oCmd.CreateParameter( 'p_customerNumber', adInteger, adParamInput, 11, nCustomerNumber )
oCmd.Parameters.Append( oParam )

cCustomerLevel='' 'String type. This varable is supposed to store the OUT parameter value
'Preparing the Second parameter (OutPut Parameter) to the Stored Procedure
oParam = oCmd.CreateParameter( 'p_customerLevel', adVarChar, adParamOutput, 10, cCustomerLevel )
oCmd.Parameters.Append( oParam )

'Now execute the Command
oCmd.Execute()

' Display the value in the OUT parameter of the Stored Procedure
MsgBox( oCmd.Parameters( "cCustomerLevel" ).Value, 'This is the value contained in the cCustomerValue ')

Hope the above will help you to understand what I am trying to explain.

Regards
Anser
 
Last edited:
DonManfred

DonManfred

Expert
Licensed User
Longtime User
i have inserted your stored procs into a empty database,
setup heidisql to use this database
and tried to execute
B4X: 
CALL GetCustomerLevel(103,@level);
SELECT @level AS level;
but i get no result in heidisql.

Maybe i dont know how to execute the proc and get the value showed in heidisql...
 
A

Anser

Well-Known Member
Licensed User
Longtime User
Manfred.png

It is working fine for me.
B4X: 
CALL get_customer_level(5,@level);
SELECT @level AS level;
I get the result "Silver"

Manfred2.png

I hope you have the above given data in the table customers. If not then kindly insert the data. Just copy the below given sql content to the Query window on HeidiSQL and run it. The data will be added to the table. After that run the above given stored procedure.
B4X: 
-- Dumping structure for table anser_db.customers
DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `customer_id` int(10) DEFAULT NULL,
  `customer_name` varchar(50) DEFAULT NULL,
  `creditlimit` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table anser_db.customers: ~10 rows (approximately)
/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
REPLACE INTO `customers` (`customer_id`, `customer_name`, `creditlimit`) VALUES
    (1, 'Anser', 5000),
    (2, 'John', 3000),
    (3, 'Thomas', 11000),
    (4, 'Sebastian', 20000),
    (5, 'David', 8000),
    (6, 'Antonio', 4000),
    (7, 'Mathew', 17000),
    (8, 'Issac', 25000),
    (9, 'Daniel', 14000),
    (10, 'Simpson', 4800);
/*!40000 ALTER TABLE `customers` ENABLE KEYS */;

Regards

Anser
 
Last edited:
Peter Simpson

Peter Simpson

Expert
Licensed User
Longtime User
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...
 
Last edited:
You must log in or register to reply here.
Top