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.

 

[Erel]

DBUtils was updated to support the changes in jSQL library.

 

[marco.canta]

where I find I get the library jsql ?

Thanks

 

[udg]

Hi marcoffio,

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

Umberto

 

[aklisiewicz]

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 ?

 

[Erel]

DBUtils is not a library. It is a code module.

You should use Project - Add Existing Module and add DBUtils.bas to your project.

Usually the most updated core libraries are included in the installation.

 

[aklisiewicz]

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

 

[Gabino A. de la Gala]

Hello. I'm trying to use progress indicator like this:

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?

 

[Erel]

You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries.

 

[Gabino A. de la Gala]

You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries.

Can I use it in BJA?.

 

[Erel]

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?

 

[Gabino A. de la Gala]

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.

 

[Erel]

Adding many rows to TableView will take some time. Do you really want to show so many rows? It might not be convenient to the user.

The best way to implement pagination is in the query itself. You can add an id column to each row.

 

[udg]

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:

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]

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]

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]

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:

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:

For i = 0 To cur.ColumnCount - 1
     sb.Append("<th>").Append(GetColumnLabel(cur,i+1)).Append("</th>")
Next

Umberto

 

[ValDog]

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

 

[Erel]

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]

Thanks Erel - good to know. I must have done something else that was causing my problem.