• support@ifer.cz
  • +420 2 4195 0607

Working with Field-Map data – using SQL queries

By Martin Cerny / Oct 16, 2016

Relational database management system (RDBMS) which is used for data storage if Field-Map supports SQL queries as a basic tool for querying and manipulating data. This is common for all RDBMS supported by Field-Map: FireBird, MSSQL, Access with minor differences in SQL syntax in each of them.

SQL (Structured Query Language) provides with multiple tools for virtualy any database operation. At least basic knowledge of SQL is required for work with it in Field-Map. Online internet help and internet schools of SQL are available on number of web pages such as http://www.w3schools.com/sql/.

Field-Map provides with standard access for SQL queries as well as with fully or partially wrapped commands for easy use. Methods for SQL queries are related to TFieldMapProjectWrapper and TFieldMapLayerWrapper:

TFieldMapProjectWrapper:
function GetQueryResult(const SQL_ :string) :TTableWrapper;
procedure PerformQuery(const SQL_ :string);

This is general tool for full SQL queries and commands.

TFieldMapLayerWrapper
function GetQueryResult(const Select_,Where_ :string) :TTableWrapper;
procedure Update(const Set_,Where_ :string);
function DeleteFrom(const Where_ :string) :integer;

function GetMaxOfAttributeValues(const AttributeName_,Where_ :string) :variant;
function GetMinOfAttributeValues(const AttributeName_,Where_ :string) :variant;

function GetMeanOfAttributeValues(const AttributeName_,Where_ :string) :variant;
function GetSumOfAttributeValues(const AttributeNameOrExpression_,Where_ :string) :variant;
function GetRecordCount(const Where_ :string) :integer;

function GetAttributeValue(const AttributeName_,Where_ :string) :variant;

Fully or partially wrapped SQL queries and commands of TFieldMapLayerWrapper simplify work with SQL. Two following provides wit equal functionality:

var MaxD :variant;
MaxD := Trees.GetMaxOfAttributeValues(‘DBH_mm’,’Species=1’);

with Project.GetQueryResult(format(‘SELECT MAX(DBH_mm) AS MaxDBH FROM Trees WHERE IDPlots=%d AND Species=1’,[Plots.ValueAsInteger[‘ID’]])) do
try
  MaxD := FieldByName(‘MaxDBH’).Value;
finally
  Free;
end;

Among layer querying tools the GetSumOfAttributeValues has special functionality. Often it is needed to calculate sum of an attribute inside OnChange script of that Attribute. The weak point of possible use of query for this purpose is that changed attribute value is not available to query unless record is posted. But since the values are handled directly inside OnChage script i.e. prior posting new value, standard query fails because it delivers wrong sum. However GetSumOfAttributeValues is built the way which make it usable even in such situation. Moreover, it can summarize not only values of some selected attribute but also sum of values calculated by SQL expression. Thus it can be used for instance for summarizing basal area of shoots upon the change of value of DBH of a shoot:

While the TFieldMapProjectWrapper commands are completely general SQL commands, the TFieldMapLayerWrapper commands together with more simplicity also provides with additional functionality required for Field-Map. This is particularly the case for SQL commands, such as UPDATE and DELETE.

Two following queries make same changes to the database:

Trees.Update(‘Species=1’,’ID IN (2,3)’);

or

Project.PerformQuery(format(‘UPDATE Trees SET Species=1 WHERE IDPlots=%d AND ID IN (1,2)’,[Plots.ValueAsInteger[‘ID’]]));

When performing UPDATE or DELETE command it is importat to refresh respective Field-Map table when that task is finished. Refresh command will reload the content of the table with newly updated data.

When performing queries the user must be aware of the fact that no OnValidate, OnChange scripts as well as layer scripts such as AfterEdit, AfterPost etc. are not performed automatically upon the change of the values in the database. The Field-Map scripting triggers are part of the Field-Map infrastructure; they do not belong to the database. This is common for both approaches.

But there is also one major difference between two approaches. The first one automatically makes also changes which are required for synchronization of data. It automatically records additional information which is necessary for synchronization, such as time stamp and user name. The second one makes same changes to the records of Trees but with no additional information indicating that the record has been changed and needs to be synchronized in next synchronization session. For those who work with synchronized projects this is very important because with the use of improper way the data may be lost in following synchronization. This is valid both for UPDATE and DELETE commands. For the project which are not being synchronized both approaches are equal.


You must be logged in to post a comment