Charting Data from SQLite Database

[JTolhurst]

Hello,

I have been reading through the SQLite Tutorial and the Charts Framework tutorial.

What I'd like to do is combine the two - pull the data out of the database and into a barchart. So I have created a table called "Orders" in a database called 1.db. There are three column names: "Id", "month" and "sales"
I want to populate the bar chart with "month" and "sales"

The Sub code from your bar chart example is:

Sub CreateBarsTab
   'It is not possible to draw directly on the panel that acts as the Tab page.
   'This panel doesn't have an absolute size as its size changes to fit the TabHost available size.
   'For that reason we add a panel to a panel.
   Dim p As Panel
   p.Initialize("")
   pnlBars.Initialize("pnlBars")
   p.AddView(pnlBars, 0, 0, 95%x, 100%y - 100dip)
   TabHost1.AddTab2("Bars Chart", p)
   Dim BD As BarData   
   BD.Initialize
   BD.Target = pnlBars
   BD.BarsWidth = 15dip
   BD.Stacked = False
   Charts.AddBarColor(BD, MakeTransparent(Colors.DarkGray, 230)) 'First bar color
   Charts.AddBarColor(BD, MakeTransparent(Colors.Gray, 230)) 
   Charts.AddBarColor(BD, MakeTransparent(Colors.LightGray, 230)) 
   For i = 1 To 4
      Charts.AddBarPoint(BD, 2005 + i, Array As Float(Rnd(0, 1000), Rnd(0, 1000), Rnd(0, 1000)))
   Next
   Dim G As Graph
   G.Initialize
   G.Title = "Bars Chart"
   G.XAxis = "Year"
   G.YAxis = "Values"
   G.YStart = 0
   G.YEnd = 1000
   G.YInterval = 100
   G.AxisColor = Colors.Black
   Charts.DrawBarsChart(G, BD, Colors.White)
End Sub

I assume I need to replace the following code with some code that would pull in the data from my database.

For i = 1 To 4
      Charts.AddBarPoint(BD, 2005 + i, Array As Float(Rnd(0, 1000), Rnd(0, 1000), Rnd(0, 1000)))
   Next

If anyone is able to give me a quick pointer (or example of how I would populate the data from the database example) I would be very grateful.
I'm guessing I would need to use ExecQuery but can't work out quite how to do it.

Thank You

 

[Penko]

Hello !

I will try to explain the basics here to you but you will have to do further work in order to get it to work:

This code says that we will add three different bar columns

  Charts.AddBarColor(BD, MakeTransparent(Colors.DarkGray, 230)) 'First bar color
    Charts.AddBarColor(BD, MakeTransparent(Colors.Gray, 230)) 
    Charts.AddBarColor(BD, MakeTransparent(Colors.LightGray, 230))

Here you start populating the data. I haven't checked the syntax of the Charts but I assume:
2005 + i is X axis. You increment i from 1 to 4, so you will have data for years 2006-2010.
The next parameter is an array of three values, these are the values for the three columns we talked about above.

  For i = 1 To 4
        Charts.AddBarPoint(BD, 2005 + i, Array As Float(Rnd(0, 1000), Rnd(0, 1000), Rnd(0, 1000)))
    Next

Your point was right, this is the segment you have to change.

But you have to tell us what way you want to populate the way, what do you want to have on the X axis and what on the Y axis and how many bar charts you want at all?

Regards the data extraction from DB. I suggest that you use the DBUtils helper module written by Erel because it really simplifies your job. Alternatively, you can use the default method:

Dim cursor1 as Cursor
cursor1 = ExecQuery("SELECT * FROM Orders")

For i = 0 To cursor1.RowCount - 1
curso1r.position = i

Dim firstColumn As String : firstColumn = cursor1.GetString("FirstColumn")
' here add your other columns

' here is also the place where you will probably add your data to the charts.
' you have to move the block for the charts population here. The code is far from optimized one 
' but I just wanted to give you some directions so you start working :)

Next

cursor1.Close

 

[klaus]

Here you are.

     ' set the bar colors
    Charts.AddBarColor(BD, MakeTransparent(Colors.Red, 230)) 'First bar color

    ' Add the items. 
    Dim i As Int
    Dim Curs As Cursor
    
    Curs = SQL1.ExecQuery("SELECT * FROM Orders")
    For i = 0 To Curs.RowCount - 1
        Curs.Position = i
        Dim Sale0 As Double
        Dim Sale1 As Float
        Sale0 = Curs.GetDouble("sales")        ' gets the sales value
        Sale1 = Sale0                                                        ' converts ouble to Float
        Charts.AddBarPoint(BD, Curs.GetString("month"), Array As Float(Sale1))
    Next
    Curs.Close
    
    ' Initialize the graph object
    ' Set the bar chart parameters
    Dim G As Graph
    G.Initialize
    G.Title = "Sales"
    G.XAxis = "Month"
    G.YAxis = "Sales value"
    G.YStart = 0                        ' min vertical scale
    G.YEnd = 5000                        ' max vertical scale
    G.YInterval = 1000            ' vertical scale divisions
    G.AxisColor = Colors.Black
    Charts.DrawBarsChart(G, BD, Colors.White)

Attached a working example.

Best regards.

 

[JTolhurst]

Thank You Penko and Klaus, Your posts/Answers were both very helpful

 

[achtrade]

How can I do this for line chart ?

 

[achtrade]

I have this table tblHistory:
Date | Sales
08/02/2014 | 55
08/14/2014 | 60
...

I'm doing this, but it isn't working:

    Activity.LoadLayout("Chart")
    If FirstTime Then
      
    End If
    ' Initialze the panel to display the lines chart
    pnlLines.Initialize("pnlLines")
    Activity.AddView(pnlLines, 10%x, 10%y, 80%x, 80%y)
  
    ' Initialize the line data
    Dim LD As LineData
    LD.Initialize
    LD.Target = pnlLines
  
    ' Set the line colors  
    Charts.AddLineColor(LD, Colors.Red) 'First line color
  
    ' Add the items.
    Dim i As Int
    Dim Curs As Cursor
  
    Curs = Main.SQL1.ExecQuery("SELECT date,sales FROM tblHistory")
    For i = 0 To Curs.RowCount - 1
        Curs.Position = i
        Dim Sale0 As Double
        Dim Sale1 As Float
        Sale0 = Curs.GetDouble("sales")        ' gets the sales value
        Sale1 = Sale0                                                        ' converts ouble to Float
        Charts.AddLinePoint(LD, Curs.GetString("date"), Array As Float(Sale1), i Mod 90 = 0)
    Next

I'm getting this error:

b4a.example.charts:_addlinepoint, [anywheresoftware.b4a.BA@b4de74a0, [Canvas=anywheresoftware.b4a.objects.drawable.CanvasWrapper@b4e0d5e8, Target=(BALayout): Left=32, Top=40, Width=256, Height=325, LinesColors=(ArrayList) [-65536]


, Points=(List) Not initialized, IsInitialized=true], 08/08/2014, [F@b4d08840, true]
Error occurred on line: 164 (charts)
java.lang.IllegalArgumentException: argument 4 should have type float, got float[]


at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
at b4a.example.chart.afterFirstLayout(chart.java:98)
at b4a.example.chart.access$100(chart.java:16)
at b4a.example.chart$WaitForLayout.run(chart.java:76)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5017)
at java.lang.reflect.Method.invokeNative(Native Method)


at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
at dalvik.system.NativeStart.main(Native Method)
** Activity (chart) Resume **

 

[JohnD]

You are using a wrong parameter type for AddLinePoint. It should be:

Charts.AddLinePoint(LD, Curs.GetString("date"), Sale1, i Mod 90 = 0)

There could be other issues as well.

 

[klaus]

You should use this line:

Charts.AddLinePoint(LD, Curs.GetString("date"), Sale1, True)

Replace i Mod 90 = 0 by True to ensure that all dates are displayed.
If the value of the last parameter is True then the X value will be displayed otherwise not.
If you don't want to display all dates you need to replace i Mod90 = 0 by another function.
If you want to display only every fifth date the function would be i Mod 5 = 0.

 

[achtrade]

Perfect. Now it's working like a charm.

Thank you JohnD and Klaus because I was about to ask that too