Save or return database query results as JSON
JSON has become the common format to store structured information in text files. Further, web services tend to return JSON these days. XML seems to have lost the battle as it generates too much meta-information nobody needs. In addition, developers hardly need the validation of XML documents which is not possible to implement using JSON.
Delphi’s database framework FireDAC has plenty of means to generate JSON content of tables and queries. In this blog post, I will introduce two approaches:
- Using methods of TFDDataset
- Using TFDBatchMoveJSONWriter
TFDDataset and derived classes (e.g. TFDQuery)
Datasets in FireDAC offer a lot of methods to persist results in files on the local file system. You can store data in a binary format, XML, or JSON. The method you use is called SaveToFile. The file format is specified in a parameter as this:
MyQuery.SaveToFile( 'c:\temp\mydata.json', sfAuto );
The example above uses the format sfAuto.That means, FireDAC uses the filename to determine the file-format. In this case, JSON would be used. To make certain JSON is used, no matter the file name, use this:
MyQuery.SaveToFile( 'c:\temp\mydata.dat', sfJSON );
In order to use any of the JSON functionality, you need to drop a TFDStanStorageJSONLink component on the form. Using this component will take care of adding all the needed units to your application. You may, of course, add the unit FireDAC.Stan.StorageJSON manually.
Determine what information is put into JSON
You can configure what information is put into the JSON file using TFDConnection:
Look for the property named StoreItems. Here you can determine what kind of data is to be stored in the JSON file:
- siMeta: Meta information to describe the result set as field names and types.
- siData: The actual data of the result set.
- siDelta: Changes that have been made to the result set since it was retrieved from the database. This is usually the case if you have an application that pulls data from the database and the application is being used offline without a database connection. The delta information allows you to update the database when going back online.
- siVisible: When the dataset is filtered, only the filtered records are stored if set to true.
The produced JSON is normally optimized for space, i.e. there are no white-spaces added. This makes it hard to read for the human eye. If you want to be able to read your JSON on screen easily and storage space is not an issue, set the StorePrettyPrint propery to true.
At first sight this seems to be a good solution for all use cases. Sadly, FireDAC produces a lot of content in the JSON file that is only needed to reload the data into a FireDAC query or table. Thus, even when just storing data, there is some overhead that makes it difficult to use with other applications. The following screenshot shows that in order to get to the data you need to follow the path of FDBS/Manager/TableList/RowList. Then, there is original values and new values to handle changes.
This component focuses on one thing: data and only data. Thus, if you leave the world of FireDAC with your JSON, use this. In particular, when using JSON for web services, this is the component to use to produce your JSON from datasets.
The setup is a bit more complicated which calls for a demo:
The demo connects to a database using FDConnection1 and FDQuery1 is set to the query in the edit field. When clicking the button a dialog opens up to ask for the filename and stores the query data in JSON format.
procedure TFrmMain.btnSaveClick(Sender: TObject); begin if DlgSaveAs.Execute then begin SaveToJSON( DlgSaveAs.FileName, txtQuery.Text ); end; end; procedure TFrmMain.SaveToJSON(AFileName, AQuery: String); begin FDQuery1.Open(AQuery); FDBatchMoveJSONWriter1.FileName := AFilename; FDBatchMove1.Execute; end;
The important part of this demo is the way how to link the batch move components. Drop the TFDBatchMove component first! Then, drop TFDBatchmoveDataSetReader and TFDBatchMoveJSONWriter. This way, both components get set up as reader and writer for the TFDBatchMove component. You can set these manually, of course, using the Writer and Reader property. Finally, you need to configure the reader and the writer. The reader needs to know which dataset to read. Thus, set its Dataset property to FDQuery1; the writer needs a filename which will be set at run-time.
In order to start the process, we need to call Execute on the batch move component. Done!
This JSON just contains the data no matter the setting of StoreItems etc. You can configure the sub-format of the JSON file using TFDBatchMoveJSONWriter.JsonFormat, though. You can save as BSON, JSON or JSONP. Still, in order to read it with your eyes, you might need a JSON formatter. The output is very much optimized for machines reading it: