Using CData’s CSV connector in C++ Builder

 

Problem: How to use the CSV FireDAC Enterprise Connector

Solution tested on C++ Builder 10.2.2

CData is a company that makes drivers for databases. Although this may sound like a useful but prosaic product, CData has a clever twist. Their mantra is “See the world as a database” and their products take this to heart. They allow you to view many things that aren’t databases as if they were. Examples include accessing sites such as eBay, Instagram, Facebook, Twitter and Gmail; working with programs like QuickBooks, Google Sheets, Microsoft Office 365; and manipulating data that is in generic formats, such as JSON, XML and CSV. The idea is that by using just SQL you can now accomplish a lot more than querying databases. Well, that’s nice, but what does it have to do with Builder?

The answer is that in 2017 Embarcadero partnered with CData to produce what they call FireDAC Enterprise Connectors. These are CData’s drivers packaged as Builder/Delphi components for use with Embarcadero’s FireDAC database framework in both FireMonkey and VCL programs. So if you’ve worked with FireDAC before, you now have a vastly expanded capabilities available through familiar components such as TFDConnection and TFDQuery.

Although I’m far from being a FireDAC expert, I have been using it for the last few years so I thought I’d try out some of the enterprise connectors. I’ll start with the CSV (comma-separated values) component because data in that format already looks like a database table. That should make learning enterprise connectors easier.

CData has written the documentation for the CSV component and it’s done a good job. CData will be providing the technical support for the enterprise connectors, not Embarcadero. In fact, I’d like to make a shout-out to the support people at CData. They answered my questions within one business day and every person who responded knew what he was talking about.

You can download a trial version of the CSV connector from the GetIt package manager in RAD Studio but you’ll probably be able to get a more recent version from CData itself. Make sure that RAD Studio is closed and then just double-click on the CSV downlad to install the component.

The rest of this post is a fairly detailed demonstration of how to use the CSV connector in a VCL program. If you’d rather just see a summary of the “gotchas”, you can go to the end of the post.

To start, make a new VCL application, or if you prefer, download all of the code in this post from here. Drop the following components on the main form: TDBGrid, TDBNavigator, TFDConnection, TFDQuery, TDataSource, FDPhysCDataCSVDriverLink, three 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:

form1

Now do the following with the use of the Object Inspector:

  • In FDConnection1, set DriverName to CDataCSV and LoginPrompt to False. Under Params, set ReadOnly to True and set DataSource to the name of the CSV file to open. (Include the path if necessary. )  I’ll use the public domain CSV file artist_data.csv, available at Tate Gallery artist data or as part of the code download for this post
  • In FDQuery1, verify that Connection is set to FDConnection1
  • In DataSource1, set DataSet to FDQuery1
  • In DBGrid1, set DataSource to DataSource1 and set ReadOnly to True
  • In DBNavigator1, set DataSource to DataSource1. Under VisibleButtons, set nbFirst, nbPrior, nbNext and nbLast to True and all the others to False

Now the cool thing is that we can actually test some of our database functionality at design time. One way to do this is to try to set the Connected property of FDConnection1 to True. If you are able to set this, you’ve successfully connected. If you can’t make the connection and get an error, make sure that the file name and path you entered in DataSource is correct.

You can also test your connection by double-clicking 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. (I don’t know why this box appears when we have LoginPrompt set to False.) 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.

When you connect to a CSV file, its contents appears as a table whose name is the name of the file, including the extension but not the path. In our case, the table is called “artist_data.csv”. When you need to refer to the table in SQL commands, put the table name in single quotes, e.g., 'artist_data.csv'.

To further test the connection, double-click on the FDQuery1 icon to get the FireDAC Query Editor. In the SQL Command tab, on the first line, type
SELECT * FROM 'artist_data.csv' and click on the Execute button. (This SQL command loads the entire table, i.e., the contents of the file.) The contents of the CSV file will appear in the RecordSet tab. Although it looks like only two columns are present, they’re all actually there. To see them use the horizontal scroll bar. To see all rows, use the vertical scroll bar.

form4

Finally, you can also see the data in the form’s database grid. To do so, in the Object Inspector set the Active property of FDQuery1 to True and voila – you’ll see your CSV file in the grid!

Design-time display of artist_data.csv

When you’re done, set the Connected property of FDConnection1 to False or else the grid will be populated as soon as the program starts.

Before moving on to run-time testing, let’s make a few changes. First, drop a TOpenDialog component onto the main form. Then, using the object inspector:

  • For Button1, set Caption to “Open CSV”and in the Events tab, double-click on the OnClick event field to create the corresponding event handler in the source code. We’ll use this button to select a CSV file to open
  • For Button2, set Caption to “SQL” and create an event handler. We’ll use this button to enter an SQL command that will select data from the CSV file and display it on the grid
  • For Button3, set Caption to “Show all” and create an event handler. We’ll use this button to conveniently load all of the file into the grid

Your main form should look like this:

form3

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

#include
#pragma hdrstop

#include "test_CSV_unit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "FireDAC.Phys.CDataCSV"
#pragma link "FireDAC.Phys.CDataCSVDef"
#pragma resource "*.dfm"
TForm4 *Form4;
//---------------------------------------------------------------------------
#include  // for TPath

void set_column_widths( TDBGridColumns* columns, int width );

__fastcall TForm4::TForm4(TComponent* Owner)
	: TForm(Owner)
{
}

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.

Next comes the code for the OnClick event handler of Button1. (By the way, in this tutorial I’m just illustrating the basics of using the CData CSV component. I’m not showing error handling and other code that should be present in production software.) The Button1Click event handler acts as follows:

  1. First we use the open-file dialog box to get the name of the CSV file to open
  2. Next, we use MessageDlg (a built-in VCL function) to ask the user if the CSV file contains a header row. (In CSV files, the first row can either be data or, if it’s a header row, the names of the columns.)
  3. We’ll need to set connection parameters containing the CSV file name and whether or not it has a header row. The Params property of FDConnection1 returns a TFDConnectionDefParams pointer. TFDConnectionDefParams contains basic information about the connection. The Params pointer actually points to a TFDPhysCDataCSVConnectionDefParams object, which is derived from TFDConnectionDefParams. The former object contains the basic connection information and also settings specific to CSV files. We’ll cast Params to a TFDPhysCDataCSVConnectionDefParams pointer for use in the rest of the method. (Note that TFDPhysCDataCSVConnectionDefParams objects are not available at design time and so you can’t drop one on a form.)
  4. Next we store the file name in the DataSource property of the TFDPhysCDataCSVConnectionDefParams object
  5. In the HDR property of that same object, we store whether the file has a header row (HDRYes) or not (HDRNo). We also tell the DBGrid to display the header row by storing the TDBGridOption::dgTitles option in the grid’s Options property or to not display the row by removing that option from Options
  6. The name of the SQL table will be the file name without a path, e.g., artist_data.csv . We use the TPath::GetFileName method to extract the file name and store it in a class variable for use by other class methods
  7. Next, we make the SQL command to load the entire contents of the file. In our case the command is SELECT * FROM 'artist_data.csv'. Note that whenever you use text as the value of an SQL field, you must enclose the text in single quotes. Also, keywords in SQL, such as SELECT and FROM, are case-insensitive but are often capitalized
  8. To execute the command, we call FDQuery1->Open. This is the method to use for SQL commands that just return data. We won’t ever use its counterpart FDQuery1->ExecSQL, the method for SQL commands that alter data, because the CSV files are read-only
  9. After the file is 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 value
void __fastcall TForm4::Button1Click(TObject *Sender)
{
	String file_name;
	if( OpenDialog1->Execute() )
		file_name = OpenDialog1->FileName;
	else
		return;

	// ask the user if the CSV file has a header row
	// Note that MessageDlg returns mr*, not mb*, e.g., mrYes, not mbYes
	const bool has_header = MessageDlg( L"Does the CSV file have a header row?",
		mtConfirmation,TMsgDlgButtons()<<mbYesParams );

	// set the data source in the CSV connector
	params->DataSource = file_name;

	if( has_header )
	{
		// if there is a header, each entry in the header row (the first row)
		// is the name of a column
		params->HDR = HDRYes;

		// tell the grid to display the header row
		DBGrid1->Options = DBGrid1->Options Options = DBGrid1->Options >> TDBGridOption::dgTitles;
	}

	// store the table name,
	// which is file name with no path, i.e., stem+extension
	m_table_name = TPath::GetFileName( file_name );

	// to start, load entire file
	const String command =
		String().sprintf( L"SELECT * FROM '%s'", m_table_name );

	FDQuery1->Open( command );
	set_column_widths( DBGrid1->Columns, 100 );
}

The event handler for Button2 is simple:

  1. First we use the VCL function InputQuery to get an SQL command from the user
  2. If the user entered a command, we execute it with FDQuery1->Open and adjust the column widths of DBGrid1, just as we did in the Button1 event handler above
void __fastcall TForm4::Button2Click(TObject *Sender)
{
  String command;

  // InputQuery() is a VCL function
  if( InputQuery("SQL command","SQL: ",command) )
  {
		FDQuery1->Open( command );
		set_column_widths( DBGrid1->Columns, 100 );
  }
}

The remaining button does the same thing as Button2 except that it issues the SQL command SELECT * FROM 'table_name' instead of prompting the user for a command. In our case, table_name is artist_data.csv. The code for the Button3 event handler and the set_column_widths utility function is

void __fastcall TForm4::Button3Click(TObject *Sender)
{
	const String command = String().sprintf( L"SELECT * FROM '%s'",
		m_table_name.c_str() );
	FDQuery1->Open( command );
	set_column_widths( DBGrid1->Columns, 100 );
}

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

There’s one more thing to do before trying out the program. In the header file test_CSV_unit.h, move the line
#include
so that it comes before the lines
#include "FireDAC.Phys.CDataCSV.hpp"
#include "FireDAC.Phys.CDataCSVDef.hpp"

If the lines are not arranged like this, you’ll get an ambiguity error from the compiler.

Now let’s try out the program. Run the code, click on the Open CSV button and select artist_data.csv. Click on Yes when asked if the file contains a header row. Your display should then look like this:

form3AYou can use the navigation tool bar or the scroll bars to examine the data set.

To get a feel for the power of the SQL’s SELECT command, click on the SQL button and enter the command below. Note that the command uses column names, which is common SQL. If the CSV file has a header row, the entries in that row are the column names. If the file has no header row, the columns are called Col0, Col1, Col2, etc. For example, to show the name and year of birth of all women artists who are still living (denoted by a year of death equal to zero), enter
SELECT name, yearOfBirth FROM 'artist_data.csv'
WHERE gender='Female' AND yearOfDeath=0

This gives:
form5

To show the name, birth year and birth place of all artists who were born in the 1950’s in the United States, use
SELECT name, yearOfBirth, placeOfBirth FROM 'artist_data.csv'
WHERE placeOfBirth LIKE '%United States%'
AND yearOfBirth BETWEEN 1950 AND 1959

(The percent sign (%) is SQL’s wildcard symbol and using LIKE with WHERE tells SQL to do a wildcard match.)

The result is:
form6

Do the same as the previous command, but fetch only male artists and sort the rows in chronological order.
SELECT name, yearOfBirth, placeOfBirth FROM 'artist_data.csv'
WHERE placeOfBirth LIKE '%United States%'
AND yearOfBirth BETWEEN 1950 AND 1959
AND gender='Male' ORDER BY yearOfBirth

The program displays:
form7
These examples demonstrate how useful SQL can be in extracting data from CSV files.

The CData CSV Enterprise Connector for FireDAC does a good job of making access to a CSV file be like accessing a database. If you want to mine data from a CSV file by all means use the CData CSV connector. Just keep the following in mind and writing your code will be much easier and faster:

Remember 

  • Get the component from CData’s site instead of the GetIt package manager
  • Close RAD Studio before installing the component
  • You can’t access the component through FireDAC Explorer
  • You can only read from the CSV file
  • The TFDPhysCDataCSVConnectionDefParams component is not available at design time
  • The include statement
    #include
    must come before the lines
    #include "FireDAC.Phys.CDataCSV.hpp"
    #include "FireDAC.Phys.CDataCSVDef.hpp"
Advertisements

One thought on “Using CData’s CSV connector in C++ Builder”

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 )

Connecting to %s