Using CData’s Gmail connector in C++ Builder

 

Problem: How to use the Gmail FireDAC Enterprise Connector

Solution tested on C++ Builder 10.2.3

CData is a company with a clever idea. It writes drivers that connect to various technologies and make them appear as if they were databases. Thus, knowing only SQL, you can read a CVS file, use Google’s Gmail, search with Microsoft’s Bing, link in to LinkedIn, and do many other things. CData and Embarcadero have partnered to create wrappers for these drivers in the form of components that you can use in both VCL and FireMonkey programs. Embarcadero calls these Enterprise Connectors. The rest of this post is a demonstration of how to use the Gmail enterprise connector in a VCL program. If you’d rather just see a summary of tips for using the connector, scroll down to the end of this post. If you’d like more background on enterprise connectors, see this post.

To start this tutorial, make a new VCL application, or if you prefer, download all of the code in this post by downloading CData_Gmail.zip from here. Drop the following components on the main form: TDBGrid, TDBNavigator, TFDConnection, TFDQuery, TDataSource, FDPhysCDataGmailDriverLink, two TButton’s and an FDGUIxWaitCursor. (We won’t use the last component but it needs to be present in order to avoid a run-time error.) Arrange the components on the form so they look similar to this:
Gmail01

Now do the following in the Object Inspector:

  • In FDConnection1, set DriverName to CDataGmail and LoginPrompt to False. Under Params, set UserName and Password to the Gmail credentials
  • In FDQuery1, verify that Connection is set to FDConnection1
  • In DataSource1, set DataSet to FDQuery1
  • In DBGrid1, set DataSource to DataSource1
  • In DBNavigator1, set DataSource to DataSource1

Builder lets us test connections and issue SQL commands at design time. Here are four ways to do so:

  • Method 1 – set the Connected property of FDConnection1 to True. If you are able to set this, you’ve successfully connected
  • Method 2 – Double-click on the FDConnection1 icon. The Connection Editor will appear. Click on the Test button and when you get the FireDAC Login dialog box, click on OK.  If FireDAC was able to connect, you’ll get a message box that says “Connection established successfully.” Otherwise, you’ll get an error message. Close the dialog box when you’re done
  • Method 3 – Double-click on the FDQuery1 icon to get the FireDAC Query Editor. In the SQL Command tab, on the first line, type SELECT * FROM Inbox and click on the Execute button. If your component connected successfully,  information about the emails that are in your inbox, such as the sender, subject line and date received, will appear in the RecordSet tab with one email per row. Although it looks like there’s only one column (Id), you can see all of the fields by using the horizontal scroll bar. To see all of the rows, use the vertical scroll bar. To see the names and data types of the columns, click on the Structure tab
  • Method 4 – Follow the directions for Method 3 but instead of clicking on the Execute button, click on the OK button. In the Object Inspector, select Activate. If Builder is able to make the connection, the email information will appear in the database grid. When you’re finished, set Activate to False

When you’re done testing the connection, set the Connected property of FDConnection1 to False or else the grid will be populated as soon as the program starts. (If you weren’t able to connect, check the spelling of the Gmail user name and password.)

Before moving on to run-time testing, let’s make a few changes. Using the object inspector:

  • For Button1, set Caption to “SQL” and create an OnClick event handler. We’ll use this button to enter SQL commands that will let us work with email messages
  • For Button2, set Caption to “Show all” and create an OnClick event handler. We’ll use this button to conveniently load all email messages into the grid

Your main form should look like this:
Gmail02

Let’s name the unit that contains the main form test_Gmail_unit. The first lines of the C++ file are:

#include 
#pragma hdrstop

#include "test_GMail_unit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "FireDAC.Phys.CDataGmail"
#pragma link "FireDAC.Phys.CDataGmailDef"
#pragma resource "*.dfm"
TForm1 *Form1;

#include  // for TPath

void set_column_widths( TDBGridColumns* columns, int width );

Your code should be similar, except that you’ll need to manually add the line that includes System.Ioutils.hpp and the line that declares the function set_column_widths. The former is for accessing TPath and the latter is a utility that sets the columns in a DBGrid to a specified width so that they become more visible.

The first method is the main form’s constructor:

__fastcall TForm1::TForm1(TComponent* Owner)
  : TForm(Owner)
{
  String text;
  if( InputQuery("Maximum number of messages","Max (0=unlimited): ",text) )
  {
    // store a pointer to a TFDPhysCDataCSVConnectionDefParams, which is what
    // the object that FDConnection1->Params points to really is
    TFDPhysCDataGmailConnectionDefParams* params =
      dynamic_cast(
      FDConnection1->Params );
    params->MaxItems = text;
  }
}

Although we could have set MaxLimits at design time, the constructor code illustrates how to change the connection parameters at run-time, namely, by casting FDConnection1->Params to a TFDPhysCDataGmailConnectionDefParams.

The code continues with the event handler for Button1’s OnClick event, i.e.

void __fastcall TForm1::Button1Click(TObject *Sender)
{
  const String select( L"SELECT" );
  String command;

  // InputQuery() is a VCL function
  if( InputQuery("SQL command","SQL: ",command) )
  {
    // for commands that return a data set
    if( command.SubString1(1,select.Length()).CompareIC(select)==0 )
    {
      FDQuery1->Open( command );
      set_column_widths( DBGrid1->Columns, 200 );
    }
    else
    {
      FDQuery1->SQL->Text = command;
      FDQuery1->Execute();
    }
  }
}

In the event handler

  1. We first use the VCL function InputQuery to get an SQL command from the user
  2. If the user entered a SELECT command we use FDQuery1‘s Open method, which is for SQL commands that return a data set. After the email messages are loaded into the grid, the columns can be very wide and hence involve a lot of scrolling to see. We call the utility function set_column_widths to set all of the column widths in the grid to a more helpful size
  3. If the user command was something other than SELECT, we assume that the command does not return data and store it in FDQuery1->SQL->Text. Then we run the command with FDQuery1->Execute(), which is for executing SQL commands that do not return data. Although we might typically have chosen FDQuery1->OpenOrExecute(), which makes the decision on its own, doing so with the CData Gmail component produces a run-time error

The remaining button allows us to quickly load the email messages in the inbox by issuing the SQL command SELECT * FROM Inbox.  The code for the Button2 event handler and the set_column_widths utility function is

void __fastcall TForm1::Button2Click(TObject *Sender)
{
  const String command = L"SELECT * FROM Inbox";
  FDQuery1->Open( command );
  set_column_widths( DBGrid1->Columns, 200 );
}
//---------------------------------------------------------------------------

void set_column_widths( TDBGridColumns* columns, int width )
{
  for( int i = 0; i Count; ++i )
    columns->Items[i]->Width = width;
}

Before running the program, let’s look at some general information about how the Gmail connector uses SQL:

  • Gmail creates the system mailbox folders (or “labels”, as Google likes to call them) Inbox, Starred, Important, Chats, Sent Mail, Drafts, All Mail, Spam, and Trash. The Gmail connector treats each of these as an SQL table. The table name for the inbox folder is “Inbox”. The table name for any of the other system folders is the folder name prefixed by “Gmail/”, e.g. the spam folder “Spam” becomes the table “Gmail/Spam”
  • The table names of folders that the Gmail user creates are the same as the folder names
  • If a table name has a space in it, the documentation says to put the name within square brackets, e.g.,
    SELECT * FROM [Gmail/All Mail]
    This does not currently work. Instead, put the name in single quotes, e.g.,
    SELECT * FROM 'Gmail/All Mail'
  • If a Gmail field name is the same as an SQL keyword, put the field name in square brackets, e.g.,
    SELECT * FROM Inbox WHERE [From] = 'support@cdata.com'
  • email addresses must be in the form
    'Friendly Name' address@company.com or address@company.com

You can see the names of all columns (fields) and pseudo-columns here. (A pseudo-column is a kind of virtual column that doesn’t exist but that we can use in a WHERE clause.) To see the names of all tables in the Gmail account you’re connected to, issue the command
SELECT * FROM sys_tables

Reading and Listing Email

The Gmail connector uses the SQL command SELECT to retrieve email messages. By default, the returned data from such queries is subject to these restrictions:

  • The default number of messages returned is 100. To return a different number of messages, use the SQL keyword LIMIT or the pseudo-column MaxItems (see below)
  • To return all the messages from a mailbox folder, specify a value of 0 or -1 for LIMIT or MaxItems
  • The MessageBody and Headers field are not returned if the SELECT statement results in multiple email messages. If the connector returns only one email message, those two fields will be present. Unfortunately, TDBGrid can’t display a MessageBody field but we can use a TDBMemo or TMemo component to do so

Now let’s try out the program. Run the code and click on the “Show all” button. This will show all of the email messages in the inbox. The SQL command is
SELECT * FROM Inbox

We can retrieve messages with specified ID’s by using these syntaxes in the WHERE clause of a SELECT command:

  • One ID – use WHERE Id = n
  • One of several IDs – use WHERE Id=m OR ID=n. (The documented syntax of Id='m,n' doesn’t work)
  • A range of IDs – use WHERE Id BETWEEN m AND n. (The documented syntax of Id='m:n' doesn’t work)

For example,

  • Return only the message with an ID of 5
    SELECT * FROM Inbox WHERE Id=5
  • Return all messages with IDs between 10 to 15
    SELECT * FROM Inbox WHERE Id BETWEEN 10 AND 15

The documentation for SELECT has quite a few examples of searching for email messages. In general, the columns that we can use in the WHERE statement are: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, and Date. Here are a few more examples:

  1. Find all inbox-messages whose body contains the phrase “Nigerian prince”
    SELECT * FROM Inbox WHERE MessageBody LIKE '%Nigerian prince%'
  2. Find how many messages in the spam folder came from the URL “networkwithexpert.com”
    SELECT COUNT(*) FROM [Gmail/Spam] WHERE [From] LIKE '%networkwithexpert.com%'

Sending Email

The Gmail connector uses the INSERT command of SQL to send email. An example of the basic format is
INSERT INTO MailMessages ( To, Subject, MessageBody )
VALUES ( 'address@company.com', 'Test message', 'This is my body' )

We can use Mailinator to test sending an email with the command
INSERT INTO MailMessages ( To, Subject, MessageBody )
VALUES ( '12016768877@mailinator.com', 'Test message from C++ blog', 'This is my body' )

Modifying Email

Use the UPDATE command of SQL to modify existing email. For example, in IMAP messages the flags that can be changed are: “Answered”, “Deleted”, “Draft”, “Flagged” and “Seen”. To mark the message in the inbox with ID of 53 as being seen (read), use the command
UPDATE Inbox SET Flags='Seen' WHERE Id = 53

Moving Email

We can also use the UPDATE command to move email from one mail folder to another. The general syntax is
UPDATE source_mailbox SET Mailbox=destination_mailbox WHERE Id=n

Deleting Email

Use the DELETE command of SQL to permanently remove an email message. The syntax is
DELETE FROM mailbox WHERE Id=n

Tips for using the Gmail connector

This concludes the tutorial on the basics of using the Gmail Enterprise Connector. A summary of things to be aware of is:

  • Set the SupportEnhancedSQL property of the connector component to True if you want to use aggregate functions, e.g., COUNT, AVG
  • Use COUNT (DISTINCT x), not COUNT_DISTINCT(x), to get the number of unique messages
  • Surround Gmail folder names that contain spaces with single quotes, not square brackets, e.g., 'Gmail/ All Mail', not [Gmail/ All Mail]
  • Use Id BETWEEN m AND n, not Id='m:n', to specify a range of message IDs
  • Use Id=m OR Id=n, not Id='5,6', to specify multiple IDs
  • FDQuery1->OpenOrExecute() produces a run-time error when used with the Gmail connector
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s