Categories
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';
...

FDQuery1.Execute(FDQuery1.Params.ArraySize);
ShowMessage(IntToStr(FDQuery1.RowsAffected));

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';
...

FDQuery1.Execute(FDQuery.Params.ArraySize);
ShowMessage(IntToStr(FDQuery1.RowsAffected));

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';
...

FDQuery1.Execute(FDQuery.Params.ArraySize);
ShowMessage(IntToStr(FDQuery1.RowsAffected));

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.

Categories
REST

Collecting Multiple Pages of REST Results in a Single MemTable

I’ve run into this a few times, where a REST Service returns results in pages, so you have to make multiple passes to get all the data. One thing I love about the RAD REST Client Library is the ability to store all the data in the FDMemTable where you can work on it. Unfortunately when you can only get part of the results that limits the functionality. Here is a simple solution to get all the pages in a single FDMemTable.

What you need is a second FDMemTable. My first one is called MembersTempTable, and it is attached to the Response Adapter. The second one is called MembersMemTable and it will hold the complete set of data.

  // Drop any existing data
  MembersMemTable.Close; 
  
  // Get the first page 
  MembersRequest.Params.ParameterByName('limit').Value := '50';
  MembersRequest.Params.ParameterByName('offset').Value := '0';
  MembersRequest.Execute;

  // This clones the FDMemTable
  MembersMemTable.CopyDataSet(MembersTempTable, 
    [coStructure, coRestart, coAppend]);

  // Now we add the other tables
  MembersMemTable.BeginBatch();
  try
    // Repeat until we don't get a full page
    while MembersTempTable.RecordCount = 50 do
    begin
      // Start with the next page
      MembersRequest.Params.ParameterByName('offset').Value := 
        (MembersMemTable.RecordCount - 1).ToString;
      MembersRequest.Execute;
      // Append those records into our FDMemTable
      MembersMemTable.CopyDataSet(MembersTempTable, [coAppend]);
    end;
    MembersMemTable.IndexFieldNames := 'id';
  finally
    MembersMemTable.EndBatch;
  end;
Categories
REST Source Code Tools

FireDAC MemTable Editing – New in 10.2 Tokyo

One of my favorite new features (beyond Linux) is the ability to edit the data in a TFDMemTable at design time. You could already edit the field defs, to set it up. This makes the TFDMemTable, which was already super flexible and powerful, even more flexible. You can use it to capture some small data at design time and then use visual live bindings or data binding to connect it to visual controls.

Edit TFDMemTable

Holger Flick has a great blog post on the topic too. Here is a video I made with Sarina that shows it in action.

What is your favorite new feature in Tokyo?

Categories
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!

Categories
REST

Decompressing the TRESTResponse

RAD Studio, Delphi and C++ 10 Seattle has a fantastic REST Client Library. If you are unfamiliar with it, check out my 5 Minute REST Client video.

The TRESTRequest component has an AcceptEncoding property where you can specify the type of compression you would like applied to the response. The TRESTResponse component has a ContentEncoding property that specifies the the encoding that the server chose. The most common compression encoding is gzip, followed by deflate. The others are rarely used (although brotli is a new compression that looks promising for the future.)

By default it is not encrypted if you leave AcceptEncoding blank. However when you use the Stack Overflow API it always compresses with gzip if nothing is specified.

The TRESTResponse has two ways to access the response data directly, the RawBytes property and the Content property. Both are read-only. So we can read the compressed response data, but cannot update the TRESTResponse with the decompressed data. Updating the TRESTReponse allows it to work with the TRESTResponseDataSetAdapter, which is a fantastic feature.

Luckily we can use the protected mode workaround to update the value. Here is a unit that you can include in your project to easily decompress the TRESTResponse and continue to use it with the Data Set Adapter.

unit RestDecompress;

interface

uses
  System.SysUtils, System.Types, System.Classes, IPPeerClient, REST.Client;

procedure DecodeRestResponse(ARestResponse: TRESTResponse);

implementation

uses
  System.Zlib,
  IdBaseComponent, IdException, IdZLibCompressorBase, IdCompressorZLib;

  type // protected mode work around
  TProtectedRESTResponse = class(TRESTResponse)
  end;


procedure DecodeRestResponse(ARestResponse: TRESTResponse);
var
  LCompressed: TMemoryStream;
  LDecompressed: TStringStream;
  LDecompress: TIdCompressorZLib;
begin
  if Length(ARestResponse.ContentEncoding) = 0 then exit;

  LCompressed := nil;
  LDecompressed := nil;
  LDecompress := nil;
  try
    LCompressed := TMemoryStream.Create;
    LDecompressed := TStringStream.Create;

    LCompressed.WriteData(ARESTResponse.RawBytes, Length(ARESTResponse.RawBytes));
    LCompressed.Position := 0;

    // Use the Indy decompression libraries because the HTTP stream doesn't
    //   have the proper headers that System.ZLib looks for.

    LDecompress :=TIdCompressorZLib.Create();

    if ARestResponse.ContentEncoding = 'gzip' then
      LDecompress.DecompressGZipStream(LCompressed, LDecompressed)
    else if ARestResponse.ContentEncoding = 'deflate' then
    begin
      // Due to variations in the deflate server side implementations,
      //   this rarely works, but is here for completeness and just in case
      LDecompress.DecompressHTTPDeflate(LCompressed, LDecompressed);
    end;

    TProtectedRESTResponse(ARESTResponse).SetContent(LDecompressed.DataString);
  finally
    LDecompressed.Free;
    LCompressed.Free;
    LDecompress.Free;
  end;
end;

end.

Simply use this unit, and then add a call to DecodeRestResponse(RESTResponse) to RESTResponse’s OnAfterExecute event handler. It checks the ContentEncoding and then uses the correct decompression and updates the content.

You’ll notice it uses the Indy TIdCompressorZLib component instead of the new System.ZLib library. The reason is a GZip encoded HTTP response doesn’t include the full headers expected by the ZLib library. There is a way to work around this, but no need to do that since the Indy library works fine.

Categories
Android iOS Mobile REST

REST Client Video Challenge

Yesterday, I mentioned I built a REST client and deployed it to iOS and Android inside 5 minutes. Someone asked me to make a video actually showing how it was done. So here it is showing a simple REST client built and deployed to iOS 9.1 and Android 6.0 Marshmallow.

Since I am using SpitCast, and it doesn’t offer an encrypted endpoint for its REST service, I had to modify the Info.plist to allow that on iOS 9.

Could I do it faster without the video recording and explanation? Yes, but I think you get the idea. Delphi makes building multiplatform REST clients really, really easy.

I’m curious to see someone create a client app using other general purpose development tools to connect to a REST service and too see how long that takes.

If I wanted to connect to a different end point to pull down more information on an individual surf spot I could that by handling the OnItemClick event for the List View.

If there is interest I’m happy to share my Sync-Timer source code.

Categories
REST Tools

Delphi and REST Client Development

I really don’t like to make comparison between different development tools. In my opinion most tools have their strengths for certain tasks. So I try not to criticize other tools by name. Delphi is my favorite, and I believe it is the best all around tool for more projects I face. Occasionally I have someone ask me how Delphi compares to another tool they are evaluating. I thought I would share one such experience.

When I was in Brazil I went to meet with a guy who was in charge of software development for a large, international company. They were all standardized on a different development tool, but it didn’t really have a mobile solution. They were looking at some tools that claimed to make it really easy to do mobile development, but once he got past the marketing he found it wasn’t all it was cracked up to be.

To evaluate the tool he decided he would develop a simple little app for iPhone and Android that connected to a REST server to pull down a list of countries, cities, etc. to help people find offices for his company. He thought that a week should be more then enough time to develop this simple application.

What he found was that while he expected it to be cross platform, it was actually only shared code. So he had to create a project for each platform, and then he could share part of the code (not as much as he would like) between each project. Also there was significant overhead in consuming a REST service. After a couple days he realized a week wasn’t enough time for even one platform.

To top it all off he discovered that the cost was actually way more then it was initially made out to be when you actually got down to doing something serious.

So I showed him Delphi and told him that the app he was describing could be done inside a day, probably with a prototype within an hour. He found that a little hard to believe, so I created a simple REST client displaying the result of a REST service and deployed it to my Android phone. It took about 5 minutes, including the time it took to connect to his WiFi.

He was so impressed he actually got his phone out to make a video of it just so he could show his boss, who he was sure wouldn’t believe him.

That is what I love about Delphi. It makes the simple things that you need to do all the time so simple and so quick, and it makes everything else still pretty simple. Need an API that isn’t wrapped in a component or the RTL, no problem, you can still access it.

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
News REST Source Code

CORS on DataSnap REST Server

Cross-origin resource sharing (CORS) is a mechanism that enables resources to be shared across domains. Typically this isn’t allowed to prevent security issues. To enable on your DataSnap REST server you can use the following code per MVP Nirav Kaku from India.

All you need to do is add a custom header in the Response before dispatching the result on the DataSnap server…

procedure TWebModule1.WebModuleBeforeDispatch(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
begin
  //allows cross domain calls
  Response.SetCustomHeader('Access-Control-Allow-Origin','*');
  if FServerFunctionInvokerAction <> nil then
    FServerFunctionInvokerAction.Enabled := AllowServerFunctionInvoker;
end;

It is useful for DataSnap server developers who want
their REST calls to be supported via AJAX using JavaScript from a
different server.

[Reference]

Note: CORS is security feature of the browser so there could be some
dependency there. Tested with Firefox, Chrome and IE and it seems to
be working fine.

Categories
Android iOS Mobile REST Source Code webinar

Mobile Summer School 6: REST & BaaS

Here are the slides and downloads from my mobile summer school session on REST & BAAS. If you just want the slides they are on Slide Share. I’ll post the video and more details here later.

For more information on BaaS, check out Sarina’s excellent series of blog posts.