* * *

Author Topic: Multiple Series from TDBGrid  (Read 1808 times)

phr0x

  • New member
  • *
  • Posts: 20
Multiple Series from TDBGrid
« on: June 11, 2018, 08:48:00 pm »
Hey there,

I am fairly new to databases and Pascal, just a heads up. I am developing a SQLite3 Database for Sensors.
Just recently I got my TDBGrid Component to Work with the TChart in a way that I send a SQL Select Statement and the Chart Component displays it right away ( did this with the DBchartsource component) . My issue is that I am only able to display one sensor this way. Because if I query for multiple Sensors the Values of the different Sensors will be mixed up in the one series I create within my chart.
So basically I want to send:
"Select * from database;"
To my Database, which has the values of two sensors in it
and then receive the answer in my Grid Component and create 2 lines ( for both sensors) in my Chart Component.
I hope I got my issue across and that you guys can help me out on this one.

Best Regards

wp

  • Hero Member
  • *****
  • Posts: 4828
Re: Multiple Series from TDBGrid
« Reply #1 on: June 11, 2018, 10:11:01 pm »
I don't understand what you mean with "different sensors will be mixed up in the one series". Do you mean that there are two sensors and they take measurements at different times?

Here is what I would do. I assume you have a database table - let's call it "SensorsTbl". It contains measurement data taken by two sensors. There are fields "SensorA" and "DateA" for the sensor A measurements, "SensorA" is the value (e.g. a temperature), and "DateA" the date and time when the measurement was taken. Similarly, there are fields "SensorB" and "DateB" for the other sensor.

To establish a connection from the data in the db table "SensorsTbl" to the chart you must first add a TDatasource component and set its "Dataset" property to "SensorsTbl".

In order to display the data of SensorA you must add a series to the chart and a DBChartsource. Link the series to the DBChartSource by entering the DBChartSource in the property "Source" of the series. Then make these changes in the DBChartSource:
  • Set "DataSource" to the TChartSource component that was added in the previous step
  • Set "FieldX" to the "DateA" field of the db table (we will plot "Date" on the x axis)
  • Set "FieldY" to the "SensorA" field (plot the values of column "SensorA" on the y axis).
  • Since "DateA" contains date/time values activate the option dcsoDateTimeX.
Repeat with SensorB, i.e. add a second series and a second DBChartSource to the chart and link them like in the previous step. The only differences are that "FieldY" must be set to "SensorB", and "FieldX" to "DateB".

When the data table opens (SensorTbl.Open, or SensorTbl.Active := true) the chart must plot the data.

The x axis will be labelled by the date numbers, but not by the usual date strings. To fix this, add a TDateTimeIntervalChartSource and link it to the property Marks.Source of the BottomAxis, and set Marks.Style to smsLabel (you may have to add TAChartUtils to "uses", otherwise Lazarus will not find the identifier smsLabel).

In the attachment you can find a simple demo based on this description. In contrast to your requirement I am using a simple TMemoryDataset here for simplicity, and dummy data are created for both series in the OnCreate method of the form.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

phr0x

  • New member
  • *
  • Posts: 20
Re: Multiple Series from TDBGrid
« Reply #2 on: June 12, 2018, 12:55:36 pm »
Thank you for your long answer. I think I did not describe the issue properly tho.

so my Table looks like this:
SensortypeSensorvalueTime
SensorA1212:00:00
SensorB1212:00:00

So basically I want to take the value from Sensor A to be displayed in a different series then the value from Sensor B.
I want to filter the Grid according to sensor, but I cant make a column for each Sensor in the DB because it's going to be far more then 2 Sensors in total.
So at the Moment what I do is that I set the Field Y to be Sensorvalue and with my SQL Statement I only display one sensor in my Grid and therefore in my Chart.
 But I want to be able to get the values of 2 sensors and display them correctly in my chart.

Best Regards

wp

  • Hero Member
  • *****
  • Posts: 4828
Re: Multiple Series from TDBGrid
« Reply #3 on: June 12, 2018, 01:35:10 pm »
For the first series (SensorA) use an SQL statement like "SELECT Time, SensoreValue, SensorType FROM your_table WHERE Sensortype = 'SensorA'". And for SensorB add another SQLDataset and use the same SQL statement except for the condition which must be "WHERE SensorType = 'SensorB'" now. (Note - the SQL may not be syntactically correct, but you should catch the idea).

Then link the SQLDataset containing the SensorA data to the DBChartSeries for the first series, and similarly with the SensorB SQLDataset.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

phr0x

  • New member
  • *
  • Posts: 20
Re: Multiple Series from TDBGrid
« Reply #4 on: June 12, 2018, 09:26:34 pm »
Thank you so much I got it to work that way  :D

I had to add another Datasource aswell but then everything worked out. Thanks again.

Maybe you guys can help me with another issue I have concerning my Chart. Currently I have divided the Field Date and Time. I now have the issue that my chart cant distinguish between the 01/01/2018 1 AM and 02/01/2018 1 AM. Because they are in different fields.
I already tried to put both together in one column with the strftime() function. The Problem I had with that was that within the Database the values were displayed correctly but within my gridfield it showed as 00:00:00.
So the data in the db would be '01-01-2018 01:00:00:000' but the Grid would show 00:00:00. So my application kind of never could read the actual date.
In my opinion that must be a problem with the definition of columntype, within sqlite3 I declared the column to be of the type TIME and then wrote into it with the strftime function.
Any Suggestions?

Best Regards

wp

  • Hero Member
  • *****
  • Posts: 4828
Re: Multiple Series from TDBGrid
« Reply #5 on: June 12, 2018, 09:54:39 pm »
Dates are difficult. Maybe it would be best if you could post your sqlite3 file (or part of it). Pack it into a zip which is accepted by the forum software for upload under "Attachments and other options"
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

phr0x

  • New member
  • *
  • Posts: 20
Re: Multiple Series from TDBGrid
« Reply #6 on: June 15, 2018, 02:54:35 pm »
Sooo i wrote a quick program for you with which you can just show the data in a grid, you jsut have to change the directory. And you can see how it doesnt show up as dates or time but as 00:00:00.
The values are in the table "xmpltbl".
 
I created the table with:
Create table xmpltbl(id integer primary key autoincrement, currentdate time);

I inserted the values with the following sql command:
Insert into xmpltbl (currentdate) values (strftime('%Y-%m-%d %H:%M%S', 'date(now)'));

So basically I just want the values to show up properly in the dbgrid component.

Best Regards

EDIT:

Well I guess I cant upload my application so its just the usual components, with a tdb grid.
The Source Code for the Query is:
Code: Pascal  [Select]
  1. procedure TForm1.ButtonClick(Sender: TObject);
  2. begin
  3. SQLQuery1.Close;
  4. SQLQuery1.SQL.Text:='select * from xmpltbl';
  5. SQLite3Connection1.Connected := True;
  6. SQLTransaction1.Active := True;
  7. SQLQuery1.Open;
  8. end;
  9.  
« Last Edit: June 15, 2018, 02:59:38 pm by phr0x »

wp

  • Hero Member
  • *****
  • Posts: 4828
Re: Multiple Series from TDBGrid
« Reply #7 on: June 15, 2018, 05:40:51 pm »
Strange. When I load the sensordata.db into LazDatabaseDesktop the grid displays 00:00:00 in the currentdate column. When I load the file into "SQLite Expert Personal" I see dates like "2018-02-06 16:53:47", with all records showing the same date and time except for the seconds increasing by 1 in every 2nd row - but you had created the field as TIME. When I create a db file with Lazarus using your create-sql the time column works fine.

Did you create the file in Lazarus? If you did you should extract the db creation procedure and show it here, or better, upload to the forum (often the errors are not in the extracted code shown).

In which time interval were the records added? Is the interval of about 0.5 seconds from record to record that what you would expect?

BTW: You CAN upload a program, but you must drop the compiled files (*.exe, *.ppu), only add *.pas, *.lfm, *.lpi and *.lpr to the zip.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

phr0x

  • New member
  • *
  • Posts: 20
Re: Multiple Series from TDBGrid
« Reply #8 on: June 15, 2018, 07:40:53 pm »
Quote
I created the table with:
Create table xmpltbl(id integer primary key autoincrement, currentdate time);

I inserted the values with the following sql command:
Insert into xmpltbl (currentdate) values (strftime('%Y-%m-%d %H:%M%S', 'date(now)'));

This is the Syntax of the Statements I used, I typed them into the linux console.
You mean I should create the table within my application?
And you are saying you can display the values correct within a tdbgrid component?

The times should only be like a second apart yes, I just sent the insert statement a few times within a short period.

wp

  • Hero Member
  • *****
  • Posts: 4828
Re: Multiple Series from TDBGrid
« Reply #9 on: June 15, 2018, 11:08:55 pm »
I do not understand what's different on the Linux console. But in the attachment you find a demo which creates your table in Lazarus and demonstrates three ways how to add records. They all show the time column correctly in the grid.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

phr0x

  • New member
  • *
  • Posts: 20
Re: Multiple Series from TDBGrid
« Reply #10 on: July 08, 2018, 10:12:58 pm »
Thank you, you helped me a lot with your example.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus