Nov 18, 2013 Replies: 38 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. Attachments DBUtils.zip DBUtils.zip 6.6 KB · Views: 2,337 Last edited: Mar 30, 2018 Click to expand...
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 B4X founder Staff member Licensed User Longtime User Dec 1, 2013 #2 DBUtils was updated to support the changes in jSQL library.
marco.canta Active Member Licensed User Longtime User Dec 7, 2013 #3 where I find I get the library jsql ? Thanks
udg Expert Licensed User Longtime User Dec 7, 2013 #4 Hi marcoffio, library jSQL is part of the standard installation of B4J. You can find it on the Libs tab. Umberto
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 Jan 2, 2014 #5 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 ?
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 B4X founder Staff member Licensed User Longtime User Jan 2, 2014 #6 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.
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 Active Member Licensed User Longtime User Jan 5, 2014 #7 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: Jan 6, 2014
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 Well-Known Member Licensed User Longtime User Feb 20, 2014 #8 Hello. I'm trying to use progress indicator like this: Code: Copy 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?
Hello. I'm trying to use progress indicator like this: Code: Copy 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 B4X founder Staff member Licensed User Longtime User Feb 20, 2014 #9 You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries.
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 Well-Known Member Licensed User Longtime User Feb 24, 2014 #10 Erel said: You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries. Click to expand... Can I use it in BJA?.
Erel said: You will need to add DoEvents in ExecuteTableView loop. Another option is to use async queries instead of the blocking queries. Click to expand... Can I use it in BJA?.
Erel B4X founder Staff member Licensed User Longtime User Feb 24, 2014 #11 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?
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 Well-Known Member Licensed User Longtime User Feb 24, 2014 #12 Erel said: 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? Click to expand... 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 said: 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? Click to expand... 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 B4X founder Staff member Licensed User Longtime User Feb 25, 2014 #13 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.
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 Expert Licensed User Longtime User Jun 27, 2014 #14 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: Code: Copy 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
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: Code: Copy 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
B billzhan Active Member Licensed User Longtime User Jun 28, 2014 #15 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".
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 Jun 28, 2014 #16 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
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 Jun 28, 2014 #17 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: Code: Copy 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: Code: Copy For i = 0 To cur.ColumnCount - 1 sb.Append("<th>").Append(GetColumnLabel(cur,i+1)).Append("</th>") Next Umberto
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: Code: Copy 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: Code: Copy 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 Oct 14, 2014 #18 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
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 B4X founder Staff member Licensed User Longtime User Oct 15, 2014 #19 ValDog said: Apparently, one has to use the B4J Designer to Generate Members. Just writing ""Private TableView1 As TableView" under Process Globals doesn't cut it... Click to expand... 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 said: Apparently, one has to use the B4J Designer to Generate Members. Just writing ""Private TableView1 As TableView" under Process Globals doesn't cut it... Click to expand... 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 Oct 15, 2014 #20 Thanks Erel - good to know. I must have done something else that was causing my problem.
