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

 

[hasanaydin52]

Hello Rafal,

My sample is same as first page.

if server is accessible anywhere can you send me your "mysql server" address, username, password and your query.

or

You can create on one of the free mysql server hosts. You can find on web.
And send information to me.


hassanaydin@gmail.com

 

[Rafal Galewski]

Thank You for answer.

I have a problem with manifest declaration "min. version sdk "

Now I have another problem with write or read Polish language from database

Problem with utf 8 - polish


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

 

[Rafal Galewski]

Thank You!

Thank You very much for help - hassanaydin.

Now all correct work.

:sign0098:

 

[Sytek]

Tested on Viewpad7 Viewsonic

This one works just fine. Tested on a Viewpad7 Viewsonic Android 2.2.1
Apk Size Generated 585kb
Size installed on the device 2.02MB
I don't even had to Declare the following in the Manifest Editor

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

:sign0098:

 

[marco.canta]

Ciao, io ho fatto una applicazione con la libreria mysql ed lo esempio della prima pagina, ma non lavora.
Io ho android 4.1 ... mi da sempre errore .

Ci sono novita per android 4.1 ??
GRAZIE Marco

-----------------------

Hello, I made an application with the mysql library and the example of the first page, but it does not work.
I have Android 4.1 ... I always error. Any news for Android 4.1?

THANK Marco

 

[frandroid]

Help please!

First of all thank you very much for your work, it is very helpful for me.

I want to create a grill of buttons with articles. I have the articles in a Mysql database.

I use your example and it works, the trouble comes when I try to use a map. Here is the code:

Dim L As List
L.Initialize
L = db.Query("SELECT * FROM articulos")

'load the articles to the grill
Dim aux As Int
aux = 0
For j = 0 To botonesy-1
   For i = 0 To botonesx-1
      If aux < L.size Then
         Dim m As Map   
    m.Initialize
    m = L.Get(aux)
         Buttons(i,j).text=m.Get("descripcion")
      Buttons(i,j).Enabled=True
      Else
         Buttons(i,j).Text=""
         Buttons(i,j).Enabled=False
      End If
      aux=aux+1
   Next
Next

When I compile and run it I receive the following message:

LastException java.lang.ClassCastException: java.util.ArrayList cannot be cast to anywheresoftware.b4a.objects.collections.Map4$MyMap

Can anyone help me?

Thanks.

Fran

 

[Smee]

example

Does anyone have a small example project of working code of this library?

Many thanks if they do

 

[jflaplante]

Android 4.2.1

Hi,

I just tried your library with a Nexus 7 tablet.

I always get the 'no records' found message.

I tested my connectivity with a MySQL manager application directly on the tablet to make sure that I could reach and log on to the server. Everything was ok when using the MySQL manager application.

The thing I noticed was the APK size of 576kb instead of the 2mb reported by others. I get no errors when compiling. Does the MySQL client need to be included in the project files? I'm using b4a 2.5.

Thanks for any info.

JF.

 

[Rafal Galewski]

Problem with time execute select query

Hi,

When I use 21 query select like this :

Dim DBUstawieniaList As List
L= a.Query("Select val from dbo.sets where SalonID=" & SalonID & " AND ValueName ='SMTP_FROM_ADDRESS'")

DBUstawieniaList=L.Get(1)

Settings.SMTP_FROM_ADDRESS=DBUstawieniaList.Get(0)

The time execute this querys is 50 secund.

In my opinion is problem with Your library about speed querys.
How can do this faster query.

 

[hasanaydin52]

Hello Rafal,

How many records asr exists in dbo.sets?

If there are many records, its normal.

 

[Rafal Galewski]

Hello Rafal,

How many records asr exists in dbo.sets?

If there are many records, its normal.

50 select like this

 

[Tadder]

blobs...

hey thanks for the cool lib - is there any way to pull a blob and display the pic?

tad

 

[hasanaydin52]

Hello Tadder,

I didnt test it.

Did you test anyway?

 

[Tadder]

What format would the blob be in if I store that gif or png manually in the db? I think I'm just javing trouble encoding/decoding it...

 

[fasilosman]

Compiling Error

I am very beginner to B4A. I did all steps as you said. But while compiling the program I get an error as follows

......................
Compiling code. Error
Error compiling program.
Error description: The directory name is invalid.

Occurred on line: 6
End Sub
................

I can't figure it our. Please help me

 

[hasanaydin52]

Hello fasilosman,

Did you wrote sample code at first page?

And, you must change this line your properties.

'you can write named server or IP
a.setDatabase("server ipnumber or url","databasename","username","password")

 

[hasanaydin52]

Hello fasilosman,

Did you wrote sample code at first page?

And, you must change this line your properties.

'you can write named server or IP
a.setDatabase("server ipnumber or url","databasename","username","password")

 

[fasilosman]

Yes. this is how I changes

a.setDatabase("sql2.freesqldatabase.com","sql26250","sql26250","hR8!aZ7*")

but the error occurs in line 6

 

[rg58sma]

I cant connect

I cant connect, I used the 1.01 version but i cant connect.

"Records Not Found"

the IP or name of the pc is correct, the user and the pass too.

i dont know what is wrong

 

[rg58sma]

Dont work for me

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

' select query
L=a.Query("select * from clientes")
' 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


This is part of the code in B4A but only receive "Records Not Found