Category: DataBase

Categories
DataBase Source Code

New Delphi Seattle MongoDB Sample

I created some more Delphi 10 Seattle samples to show off MongoDB and FireDAC functionality: LocalSQL, Indexing & Geospatial.

FireDAC MongoDB NoSQL

The first one queries some data from MongoDB allowing you to specify the match, sort and projection, then it stores the results in a DataSet. At that point you can use LocalSQL to write a SQL query against the result set. While FireDAC gives you full native support for MongoDB, it also puts the SQL back into NoSQL.

MongoDB FireDAC LocalSQL

Indexing is used to improve your query performance. It is really easy to work with MongoDB queries with FireDAC.

MongoDB FireDAC Indexes

And one of the cool features of MongoDB is that you can do spatial queries. Here is an example that shows how to create a Spatial index and then do a spatial query with FireDAC. This uses the restaurant data that is included with the shipping samples, so make sure you load the restaurant data first.

Geospatial MongoDB FireDAC

If you missed my previous post I had a MongoDB FireDAC and C++Builder sample.

[You can download my new samples here.]

Categories
DataBase REST Tools

Export Trello to CSV

I’m a big fan of Trello as a way to organize and collaborate. Recently I needed to export a Trello board to CSV. They have an export to JSON, but you have to buy a years worth of business class for the export to CSV. The business class is probably worth it for other reasons as well, but I thought I would see what it would take to convert JSON to CSV using Delphi 10 Seattle’s REST Client and FireDAC. I’m really pleased with what I came up with, and it didn’t take much effort.

I used the REST Client to connect to the JSON export on Trello. The REST Client is overkill for this since it is just a simple HTTP GET request, but the REST Response DataSet adapter is what I was after. This piped the JSON into a TFDMemTable.

Now the JSON from Trello contains a few collections (JSON Array’s of objects) so I used 3 different adapters (each with a different root node) and 3 different memory tables: Cards, Lists and Labels. There are other collections, but I wasn’t interested in them right now.

Once I have the JSON in a DataSet, I need to work with it. So I put down a TFDLocalSQL component and pointed it to the Mem Tables. Then attached a TMemo to the Query of a TFDQuery component, and I was able to run SQL querries against the Trello board. This made it easy to select exactly the data I wanted to export.

For the export I just used the FireDAC ETL functionality to pump the DataSet resulting from the Local SQL query to a text writer which saves a CSV file.  I love the way FireDAC provides all this great high level functionality like Local SQL and the Text Writers. It makes so many tasks so easy to deal with.

There is still a lot of polish to do for this, but right now it does the job really well. I thought I would share it with anyone else who might be interested in exporting Trello to CSV. You could take a look at the source code and use it to convert most any REST/JSON datasource to CSV.

I put the code up on GitHub.

Categories
DataBase

MongoDB FireDAC C++ Sample

I created a simple sample that shows some basic FireDAC functionality with MongoDB in C++ (Works with C++Builder or RAD Studio 10 Seattle)

MongoDB FireDAC C++

This is similar to a one of the Object Pascal / Delphi MongoDB sample. It shows how you can query a MongoDB database using the new TFDMongoQuery component and display it in a grid. It also shows how to handle nested document objects (like Address) and arrays / DataSets (Address.Coords and Grades).

In addition to using the match property of the query, it also allows the specification of a sort and a projection. The projection is used to change which fields of the document you retrieve.

For more information on MongoDB, check out my MongoDB Skill Sprint and register for CodeRage X (Oct 13 – 15)

FireDAC MongoDB NoSQL