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

 

[rg58sma]

Dont work for me

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")

'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

 

[giga]

If your settings are correct. You may want to check the manifest and make sure there is a 4 instead of 14 for the SdkVersion. I had that problem and the 14 kept giving me "Record not Found"

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

Good Luck

 

[bnpmunson]

How to destroy/close database connection?

I did not notice any way to destroy/close the database connection. How does one go about making sure that the instance is closed and destroyed?

I need to have the database connection established/broken from within a service module that runs every 10 minutes to update a database table. I need to connect/insert/close each time I update as I don't want hundreds of always on connections to my server.

 

[andre.astafieff]

How can I run on the android 4.0 above?
For me only returns "no records" ...

Tested on 2.3.6 and it works normally. : Confuso:

 

[giga]

How can I run on the android 4.0 above?
For me only returns "no records" ...

Tested on 2.3.6 and it works normally. : Confuso:

Did you check the manifiest for this... Version = 4

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

 

[andre.astafieff]

Did you check the manifiest for this... Version = 4

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

ok. :sign0060:

 

[gokula]

hello, the library works greats, but is posible to modify this part of the source? I need the source please!!

metaData.getColumnName(i) Changed to metaData.getColumnLabel(i);

(for use as headers labels the " as customfieldname" from the query)

private void addHeaders()
{
try
{
ArrayList<String> arrayHeader = new ArrayList<String>();
arrayHeader.clear();
for (int i=1;i<columnCount+1;i++)
{
String cName = metaData.getColumnLabel(i);
arrayHeader.add(cName);
}
arrayResults.add(arrayHeader);
}
catch (SQLException e)
{
return;
}
}

my email is bisognisistemas@gmail.com

thanks!!!

 

[gokula]

I could achieve compile the library with the modification in the code to display the alias of the field, if anyone needs the modification mail me thanks

 

[Erel]

I don't recommend you to use this library in production. Networking should not be done on the main thread. In many cases it is also problematic to include the user name and password of your database server in the app.

You should instead use the PHP approach or RDC: http://www.b4x.com/android/forum/threads/31540

 

[frac3sco]

ciao ragazzi,
qualcuno posterebbe un esempio funzionate per accedere a mysql cn android 4.1.
ho provato tutti gli esempi del forum e mi restituisce l'errore che non trova i record. grazie

 

[Sergio Haurat]

Dear hasanaydin52 / Erel,
What would be the correct way to pass data from a data type List to Cursor. I need to improve this version, I am using Cursor and List data types. This is very cumbersome and messy.

Public Sub CheckTablesUpd As Boolean
  Dim blnUpd As Boolean = False
  Dim curdtSQLite As Cursor
  Dim strQuery As StringBuilder
  strQuery.Initialize
  strQuery.Append("select").Append(CRLF)
  strQuery.Append("*").Append(CRLF)
  strQuery.Append("from").Append(CRLF)
  strQuery.Append("cmn_tables_update")
  Try
    curdtSQLite = sqLiteConn.ExecQuery(strQuery.ToString)
    If curdtSQLite.RowCount = 0 Then
      Msgbox(GetRes("dbe_lUpdEmpty"), GetRes("app_name"))
      ExitApplication
    End If
  Catch
    Msgbox(GetRes("dbe_lSelect") & CRLF & LastException.Message, GetRes("app_name"))
    ExitApplication
  End Try
  Try
    Dim lstMySQL As List
    lstMySQL.Initialize
    lstMySQL = mySQLConn.Query(strQuery.ToString)
    If lstMySQL.Size = 0 Then
      Msgbox(GetRes("dbe_rUpdEmpty"), GetRes("app_name"))
      ExitApplication
    End If
  Catch
    Msgbox(GetRes("dbe_rSelect") & CRLF & LastException.Message, GetRes("app_name"))
    ExitApplication
  End Try
  Dim cntCol As Int = 0
  Dim aryData As List
  aryData = lstMySQL.Get(1)
  curdtSQLite.Position = 0
  For cntCol = 0 To curdtSQLite.ColumnCount - 1
    Dim strLocalTable, strRemoteTable As String
      strLocalTable = aryData.Get(cntCol)
      strRemoteTable = curdtSQLite.GetString2(cntCol) & ".0"
      If strLocalTable <> strRemoteTable Then
        blnUpd = True
      End If
  Next
  Return blnUpd
End Sub

 

[keirS]

Do you know you need to pay Oracle a licence fee for using the JDBC library commercially? IIRC its about 900 USD.

 

[giacomo-italy]

Hi! Help me.
I use this library, it is my favourite, and all works fine!
But it is possible to save and load an image in blob field of mysql table, with this library? How?
Is there something like ExecuteNonQuery2 method?
Is possible to use something like Cursor? How?
Thanks!!

 

[BarryW]

How to read blob using this library? Please help . . .

 

[imgsimonebiliato]

Please help,
I got this error

Parsing code.                           0.00
Compiling code.                         Error
Error parsing manifest script:
Line = 13, Word =
Command expected.

Line = 13 is:

AddPermission("android.permission.ACCESS_NETWORK_S TATE")

 

[Erel]

This line looks correct. Can you post the full manifest code?

 

[imgsimonebiliato]

This line looks correct. Can you post the full manifest code?

Here it is:

'This code will be applied to the manifest file during compilation.
'You do not need to modify it in most cases.
'See this link for for more information: http://www.b4x.com/forum/showthread.php?p=78136
AddManifestText(
<uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/>
<supports-screens android:largeScreens="true"
    android:normalScreens="true"
    android:smallScreens="true"
    android:anyDensity="true"/>)
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
'End of default text.

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

 

[Erel]

There is an extra space in the permission (S TATE). Maybe this is the problem.

 

[imgsimonebiliato]

I tried but I've got the same problem

 

[Erel]

I'm unable to reproduce it. I get no error with this code.

Can you upload your project?