B4J Tutorial DBUtils Example

Latest version available here: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/

This example demonstrates how DBUtils module can be used in B4J.

DBUtils was modified to match the slightly different API of B4J jSQL library.

Other changes:
- ExecuteSpinner and ExecuteListView were removed. Instead there is an ExecuteList methods that can be used with ComboBox and ListView.
- ExecuteHtml was removed. Currently there isn't a node similar to WebView in B4J (it will be added in the near future).
- ExecuteTable was added. It fills the result set in a TableView.

SS-2013-11-18_12.51.51.png
 

Attachments

  • DBUtils.zip
    6.6 KB · Views: 1,740
Last edited:

udg

Expert
Licensed User
Longtime User
Hi marcoffio,

library jSQL is part of the standard installation of B4J.
You can find it on the Libs tab.

Umberto
 

aklisiewicz

Active Member
Licensed User
Longtime User
I copied extracted folder to C:\Program Files\Anywhere Software\Basic4android\Libraries but I cannot see the LIB on the list in IDE.
What is the proper structure for Libs ? Can each Lib be placed in its own folder or they have to start from the \Libraries folder ?
does this Lib show in the panel as DBUtils or as SQL ?

BTW. How can I quickly verify if all my Libs are up to date ?
Arthur

any sugestions ?
 

aklisiewicz

Active Member
Licensed User
Longtime User
thanks.

I just downloaded SCROLLVIEW2 lib and have the same problem.
Extracted files and copied the folder to ...\Basuc2Android\Libraries\ScrollView2Dv1.1\....

but I cannot see the LIB on the list

I'm also trying to run the SQLiteDV_2D example and up[on compilation I get (missing library error)

Arthur
 
Last edited:

Gabino A. de la Gala

Active Member
Licensed User
Longtime User
Hello. I'm trying to use progress indicator like this:

B4X:
progress.Visible = True

DBUtils.ExecuteTableView(sql1, "SELECT Codigo, RazonComercial, RazonFiscal, Cuota, Fecha from Cli" & EmpresaSeleccionada , Null, 0, TableView1)

progress.Visible = False

But it doesn't work.

Can I do anything?
 

Gabino A. de la Gala

Active Member
Licensed User
Longtime User
My mistake. Didn't see that we are in the B4J forum...

There is no DoEvents keyword in B4J. How many rows are you reading?
Don't worry.

Depends. 11.000, 23.000, etc.

I am thinking about paginate, but i think I need something like cur.RecordCount to can calculate how many pages I need without to have to get before the number of rows in another query.

Sorry for my poor english.
 

udg

Expert
Licensed User
Longtime User
Hi all,
I am using DBUtils code module to easily produce table data to be inserted in an existing HTML page.
Function ExecuteHtml produces the expected result but takes in no account my renaming of an ugly column name; I guees this depends on how ResultSet.GetColumnName works.
A short example:
B4X:
tabdata = DBUtils.ExecuteHtml(sql1,"SELECT t2.mat_denom as Materia, t1.pagina from manuale as t1 ...",Null,20)
The above renders as:
mat_denom --!-- pagina
instead of
Materia --!-- pagina
(--!-- fake separator to easy reading)

Is there any way to influence how GetColumnName returns its value?
Should I modify the html header code in ExecuteHtml to take in account for this situation?

TIA

Umberto
 

billzhan

Active Member
Licensed User
Longtime User
Your sql code should return GetColumnName Materia --!-- t1 (columnname after keyword as)

I think there are may something wrong with your sql. Note that you have two tables in the query, make sure that there are condition like "WHERE t1.id=t2.id".
 

udg

Expert
Licensed User
Longtime User
Thank you billzhan for your reply.
I tested my complete sql statement with phpMyadmin and it works as expected (btw, it is a join). It's just using the ExecuteHtml sub that I get the real column name instead of the one indicated in the sql statement.
The sql fragment posted was intended only to show the relevant part involved in my request.

Today I'm gonna try to modify ExecuteHtml to "print" the exact data as returned after the select statement. Eventually I'll code column titles by hand.

Umberto
 

udg

Expert
Licensed User
Longtime User
Hi all,
it seems I solved the problem showed in post #14 above.
Reading java.sql documentation I found the object ResultSetMetaData which has function getLabelName (and also that getColumnName as returned by Resultset.GetColumnName used in post #14).
Poorly using the JavaObject library I wrote the following function:
B4X:
Public Sub GetColumnLabel(rs1 As ResultSet, column As Int) As String
  Dim rs As JavaObject
  rs.InitializeStatic("java.sql.ResultSet")
  rs = rs1
  Dim rsmd As JavaObject
  rsmd = rs.RunMethodJO("getMetaData",Null)
  Return rsmd.RunMethod("getColumnLabel", Array(column))
End Sub
Bear in mind that java counts columns from 1 not 0, so using the above function in DBUtil.ExecuteHtml to set column labels, we should write:
B4X:
For i = 0 To cur.ColumnCount - 1
     sb.Append("<th>").Append(GetColumnLabel(cur,i+1)).Append("</th>")
Next

Umberto
 

ValDog

Active Member
Licensed User
Longtime User
Having a problem with ExecuteTableView:

Relevant code:

SQL1.InitializeSQLite(MF_File.FileDir(sFileNameS), MF_File.FileName(sFileNameS), True)
DBUtils.ExecuteTableView(Main.SQL1, "SELECT IDS, DeviceID FROM Samples", Null, 0, TableView1)

Dialog shows a tableview which says "No content in table" - table does have records. What am I missing?


UPDATE: I guess I stumbled across the solution. Apparently, one has to use the B4J Designer to Generate Members. Just writing ""Private TableView1 As TableView" under Process Globals doesn't cut it...
 
Last edited:

Erel

B4X founder
Staff member
Licensed User
Longtime User
Apparently, one has to use the B4J Designer to Generate Members. Just writing ""Private TableView1 As TableView" under Process Globals doesn't cut it...
That is not correct. There is no difference between manually adding the line or letting the designer to add it. The result is exactly the same.
 

ValDog

Active Member
Licensed User
Longtime User
Thanks Erel - good to know. I must have done something else that was causing my problem.
 
Top