B4A Library MySQL Library with jdbc

Hello everyone,

This is my second library very similiar to MSSQL.


There are only three functions.

1) setDatabase("Server IPNUMBER (not name)","databasename","username","password")
2) Query("select * from tablename") --> return rows as a LIST
3) TableList --> return table names rows as a LIST.

You can also write create, update or delete queries.
You must copy MYSQL.jar, MYSQL.xml files to your addititonal library folder.

You should download jdbc driver from Mysql site:
Download jdbc driver

After you must copy mysql-connector-java-5.1.22-bin.jar file to your additional library folder.

You must add Manifest

AddPermission("android.permission.INTERNET")
AddPermission("android.permission.ACCESS_NETWORK_S TATE")​

Maybe you need to allow access to MySQL;

mysql> grant all privileges on *.* to root@'%' with grant option;
you can change username root to another one.
​

Using

'Activity module
Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.

End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   Dim a As MYSQL
   Dim L As List
   Dim hsv As HorizontalScrollView   
   Dim svRows As ScrollView
End Sub

Sub Activity_Create(FirstTime As Boolean)

   'write your own parameters
   'a.setDatabase("server ipnumber (not name)","databasename","username","password")  'Cancel this line
   'you can write named server
   a.setDatabase("server ipnumber or url","databasename","username","password")

   ' select query
   '    L=a.Query("select * from tb_Test")
   ' or get table list for database
      L = a.TableList  'you can get all tables for default database

   ' CUD: Create, Update, Delete
   ' or UPDATE query
   '   Dim r as Int        ' return affected rows count
   '   r = a.ExecuteNonQuery("update tablename set fieldname='xxxx' where keyname='xx'; ")    
   
   If L.IsInitialized=False Then
      Msgbox("Records Not Found","Warning")
      Return
   End If
   
   Dim row As List,cols As Int   ,rows As Int
   rows = L.Size   
   row = L.Get(0)  'Header row
   cols = row.Size

   hsv.Initialize(cols*150dip,0)
   Activity.AddView(hsv,0,0,100%x,100%y)

   svRows.Initialize(rows*30dip)
   hsv.Panel.AddView(svRows,0,30dip,cols*150dip,100%y-30dip)
   
   'CREATE HEADER LABELS
   For j=0 To cols-1
      hsv.Panel.AddView(LabelCreate(row.Get(j),Colors.DarkGray,Colors.White) _
                   ,j*150dip   _
                   ,0         _
                   ,149dip   _
                   ,29dip)
   Next
   
   'CREATE RECORD LABELS
   For i=1 To L.Size-1
      row = L.Get(i)
      For j=0 To cols-1
         svRows.Panel.AddView( LabelCreate(row.Get(j),Colors.LightGray,Colors.Black) _
                         ,j*150dip      _
                         ,i*30dip-30dip _
                         ,149dip        _
                         ,29dip)
      Next
   Next

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub LabelCreate(str As String,backcolor As Int,textcolor As Int) As Label
   Dim t As Label
   t.Initialize(0)
   t.text=" " & str
   t.color=backcolor
   t.textcolor=textcolor
   t.Gravity = Gravity.CENTER_VERTICAL
   Return t
End Sub

I wish to be useful.

Note:

Be carefully working with databases, responsibility for problems caused by this library is yours.

This library userfull 'in firm' applications.
Because MySQL IPNumber should be accessible through the network.
I'm canceling this red-colored description. You can connect if Mysql server accessible anywhere.
​

 

[Mahares]

@hasan: Thank you for your MySQL lib. Here are my questions:
1. The small code sample you have when compiled produces an APK file of 2MB, which is huge. I assume it is because of the mysql-connector-java-5.1.22-bin.jar file. If you include it in another application, it will make the app even bigger. Isn't that going to really compromise the performance and slows it down dramatically? Do you have a solution?
2. When you try to get the record count and use a list size, it includes the header as another record. For instance, if the number of records in the table is 50, it shows 51 because it adds the table (query) header as the 1st record.
3. I cannot INSERT records into any table using INSERT INTO etc.. and a.Query("INSERT INTO etc. "). I can view records, but not insert. It does not return any errors. When you check the server, there are no new records.
Thank you for addressing these.

 

[hasanaydin52]

Mysql Update,Insert,Delete query

Hello Mahares,

1. I downloaded the last jdbc from Mysql official site.
   Maybe there are smaller sized jdbc driver. I will search.
2. It seemed like a good idea to me put headers in the first row. I think not block you.
   However, no direct assignment in any Control/View in a situation that prevents the need to solve it.
3. I added ExecuteNonQuery function to library 1.01 for non query sql.

 

[Mahares]

Thank you hasan:
1. The a.ExecuteNonQuery(SQL) works now. To make it compatible with B4A, perhaps it should have been called: ExecNonQuery(SQL) although it is not a big deal.
2. To get the record number returned by a query, does one have to subtract 1 record all the time? It is a good idea to include the header, but not in the record count.
3. When a record is not saved because of an error, you cannot tell whether it worked or not unless of course you look in the server. Is there a way to tell whether the record is successful or not immediately after the execution of the statement?
4. What do you mean by the below quote?

However, no direct assignment in any Control/View in a situation that prevents the need to solve it.

5. I think your library will be a lot more accepted if the jdbc file is much smaller. The same goes for your MSSQL lib.

 

[hasanaydin52]

Mysql Jdbc

Hello Mahares,

I wanto to say,

if you want to connect List variable directly to View Control.

For example;

Dim L as List
Dim d as MYSQL

d.SetDatabase(....)
L = d.Query("select ... from ...")

Dim Lv as ListView
Lv.Initialize(0)

Lv.LoadAray( L )   'concept 

.
.
.

Is there something similar to this?
​

 

[sigster]

Hi

thanks for the Mysql Library
I get No Records Not Found

I use IP address , Database , Username , Password
do I get same error
if I can not connect no internet access
if I can connect but I can not connect to the database

Regards
Sigster

 

[sigster]

Hi

it was something wrong with the Database,username,password
it is working know

Thanks
sigster

 

[hasanaydin52]

i am glad

 

[ivanomonti]

mmm help me

no error, no record no all :BangHead:

Sub Process_Globals

End Sub

Sub Globals
   ' http://www.b4x.com/forum/additional-libraries-classes-official-updates/22291-mysql-library-jdbc.html#post129399
   Dim Button1 As Button
   Dim ListView1 As ListView
End Sub

Sub Activity_Create(FirstTime As Boolean)
   Activity.LoadLayout("Main")
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub Button1_Click
   Dim db As MYSQL
    Dim L As List
   db.setDatabase("server","schema","user","password")
   L = db.Query("Select * fron t_000_schede")
    If L.IsInitialized=False Then
        Msgbox("Records Not Found","Warning")
        Return
   Else
      For i = 0 To L.Size
         ListView1.AddSingleLine(L.Get(i))
      Next
   End If
End Sub

Possible not always list IsInitialized !!!!!!!!

 

[Mahares]

I recreated your project and the necessary layout. I replaced your credentials with mine. The only error I found in your code is:

For i = 0 To L.Size

should be:

For i = 0 To L.Size-1

Once I changed the above line, it worked fine and displayed the data in the listview as it should. You might want to check the info you have in this line or permissions????
db.setDatabase("server ipnumber (not name)","databasename","username","password")

 

[ivanomonti]

I recreated your project and the necessary layout. I replaced your credentials with mine. The only error I found in your code is:

For i = 0 To L.Size

should be:

For i = 0 To L.Size-1

Once I changed the above line, it worked fine and displayed the data in the listview as it should. You might want to check the info you have in this line or permissions????
db.setDatabase("server ipnumber (not name)","databasename","username","password")

Sub Button1_Click
   Dim db As MYSQL
    Dim L As List
   db.setDatabase("81.88.xx.xxx","namedb","username","password")
   L = db.Query("Select * from t_000_schede")
    If L.IsInitialized=False Then
        Msgbox("Records Not Found","Warning") 
        Return
   Else
      For i = 0 To L.Size-1
         ListView1.AddSingleLine(L.Get(i))
      Next
   End If
End Sub

result always the same, not collects, but strange thing is this

1) Android 2.2 = ok collects data
2) Android 4.0.3 = not collect the data, but it connects and always >> L.IsInitialized = False Aler after a period of time I get out of an unhandled error
3) emulator = ok collects data


2) samsung Galaxy s2 / android 4.0.3 / Bug

 

[hasanaydin52]

Hello ivanomonti,

I have S2 too.

It has run 4.0.3 and still running 4.0.4.

Can you send source to me?

 

[ivanomonti]

Hello ivanomonti,

I have S2 too.

It has run 4.0.3 and still running 4.0.4.

Can you send source to me?

yes, your email, I'll send the source

 

[hasanaydin52]

Hello ivanomonti,

My email address is hassanaydin@gmail.com

 

[Rafal Galewski]

Android 4.1.1 and problem with collect data

I have Android 4.1.1 and not collect the data, but it connects and always >> L.IsInitialized = False Aler after a period of time
I get out of an unhandled error

 

[hasanaydin52]

Hello Rafal,

My Galaxy S2, 4.0.4 running without problem.
But, i didnt test 4.1.1.
I will test as soon as possible.

Also;
Can you ping to mysql server?
Check your firewall.
Make grant permissions to tables.

 

[Rafal Galewski]

Now I check to another phone Galaxy S2 with 4.0.4

But nothing works.

Yes, I can ping it.

Firewall is ok.

A permission is set.


Now worked !!!

THe problem was in manifest file

'<uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/>

correct have to be <uses-sdk android:minSdkVersion="4"/>

 

[Rafal Galewski]

Insert

How can I use Non query insert ?

What is correct formula in Your library ?

database.ExecuteNonQuery("INSERT ...........")

 

[Rafal Galewski]

missing Zero in string

When I give You in INSERT "0001" as string

Your library put to database "1"


How to do it optional "0001" not only put 1 without zero?

 

[Rafal Galewski]

Problem with utf 8 - polish

Hi,

I have database MYSQL with coding UTF8_Polish_CI .

I cant write Polish letters to database using Your library.

Maybe You can help me how resolve this problem.

Thank You
Very much