Cloud FireDAC REST Source Code

API Limits with #FDEC

API Limits with FireDAC Enterprise ConnectorsThe FireDAC Enterprise Connectors (#FDEC) by CData and Embarcadero make it really easy to work with various APIs just like you would any SQL database. For example if you want to publish the results of a query to a Google Sheet (which I find incredibly useful) then it is just a few FireDAC components and you are off to the races. You might run into an API limit though.

What is an API limit? Most rest services have a limit to how often a client can call a specific API within a certain amount of time. Google calls this their usage limit:

This version of the Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.

That may seem like a lot, but I found I was running into that limit pretty quick once I moved my project into production. Luckily FireDAC and the FireDAC Enterprise Connectors have a simple workaround: Batch Processing.

Using the Array DML features of FireDAC you can batch multiple DML (Data Manipulation Language) operations into a single API call. The FDEC Google Sheets documentation from CData doesn’t cover Array DML, but the component supports this (they are updating the documentation). The Elasticsearch documentation does cover Batch Processing with an example, and I’ve used this with Sheets and it works great!

Bulk Insert

The following example prepares a single batch that inserts records in bulk.

FDConnection1.ResourceOptions.ServerOutput := True;
FDQuery1.SQL.Text := 'insert into Account values (:Name, :Id )';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= 'MyName1';
FDQuery1.Params[1].AsStrings[0]:= 'MyId1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= 'MyName2';
FDQuery1.Params[1].AsStrings[1]:= 'MyId2';


To retrieve the Ids of the new records, query the LastResultInfo#TEMP table:

sName := FDQuery1.Open('SELECT * FROM [LastResultInfo#TEMP]');

Bulk Update

The following example prepares a single batch that inserts records in bulk.

FDQuery1.SQL.Text := 'update Account set Name = :Name WHERE Id = :Id';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= 'Floppy Disks';
FDQuery1.Params[1].AsStrings[0]:= 'Id1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= 'Jon Doe';
FDQuery1.Params[1].AsStrings[1]:= 'Id2';


Bulk Delete

The following example prepares a single batch that inserts records in bulk:

FDQuery1.SQL.Text := 'delete Account where Id = :Id';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= 'MyId1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= 'MyId2';


If you want to learn more about Array DML check out these videos:

Array DML Skill Sprint with Pawel Glowacki

FireDAC in Depth with Cary Jensen

Also check out Cary Jensen’s book on the topic of FireDAC in Depth.


Google Cloud Shell Network Details

Marco Cantu’s blog post about Running a Delphi Linux Application on Google Cloud Shell got me wondering more about the Google Cloud Shell, specifically around the networking. First of all, is the IP address public?

jim@cloudshell:~$ hostname -I
jim@cloudshell:~$ curl

So the private IP is different from the public IP. I’ll need to test to see if it is mapped with NAT or something. Seems unlikely though. Google offers public facing computers as part of their cloud services, so it makes sense that you would use those instead.

Next question is around bandwidth.

curl -s | python -

Results in . . .

Retrieving configuration...
Testing from Google Cloud (
Retrieving server list...
Selecting best server based on ping...
Hosted by Mimosa Networks (San Jose, CA) [17.60 km]: 40.662 ms
Testing download speed........
Download: 322.69 Mbit/s
Testing upload speed........
Upload: 260.94 Mbit/s

322.69 Mbit/s down and 260.94 Mbit/s up is really impressive! So if you have a task that requires a lot of bandwidth and then condenses it down to a smaller digest, it would be a really useful platform.

Android Cloud FireMonk iOS macOS REST TMS

Using TMS Cloud Pack to Integrate Your Apps with the Cloud

I’m a huge fan of the REST Client Components included with RAD Studio. They make it really easy to take advantage of the huge amount of REST services available online.

Here is a quick video of me using the REST Client components to build a REST Client in 5 minuntes.

One thing about more complex REST APIs is REST isn’t a strict protocol like SOAP, it is more of a philosophy in building an API. This usually means I spend a few hours looking at each REST API that I want to work with. Authentication is the thing that really changes from API to API.

That is where the TMS Cloud Pack comes it. They make working with some of the standard large APIs a breeze (get it, a breeze when I am talking about clouds!?!) You just need to setup an API account and get your API key and provide it to the component and it does the rest.

I got a license for TMS Cloud Pack from TMS Software. I was surprised at first that it didn’t work with FireMonkey. It turns out there is a seperate Cloud Pack for FMX, although they offer a Cloud Studio that works with a variety of tools (including IntraWeb!)

When I started working with it I realized it made sense to have a different VCL and FMX version of the components because they include a browser window that is used when the user needs to authenticate for the OAuth services.

Besides the different browser window I found the FMX and VCL versions very simialr in functionality. So it is just a matter of what frameworks and platforms you want to work with since the FMX versions add support for macOS, iOS and Android in addition to the Windows support in the VCL version.

With the power and variety of REST APIs available today, you really should look at leveraging them in your apps, and if it is one of the APIs that TMS supports, then you will want to use it!

If you are new to TMS Cloud Pack then join us for our webinar on Tuesday the 21st of February and check out the TMS Cloud Packs in the GetIt Package manager.

See you online!