RAD Studio Tip: Using resource scripts to organize project dependencies.

RAD Studio has been around for a very long time, and continues to see new feature development. Consequently, it’s sometimes easy to forget some of the great features that have been included in the product for several years. This happened to me recently and so I wanted to share with you a reminder of one of RAD Studio’s great features of which I was reminded.

The story so far…

I’d like to thank Jens Borisholt for the tip which I’m sharing in this blog post.

Each week, Jim McKeeth and myself are hosting a live coding stream at LiveEdu.tv in which we build projects and generally fool around. Last week we began a new project which involves connecting to a database and executing SQL against it. During last weeks stream, as I was doing my thing inserting SQL statements into constants in the source code, a suggestion came from one of the viewers.

Jens Borisholt suggested that SQL statements could be stored in their own source files, and then linked into the project using the resource compiler. He was kind enough to put together a sample application and even demonstrated that sample application on the stream for us.

Jens clever use of the resource compiler to inject the dependencies, spares us from altering the SQL statements in order to inject them into the code, separates the SQL cleanly from the other sources, and even provides a convenient way to organize the project for easier navigation.

Setting up..

Start a new blank application (VCL or Multi-device) and from the “Data Explorer” tab under the project manager on the right, drag a connection to the employee database onto your form…

Now drop a TFDQuery component on the same form, and set it’s “Connection” property to “EmployeeConnection”

Now drop a memo control on your form and position it with some space to display names from the database..

Lets write some code to select names from the employees database and display them in the memo. Select your form and set the OnCreate() event to the following…

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDQuery1.SQL.Text := 'select FIRST_NAME, LAST_NAME from employee;';
  FDQuery1.Active := True;
  if not FDQuery1.Active then begin
    ShowMessage('Connection to database failed.');
    exit;
  end;
  Memo1.Lines.Clear;
  FDQuery1.First;
  while not FDQuery1.EOF do begin
    Memo1.Lines.Add(
      FDQuery1.FieldByName('FIRST_NAME').AsString+' '+
      FDQuery1.FieldByName('LAST_NAME').AsString
    );
    FDQuery1.Next;
  end;
end;

If you now run your application, you should see the names extracted from the database, like this…

The Problem

So what’s the problem here? Well it comes down to this one line of code…

FDQuery1.SQL.Text := 'select FIRST_NAME, LAST_NAME from employee;';

You see, the SQL text is injected into the code as a literal constant. In our simple example there is no real issue doing this, we built the application to demonstrate the solution rather than the problem. In any real application using SQL statements, the constants quickly become larger, multi-lined, include quote characters which must be escaped, and effectively become difficult to manage in source code.

Consider this create table statement for example…

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

When injected into pascal code, it looks something like this…

const cPersons = 
  'CREATE TABLE Persons (' +
  'PersonID int, '+
  'LastName varchar(255), '+
  'FirstName varchar(255), '+
  'Address varchar(255), '+
  'City varchar(255)  '+
  ');';

As you can see, it’s already less readable, but lets take a look at an insert statement for this table..

INSERT INTO Persons (PersonID,LastName,FirstName,Address,City)
  Values (1, 'Blogs', 'Joe', 'Somewhere', 'SomeCity' );

becomes..

const cInsert = 
  ' INSERT INTO Persons ( PersonID, LastName, FirstName, Address, City ) '+
  ' Values (1, ''Blogs'', ''Joe'', ''Somewhere'', ''SomeCity'' ); ';

Just look at all those doubled up quote marks!
If you were debugging this application and wanted to test a sql statement, you’d have to copy it from your source code, remove all the doubled up quotes, remove the surrounding quotes and constant declaration, and remove the ‘+’ operator and additional semi-colon. Having tested your SQL statement and presumably having made alterations to it, you now have to copy the statement back to your source code, and add back in all those decorations.

Wouldn’t it be nice if you could just keep the .SQL in a file and load it into your program? Well of course you can, but then this complicates the deployment of your application, you’d have to deploy the SQL files with your application for one thing, and in many cases you may wish to hide details of your data model by not sharing the SQL.

What we need is a way to inject our SQL into the source code in such a way that it’s isolated from the source, appears as plain un-decorated SQL text, and is not an external file…

Have your cake and eat it

Lets enhance our application to demonstrate the solution to the above problem, but first, save the sample application to disk as it’ll be helpful in a moment..

Scroll down and select .txt from the list…

This will create a new text file named “New1.txt” under your project, select that file and then save it as “select.sql”

Now, open the file and copy our SQL statement into it…
(* note this SQL is in your main form ‘OnCreate’ handler, you can copy from there *)

Now we have a .sql file contained within our project source code which may be browsed to within the project manager, and could even be saved into sub-directories within the project source. Importantly, it’s plain SQL and does not need to be decorated to be used within our project. However, it’s currently not actually connected to our project in any way, so lets correct that…

First create a new file using “File / New / Other” and selecting “Other Files / Text File” and again scroll down to “.txt”…

This time, save your new file as “sqlresource.rc”

Within the resource script file (sqlresources.rc), enter the following:

cSelect         SQL     ./select.sql

What we’re doing here is adding the .sql file into the resource script with an identifier. The left most column is an identifier by which the resource will be identified to our source code, separated by white-space (I use tabs) the next column identifies the type of resource which in this case is SQL, and the third column gives the path to our sql file relative to the resource script.

Important! Now right click the ‘sqlresources.rc’ file and remove it from your project.
Sometimes the IDE fails to add the resource file correctly, which causes an error at build time. The safest option to resolve this is to remove the resource file from your project, we’ll manually include it back in.

Right click on your project file and select, View Source…

Within your project source, add this line (after the program label)...

program SQLResourceExample;
{$R 'sqlresources.res' 'sqlresources.rc'}

This new line ({$R…) tells the compiler to include the resource script into the application. This means that each resource which is identified in the resource file, in our case select.sql, will be compiled into the application executable file using the identifier specified in the resource file (in our case “cSelect”).

So now when our project is built, it will include the sql resources specified in the resource script file, but we have one last step to complete and that is to load the sql statement into our query.

Switch back to the application main form source code, and add the following function:

function LoadResourceString(const aResourceName: string; aResourceType: string = 'SQL' ): string;
var
  streamResource: TResourceStream;
  SQL: TStringList;
begin
  streamResource := TResourceStream.Create(HInstance, aResourceName, pchar(aResourceType));
  try
    SQL := TStringList.Create;
    try
      streamResource.Position := 0;
      SQL.Clear;
      SQL.LoadFromStream(streamResource);
      Result := SQL.Text;
    finally
      SQL.DisposeOf;
    end;
  finally
    streamResource.DisposeOf;
  end;
end;

This function needs to be included only once, and can be included anywhere within your project so long as it’s in scope of any method that needs to call it. What it does is to load the resource who’s name is specified in the ‘aResourceName’ parameter as a stream, and then translate that stream into a string to be returned.

(*Note the second parameter ‘aResourceType’ is optional and defaults to ‘SQL’ which is the type we entered into our resource script.*)

Now, lets look back at that line of source which inserts SQL into our FDQuery component…

FDQuery1.SQL.Text := 'select FIRST_NAME, LAST_NAME from employee;';

Replace this line with this…

FDQuery1.SQL.Text := LoadResourceString('cSelect');

Finally, lets run the application once more…

Conclusion

Great, our application is doing the same thing again, but now the SQL is isolated out into a separate file. We can easily add more .SQL files to the project in the same way that we added this first one, just remember to open the .rc file back up and add a new line to include each new piece of SQL that you need.

Note: Once you have successfully built your application, you should also be able to add the .rc file back into your project without adverse effect. The fault appears to be a bug in the IDE, however, it only seems to affect the first build. Adding the file back into your project makes the .rc file convenient to edit in the IDE, having worked around bug

Also be aware that as of today (with RAD Studio 10.2 still in Beta), this feature is not supported under Linux. It makes sense too, since resources are loaded into a special section of the executable dedicated to resources. The Windows OS uses the PE (Portable Executable) file format for it’s executables, which supports this resource section. Linux (and just about all other supported targets) does not support the PE format file, but instead uses .ELF format, which does not have a resource section.

I have a cross-platform solution to this problem in my back pocket too, but it’s not quite ready so watch this space!

Thanks for Reading