B4J Tutorial DBUtils Example

Discussion in 'B4J Tutorials' started by Erel, Nov 18, 2013.

Similar threads

B4J Library [B4X] DBUtils 2
B4J Tutorial SQL Tutorial
B4J Tutorial TableView Tutorial
B4A Tutorial [B4X] SQL with Wait For
B4J Question SQL to Excel?
  1. Erel

    Erel Administrator Staff Member Licensed User

    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.


    Attached Files:

    Last edited: Mar 30, 2018
    Johan Hormaza, tdocs2, aeric and 5 others like this.
  2. Erel

    Erel Administrator Staff Member Licensed User

    DBUtils was updated to support the changes in jSQL library.
  3. marco.canta

    marco.canta Active Member Licensed User

    where I find I get the library jsql ?

  4. udg

    udg Expert Licensed User

    Hi marcoffio,

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

  5. aklisiewicz

    aklisiewicz Active Member Licensed 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 ?

    any sugestions ?
  6. Erel

    Erel Administrator Staff Member Licensed User

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

    aklisiewicz Active Member Licensed User


    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)

    Last edited: Jan 6, 2014
  8. Gabino A. de la Gala

    Gabino A. de la Gala Active Member Licensed User

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

    progress.Visible = True

    "SELECT Codigo, RazonComercial, RazonFiscal, Cuota, Fecha from Cli" & EmpresaSeleccionada , Null0, TableView1)

    progress.Visible = 
    But it doesn't work.

    Can I do anything?
  9. Erel

    Erel Administrator Staff Member Licensed User

    You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries.
  10. Gabino A. de la Gala

    Gabino A. de la Gala Active Member Licensed User

    Can I use it in BJA?.
  11. Erel

    Erel Administrator Staff Member Licensed 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?
  12. Gabino A. de la Gala

    Gabino A. de la Gala Active Member Licensed User

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

    Erel Administrator Staff Member Licensed User

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

    udg Expert Licensed 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:
    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?


  15. billzhan

    billzhan Active Member Licensed 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".
  16. udg

    udg Expert Licensed 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.

  17. udg

    udg Expert Licensed 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:
    Public Sub GetColumnLabel(rs1 As ResultSet, column As Int) As String
    Dim rs As JavaObject
      rs = rs1
    Dim rsmd As JavaObject
      rsmd = rs.RunMethodJO(
    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
  18. ValDog

    ValDog Active Member Licensed 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: Oct 14, 2014
  19. Erel

    Erel Administrator Staff Member Licensed User

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

    ValDog Active Member Licensed User

    Thanks Erel - good to know. I must have done something else that was causing my problem.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice