TVPlanit Reboot

[wp]

You could add a temporary DBGrid to the form which you can link, via a TDataSource, to the EventsTable of the TVpBufDSDatastore. This is accessible by a non-published property which you connect to the Datasource at runtime. Do you see the events in the grid? Are all fields correctly populated?

If this is ok, is the Directory of the TVpBufDSDatastore set correctly?

You could also use the BufDSDatastore demo to open your manually create buf database.

And a final idea: The BufDSDatastore uses autoincremented ID fields. In this case your own procedure cannot write the ID values to the resource and event tables. You can fix this during writing of the resource table this way: Store the Resource ID which is found in your original table. Write the Resource record to the bufDS, but without the ResourceID field which is autocreated. Read the written record and store the ResourceID used along with the original value. Later when writing the Events table read the Resource ID used in your original table. Lookup the Resource ID written before and use this value when writing the Event record. Don't write the RecordID of the Event Table because it is autocreated as well.

[JD]

You could add a temporary DBGrid to the form which you can link, via a TDataSource, to the EventsTable of the TVpBufDSDatastore. This is accessible by a non-published property which you connect to the Datasource at runtime. Do you see the events in the grid? Are all fields correctly populated?

If this is ok, is the Directory of the TVpBufDSDatastore set correctly?

You could also use the BufDSDatastore demo to open your manually create buf database.

And a final idea: The BufDSDatastore uses autoincremented ID fields. In this case your own procedure cannot write the ID values to the resource and event tables. You can fix this during writing of the resource table this way: Store the Resource ID which is found in your original table. Write the Resource record to the bufDS, but without the ResourceID field which is autocreated. Read the written record and store the ResourceID used along with the original value. Later when writing the Events table read the Resource ID used in your original table. Lookup the Resource ID written before and use this value when writing the Event record. Don't write the RecordID of the Event Table because it is autocreated as well.

This is what I did. I created the Resource and Events by inserting data into a Resource & Event table on the server so the IDs are created automatically same as if I used an SQLite database. The IDs are properly created so the problem is not there. I want to retrieve the data and show it client-side. I have populated the BufDSDatastore and I know the data is there because RecordCount gives the right values.

I delete ALL *.db files after the program exits so that the program ONLY uses data retrieved from the remote server. Is there a command I need to use to show the Events?

Thanks,

JD

[wp]

This is what I did. I created the Resource and Events by inserting data into a Resource & Event table on the server so the IDs are created automatically same as if I used an SQLite database. The IDs are properly created so the problem is not there. I want to retrieve the data and show it client-side. I have populated the BufDSDatastore and I know the data is there because RecordCount gives the right values.

I believe you that the IDs are correct in the original database. But I think the issue is in the IDs in the db files - since here the ID fields have the autoinc flag tvplanit will recreate the IDs - and screw up everything. Maybe I should offer an option to use regular integer ID fields. But it restricts the original application I had in mind, and therefore I am a bit hesistant. We should make sure of validity of the IDs in the db files. If you don't mind could you upload the .db files here?

[JD]

Here they are: the Events & Resources table contain 2 & I records respectively with a ResourceID of 2 in each table.

Thanks,

JD

[wp]

The ResourcesTable has ResourceID 1, but the ResourceID is 2 in the events table (use attached program, copy both db files to its exe directory).

Please check your code, or post the procedure which creates the tables (in the beginning I got easily confused by the RecordID and ResourceID fields).

[JD]

The ResourcesTable has ResourceID 1, but the ResourceID is 2 in the events table (use attached program, copy both db files to its exe directory).

Please check your code, or post the procedure which creates the tables (in the beginning I got easily confused by the RecordID and ResourceID fields).

Thanks, I'll check the code. I populated & created the tables using SQL on the server.

[JD]

The code looks OK. Is there a possibility that the ResourceID field is being overwritten by TVPlanit?

JD

[JD]

I know where the bug is

Code: Pascal  [Select][+][-]

1. //
2. // LoadTVPResource
3. //
4. procedure TfrmMain.LoadTVPResource(
5.   var ADatastore: TVpBufDSDatastore; AJSON: string);
6. var
7.   intGridRow, intNumberOfSQLQueryRows: integer;
8.   // JSON related variables
9.   jsParser: TJSONParser;
10.   jsArrayRow: TJSONArray;
11.   jsObj: TJSONObject;
12.   //
13.   lastRes: TVpResource;
14. begin
15.   //
16.   if AJSON = EmptyStr then
17.     exit;
18.  
19.   //
20.   ADataStore.CreateTables;
21.  
22.   // create the json parser
23.   jsParser    := TJSONParser.Create(AJSON);
24.   //
25.   try
26.     // Parse the JSON data
27.     jsArrayRow  := jsParser.Parse as TJSONArray;
28.     //
29.     try
30.       // get the number of rows returned by the query in the JSON data object
31.       intNumberOfSQLQueryRows := jsArrayRow.Count;
32.       // Empty the dataset
33.       ADataStore.ResourceTable.Close;
34.       ADataStore.ResourceTable.Open;
35.       //AComboBox.Clear;
36.       // append the json rows to the grid
37.       for intGridRow := 0 to Pred(intNumberOfSQLQueryRows) { - 1} do
38.       begin
39.         // assign the object to jsObj
40.         jsObj := jsArrayRow.Objects[intGridRow];
41.         //
42.         try
43.           // All events related to a particular resource
44.           //
45.           with ADataStore.ResourceTable do
46.           begin
47.             //
48.             Append;
49.             //
50.             if not jsObj.Items[0].IsNull then
51.             begin
52.               FieldByName('resourceid').AsInteger         := jsObj.Items[0].AsInteger;
53.               ShowMessage(jsObj.Items[0].AsString);
54.               ShowMessage(FieldByName('resourceid').AsString);
55.             end
56.             else
57.               FieldByName('resourceid').Clear;
58.             //
59.             if not jsObj.Items[2].IsNull then
60.               FieldByName('description').AsString       := jsObj.Items[2].AsString
61.             else
62.               FieldByName('description').Clear;
63.             //
64.             if not jsObj.Items[3].IsNull then
65.               FieldByName('notes').AsString          := jsObj.Items[3].AsString
66.             else
67.               FieldByName('notes').Clear;
68.             //
69.             if not jsObj.Items[4].IsNull then
70.               FieldByName('imageindex').AsInteger         := jsObj.Items[4].AsInteger
71.             else
72.               FieldByName('imageindex').Clear;
73.             //
74.             if not jsObj.Items[5].IsNull then
75.               FieldByName('resourceactive').AsBoolean      := jsObj.Items[5].AsBoolean
76.             else
77.               FieldByName('resourceactive').Clear;
78.             //
79.             Post;
80.           end;
81.         finally
82.           //
83.           jsObj.Clear;
84.         end;
85.       end;      // for intGridRow := 0 to intNumberOfSQLQueryRows - 1 do
86.     finally
87.       //
88.       jsArrayRow.Free;
89.     end;          // try...finally (jsParser := TJSONParser.Create(AJSString))
90.   finally
91.     // free the jsParser
92.     FreeAndNil(jsParser);
93.   end;
94.   //
95.   ADataStore.ResourceTable.Active := True;
96.  
97.   // Connect the datastore to the newly added data
98.   if not ADatastore.Connected then ADatastore.Connected := True;
99.  
100.   //
101.   if ADatastore.Resources.Count > 0 then
102.   begin
103.     // Find the last resource ID value
104.     lastRes := ADatastore.Resources.Items[ADatastore.Resources.Count];
105.     ADatastore.ResourceID := lastRes.ResourceID;             [b]<-----------------  ResourceID is set to 1 here[/b]
106.   end;
107. end;
108.  

The bug is in

Code: Pascal  [Select][+][-]

1.   //
2.   if ADatastore.Resources.Count > 0 then
3.   begin
4.     // Find the last resource ID value
5.     lastRes := ADatastore.Resources.Items[ADatastore.Resources.Count];
6.     ADatastore.ResourceID := lastRes.ResourceID;  
7.   end;
8.  

ADatastore.ResourceID is reset to 1 in ADatastore.ResourceID := lastRes.ResourceID

JD

[wp]

Good that you found it. I still don't understand, though, why the autoinc fields can be written. Is this a feature of TBufDataset?

Maybe my update that I posted a minute ago is not necessary. TBufDSDatasource has a new property UseAutoIncFields. This is true by default. In cases where the AutoInc fields must be preserved set it to false BEFORE the tables are created (afterwards you cannot change it any more), For records created after importing your database random numbers will be selected for the ID fields.

If your corrected version is working fine now I'll resume the commit because it is not needed.

[JD]

Good that you found it. I still don't understand, though, why the autoinc fields can be written. Is this a feature of TBufDataset?

Maybe my update that I posted a minute ago is not necessary. TBufDSDatasource has a new property UseAutoIncFields. This is true by default. In cases where the AutoInc fields must be preserved set it to false BEFORE the tables are created (afterwards you cannot change it any more), For records created after importing your database random numbers will be selected for the ID fields.

If your corrected version is working fine now I'll resume the commit because it is not needed.

I'll update & test immediately.

[JD]

@wp Now the ResourceIDs match (see screenshot) but the events still do not show in the DayView. I'll keep digging..... 

[wp]

And when you run the previous version (r4905)? Does it produce matching IDs as well? You wrote that you found a bug in your code. I'd prefer to revert to r4905 because with the random IDs I have a bad feeling of what happens when you copy the changes in the local tables back to the server

As for the events not showing in the DayView: Did you set BufDSDatastore.Connected to true after importing the data into the BufDatasets? This would load data from the datasets into the internal lists and sets an internal Flag (FConnected). After that, call the code that I proposed above to select the ResourceID.

[JD]

And when you run the previous version (r4905)? Does it produce matching IDs as well? You wrote that you found a bug in your code. I'd prefer to revert to r4905 because with the random IDs I have a bad feeling of what happens when you copy the changes in the local tables back to the server

I overwrote svn 4905. Can you please leave the new property UseAutoIncFields and make it optional? Forcing its use/application is what reset the ResourceID to 1 because there is only one resource in the Resource table in BufDSDatastore.

 My server takes care of auto inc fields like this:

Resources table on the server: ResourceID (auto incremented by server)
Events table on the server: EventID (or RecordID in the DBGrid screenshot - auto incremented by server), ResourceID (foreign key link to Resource table)

I keep the server created IDs in memory & will overwrite any random ID generated by BufDSDatastore before committing the changes.

In addition, my resources are tied to employee contract ID which are never exposed for modification client side.

So I have no concerns over there.

As for the events not showing in the DayView: Did you set BufDSDatastore.Connected to true after importing the data into the BufDatasets? This would load data from the datasets into the internal lists and sets an internal Flag (FConnected). After that, call the code that I proposed above to select the ResourceID.

I set BufDSDatastore.Connected to true. See the code below the last lines of my LoadTVPEvents procedure.

Code: Pascal  [Select][+][-]

1.   // Events table is now populated so activate it, connect it and set the last resource ID
2.  
3.   //
4.   ADatastore.EventsTable.Active := True;
5.  
6.   // Connect the datastore to the newly added data
7.   if not ADatastore.Connected then ADatastore.Connected := True;
8.   //
9.   if ADatastore.Resources.Count > 0 then
10.   begin
11.     // Find the last resource ID value
12.     lastRes := ADatastore.Resources.Items[ADatastore.Resources.Count - 1];
13.     ADatastore.ResourceID := lastRes.ResourceID;
14.     //ResourceEventID := lastRes.ResourceID;
15.   end;
16.  

[wp]

I set BufDSDatastore.Connected to true. See the code below the last lines of my LoadTVPEvents procedure.

Code: Pascal  [Select][+][-]

1.   // Events table is now populated so activate it, connect it and set the last resource ID
2.  
3.   //
4.   ADatastore.EventsTable.Active := True;
5.  
6.   // Connect the datastore to the newly added data
7.   if not ADatastore.Connected then ADatastore.Connected := True;
8.   //
9.   if ADatastore.Resources.Count > 0 then
10.   begin
11.     // Find the last resource ID value
12.     lastRes := ADatastore.Resources.Items[ADatastore.Resources.Count - 1];
13.     ADatastore.ResourceID := lastRes.ResourceID;
14.     //ResourceEventID := lastRes.ResourceID;
15.   end;
16.  

Looks ok.

Did you try to load the generated db files into the bufdsdatastore demo in folder examples?

[JD]

I need clarification with something.

a) First I fill the VpBufDSDatastore Resource table and then I activate it
b) Next I fill the VpBufDSDatastore Events table and then I activate it
c) I then set VpBufDSDatastore connected to true
d) I then get the last ResourceID

Is this the correct order?

JD