B4J Question DButils multicolumn handling

ThRuST

Well-Known Member
Licensed User
Longtime User
I want to use DButils to list multicolumn contents from a SQLite database in a tableview.
However so far I can only list the query into the first column, even though I initialize with mylist = tableview.items to associate the list data with the tableview columns. My db holds 7 columns, so how to work with lists with a 7 column database contents through dbutils? This keeps my busy.

Parts of my code
B4X: 
Dim tableView1 As TableView
Dim mylist As List
Private SQL1 As SQL

mylist.Initialize
mylist = tableView1.Items

SQL1.InitializeSQLite("c:\", "test.sqlite", False)

DBUtils.ExecuteTableView(SQL1, "SELECT DocName FROM UserTable", Null, 0, tableView1)
  
    For Each i As String In mylist
    Log(i)
    Next

Any clues are very appreciated, thanks.
 
Sort by date Sort by votes

stevel05

Expert
Licensed User
Longtime User
You are specifying only one column to select:

B4X: 
"SELECT DocName FROM UserTable"

So only data in the column Docname will be returned.

Try

B4X: 
"SELECT * FROM UserTable"
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
Try
"SELECT * FROM UserTable"
Click to expand...



Thanks Steve, that worked The image shows my SQLite database which I've used with a prototype code snippets tool that I wrote 3 years ago in C sharp, so I am porting that application to B4J. It does not look so good and have to be tweaked, but it's a milestone that I've come this far with my work. Actually it helped me to study your code earlier today. I'm impressed by how well you've understood B4J and how you organize your code in code modules. Nice and tidy code, well done.
What's your suggestion when to use ExecuteList, ExecuteMap and ExecuteMemoryTable? I've noticed you used the last one in your project.
My B4j project progresses well, and I usually separate my examples untill I cover them enough to implement the functionality in my project. B4J is great fun and I am green of envy from studying your code
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
btw my for each loop return this


Any suggestion how to read each column in a proper (readable) way?
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
Thanks for the nice comments, I like to try and keep my code tidy. It helps others understand it and me maintain it. On the forums a year may go past from the time code is shared to questions being asked, so it helps if it's easily readable.

I've been using B4x in one form or another for almost 6 years, so I've been able to learn each piece that has been added in an orderly fashion. I do feel for people starting out now as the environments are (by necessity) large, and there is a lot to learn.

When to use each method depends on what you want to do with the data. Most of the methods return one record. ExecuteTableView and ExecuteMemoryTable return multiple records. If the data were ready to present straight from the table, I'd use ExecuteTableView, but more often that note you want to do something different with it, so I'd use ExecuteMemoryTable.

To answer your second question the [Ljava.lang.String@..... is a pointer to a string array. I guess it's from ExecuteMemoryTable so each element in the list is an array and each element of the array is a column in a record, so it would be.

B4X: 
For Each arr() as String in L
  Log($"${arr(0)} ${arr(1)} ${arr(2)}"$) 'etc
Next

Hope it helps
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
For Each arr() as String in mylist
Log($"${arr(0)} ${arr(1)} ${arr(2)}"$) ' etc
Next
Click to expand...

Thanks, I've only been learning B4J for about 3 month, however I've been using B4A for more than 2 years so that makes it easier to work with B4i and B4J.
B4X is easy to pick up after nearly 15 years with VB6/VB.NET.

* Do you mind explaining the for each solution because it will help learning what's happening.
* How would you store each db column data in an array of lists instead of the log? (which could be useful to keep the db contents in memory).

Below is what the readable output looks like. (it's some of my personal stuff I've covered if anyone is interested, this is what my project is all about)

 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
OK no problem, On each iteration of the loop a new Array (arr()) is created and populated with a row from the list (L).

If you want to keep the data in memory, I would suggest you use a Type to hold each record and store them in a list. A list is simpler than an array as you don't need to know how many items there will be in advance. I don't know what your 7 field names would be but you can easily change them.

In Process_Globals:

B4X: 
    Type MyDataType ( _
        ID As String, _
        Environment As String, _
        Task As String, _
        Field4 As String, _
        Field5 As String, _
        Field6 As String, _
        Field7 As String)
       
        Dim MyDataList As List

Then where you are loading the data:
B4X: 
MyDataList.Initialize
    For Each Arr() As String In L
        Dim MDT As MyDataType
        MDT.Initialize
        MDT.ID = Arr(0)
        MDT.Environment = Arr(1)
        MDT.Task = Arr(2)
        MDT.Field4 = Arr(3)
        MDT.Field5 = Arr(4)
        MDT.Field6 = Arr(5)
        MDT.Field7 = Arr(6)
       
        MyDataList.Add(MDT)
    Next


Then to read one:

B4X: 
Dim MDT As MyDataType = MyDataList.Get(10) ' Or whichever you want to read
Log($"${MDT.ID} ${MDT.Environment} ${MDT.Task}"$) 'etc
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
Thanks, I will investigate types with B4J further. It looks a bit complex with all the ${} signs
Even though it's great to learn from I am not sure if it's good practice to store and work with database contents in memory, other than to just allow the user to make changes to the database and then have it reloaded into the tableview. I'm trying to find 'the best way' or at least one way that is 'the preffered way' so to speak.
Since you are more experienced with B4J and DButils it's of great value to know your advice and recommendations how to properly work with database handling.
DButils seems like a convenient module for viewing database contents prefferably with the tableview control, at least that's my own experience. The mic is yours Steve.
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
I fine the smart strings easier to use that concatenating strings for output:
B4X: 
Log($"${MDT.ID} ${MDT.Environment} ${MDT.Task}"$)

Could just as easily have been
B4X: 
Log(MDT.ID  & " " & MDT.Environment & " " & MDT.Task)

DBUtils is a very useful Code module, however it is still sometimes easier to use the SQLite queries directly, especially the ExecQuerySingleResult and variants. You should become familiar with DBUtils and the direct SQLite calls and use the one that is more appropriate to the task in hand.

Design of the database is critical to getting the data in and out in an efficient manner, so you need to consider how the data is coming in, and how you want to access the data and create tables in a structure that makes it simplest. If you are using multiple tables, become familiar with the JOIN facility of SQLite.

Invariably if you are writing something new 'on the fly' things will change and you may need to change the database structure before you have finished. Which is why planning is Key. If you can get it as close as possible on the first attempt, it can save a lot of time and data re-entry.
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
Thanks, I will organize my code in code modules since it seems like the whole point with modules in the first place, and after studying your code.
I had no idea about those smart strings in B4X, those reminds me of string handling in C sharp. Good planning is really something to considder every time.
Before wrapping up this post I have summed up some questions that will make life easier when working with DButils and databases. It will help others as well.


 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
I answer the second question myself, in case it might help someone else

tableView1.SetColumnVisible(x, False)
Click to expand...
Were x is the column to hide. 0 for the first column, 1 second column etc.
And last parameter is visible True/False.
 
Upvote 0

stevel05

Expert
Licensed User
Longtime User
Is it possible to work the same way with remote databases i.e. a mySQL remote database with B4J-DButils-MySQL-TableView, as with a local SQLite database?
Click to expand...

I doubt it, I don't know mySQL but DBUtils was written for SQLite. I don't know if there is any compatibility in the command syntax, but you could create a DBUtils module to work with it using code from the existing version.

Can you give examples on when to use JOIN, you mentioned that it's especially useful when working with multiple columns?
Click to expand...

It would not be a 5 minute job, there are many good tutorials and examples already available on the internet, just be aware that SQLite has facilities to make accessing multiple tables easier and do a Google search when you need it. As always, if you get a problem with implementation, ask on the forums.

Happy New Year to you and all.
 
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
Thanks for pointing this out. I want to share how I could read the first column as hidden, by clicking on the tableView1 (pay attention to the case sensitive name).
I know that there's B4J users in this forum (including myself) who need DButils to support both SQLite and MySQL databases. It would be great if you Steve, or someone
else just as experienced can implement support for mySQL (remote database) support in DButils. Or perhaps as you said, write a custom DButilsMySQL module.
Many of us would be very thankful for the one who do this. Please please please Steve help us with this, and have Erel include it in the new B4J update. I can't wait.

EDIT: I forgot to mention users who set up their own RDC2 server will need DButils to support SQLite and MySQL.

Here's how to read hidden column data when clicking on the tableview
B4X: 
Sub tableView1_MouseClicked (EventData As MouseEvent)
    fx.Msgbox(MainForm, tableView1.SelectedRowValues(x), "Hidden column data")
End Sub
where x is the column to read from.
 
Last edited:
Upvote 0

stevel05

Expert
Licensed User
Longtime User
I'm afraid I don't know mySQL and currently have no need for it. I couldn't justify spending the time required to learn to use it. Perhaps someone already conversant in it may take up the challenge, if it's even possible.
 
Last edited:
Upvote 0

ThRuST

Well-Known Member
Licensed User
Longtime User
Perhaps Erel or someone else can give us some advice about DButils-mySQL-tableview. New commands can better be baked into an updated version of DButils by the author of DButils, whoever it was.. whoever it was it is great, but can be even better
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
TableView <- DButils.ExecuteTableView -> by _MouseClicked
Replies
1
Views
2K
Erel
  • Article
Share My Creation TableView: Magical Inline Editing
Replies
11
Views
7K
Mashiane
  • Locked
  • Article
B4J Tutorial TableView Tutorial
2
Replies
33
Views
62K
Erel
E
  • Question
B4J Question MySQL to TableView
Replies
10
Views
7K
killiak
  • Article
B4J Tutorial DBUtils Example
2
Replies
38
Views
38K
ThRuST
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…