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
Audio podCast FireDAC podcast

Getting Technical with Cary Jensen and FireDAC (Episode 74)

In this episode of the Podcast @ Delphi.org we talk with Cary Jensen about his new book Delphi in Depth: FireDAC and the upcoming Delphi Developer Days which Cary puts on with Dr. Bob Swart. Nick Hodges, our co-host, was a previous host of Delphi Developer Days with Cary, and I’ve made a number of appearances there as well. They are highly recommended.

Cary’s new 558 page Delphi in Depth book tackles everyone’s favorite multi-platform database access framework: FireDAC. Cary gives it his unique treatment that only he can deliver. He gives special emphasis to all the configuration options available with FireDAC that can help you gain even more performance.

Delphi in Depth: FireDAC by Cary Jensen

For this year’s Delphi Developer Days, Cary and Dr. Bob are doing things a little differently. They are doing a special workshop focused on database development. You are encouraged to bring your laptop and follow along while you learn from the masters!

Just in case you missed it, Cary also put on a webinar on FireDAC recently that I recommend you check out.

Categories
FireDAC

Simple Mobile FireDAC App

The DocWiki has a great mobile tutorial on building a mobile To Do app with FireDAC and SQLite. I find I usually build it a little differently though, so I thought I would share my code here.

Go ahead and lay the UI out the same, and put down most of the same FireDAC components, with the visual LiveBindings. I love how it uses the LiveBindings wizard to create the data source. What I change is the code in the event handlers, but what you don’t need is the Insert and Delete FDQuery components. While that code all works, it uses an older model that doesn’t take full advantage of the amazing features of FireDAC.

So your form will look something like this . . .

FireDAC-ToDo-1

Add an implementation uses clause

uses FMX.DialogService.Async, IOUtils;

And then your addButtonClick event handler will look like this . . .

procedure TForm42.addButtonClick(Sender: TObject);
begin
  TDialogServiceAsync.InputQuery('New item',['Name'], [''],
  procedure (const AResult: TModalResult;
             const AValues: array of string)
  begin
    if (AResult = mrOK) and (Length(AValues) > 0) and
       (Length(Trim(AValues[0])) > 0) then
    begin
      FDQuery1.InsertRecord([AValues[0]]);
    end;
    UpdateDeleteButton;
  end);
end;

You’ll notice a few changes. First of all, I used an anonymous method for the async callback. Also, instead of using an entirely different insert query, I just call InsertRecord on the existing FDQuery1, passing in the value. For simple tables like this one this is so much easier. This really simplified the code in my opinion. Also instead of spreading the code to update the Delete button’s visibility all over the place I used a procedure called UpdateDeleteButton. Here is the rest of the code with a few comments.

procedure TForm42.deleteButtonClick(Sender: TObject);
begin 
  // Again we use the built in Delete method instead of a separate
  //   Delete query. This just deletes the currently active record.
  FDQuery1.Delete;
  UpdateDeleteButton;
end;

procedure TForm42.FDConnection1AfterConnect(Sender: TObject);
begin
  // This makes sure our table exists, just in case the data file
  //   no longer exists. Makes the app more resilient. 
  FDQueryCreateTable.ExecSQL;
  // And let's not assume anything about that Delete button status
  UpdateDeleteButton;
end;

procedure TForm42.FDConnection1BeforeConnect(Sender: TObject);
begin
  // We don't need to use a compiler directive here, but that does
  //   mean our database file is in our documents folder, which 
  //   doesn't make as much since for a desktop.
  {$if DEFINED(iOS) or DEFINED(ANDROID)} 
  FDConnection1.Params.Values['Database'] := 
    TPath.Combine(TPath.GetDocumentsPath, 'todolist.sdb');  
  {$ENDIF} 
end;

procedure TForm42.ListView1Click(Sender: TObject);
begin
  UpdateDeleteButton;
end;

procedure TForm42.UpdateDeleteButton;
begin
  deleteButton.Visible := ListView1.Selected <> nil;
end;

The beauty of calling the FDQueryCreateTable.ExecSQL is that you don’t need to deploy an empty database file. It just creates an empty one the first time it runs.

So what do you think, is this simpler? What would you do differently?