API/Plug-in Center

Tutorials

Creating and Working with Queries

The 4D API contains a series of Query calls that you can use to develop your own Query commands. In 4D, you would use a command such as QUERY, but when using the API you must create your Query from the ground up by Opening the Query, Defining what is to be Queried, and finally Closing the Query. It may take a few more steps than with the 4D command QUERY, but after using the commands you will find it rather straightforward.

Opening ("Creating") a Query

To initialise a query using the API, you must call the command PA_OpenQuery. The only parameter to this command is the Table Number you wish to perform the Query. This command will return a PA_QueryRef.

NOTE: The API documentation does not mention the PA_QueryRef value in any of the commands. PA_OpenQuery will return this value, and all subsequent Query calls will use this value as the first parameter with the exception of PA_CloseQuery.

To use PA_OpenQuery, first define the return value as a PA_QueryRef and pass the table number with which you wish to perform the Query.

Void returnQuery (long TableNum)
{
PA_QueryRef QueryRef;

QueryRef = PA_OpenQuery(TableNum);
.
.
.
}

Defining the Query

After you have initialised or "opened" the query, it is time to define what values are to be queried. In the API you can query for Boolean, Date, Integer, Longint, Real, String, and Time values. There are two important operators that are in each of these query calls: the PA_QueryOperator and the PA_QueryComparision.

The PA_QueryOperator tells 4D the logical connection between multiple query lines. You are probably familiar with AND, OR, and Except. There is one additional operator that is needed and that is NoOperator. The NoOperator is always used in the first line of a query definition. The format definitions are eQO_NoOperator, eQO_LogicalAND, eQO_LogicalOr, and eqO_Except. These definitions can be found in the PublicTypes.h file of the 4D API files.

The PA_QueryComparision tells 4D how to perform the query. For instance, are you looking for equal values, greater than values, or less than values? The formal definitions for these values are eQC_IsEqual, eQC_IsDifferent, eQC_IsGreater, eQC_IsGreaterOrEqual, eQC_IsLess, eQC_IsLessOrEqual, eQC_Contains, and eQC_NotContains.

Let's take a look at the PA_QueryLongint command.

PA_QueryLongint(PA_QueryRef, TableNum, FieldNum, PA_QueryOperator, PA_QueryComparison, valueToSearch);

The first five parameters to the query commands should always be of the same type, Query Reference, Table Number, Field Number, Query Operator, and Query Comparison. The last value, valueToSearch is that which you wish to Query on. The valueToSearch may be different for different types of Queries. For example when querying on a date you would also pass the day, month, and year to the query command.

Closing the Query

The last step of creating a query is to close the query. This notifies 4D that you are done and sends the query to 4D to process. All you need to do to close a query is call the command PA_CloseQuery. The command takes no parameters.

Putting It All Together

Now let's put all the steps together and look at some examples of how to use the Query API commands.

In our first example, let's create a query that is simply looking for a longint value that is greater that 100.

Void firstExample (long TableNum, long FieldNum)
{
   PA_QueryRef queryReference;

   QueryReference = PA_OpenQuery(TableNum);
   PA_QueryLongint(queryReference, TableNum, FieldNum, eQO_NoOperator, eQC_IsGreater, 100);
  PA_CloseQuery();

}

Notice in this example when we call PA_QueryLongint we are passing the eQC_NoOperator. This operator is always on the first line of a query, even if there is only one line to the query. In subsequent examples you will see this operator used.

In our next example, let's expand things a bit, Now let's query for a longint that is greater than or equal to a value passed in as a parameter to the method, where we are also looking for a date greater than a value passed as a parameter.

Void secondExample(long TableNum, long FieldNum, long longintValue, PA_Date dateValue)
{
   PA_QueryRef queryReference;

   QueryReference = PA_OpenQuery(TableNum);

   PA_QueryLongint(queryReference, TableNum, FieldNum, eQO_NoOperator, eQC_IsGreaterOrEqual, LongintValue);

   PA_QueryDate(queryReference, TableNum, FieldNum, eQO_LogicalAND, eQC_IsGreater, &DateValue.fDay, &DateValue.fMonth, &DateValue.fYear);

   PA_CloseQuery();

}

Notice that in our call to PA_QueryDate, we use eQO_LogicalAND to specify that we are looking to tie the two queries together with an AND query. Also note how we use the PA_Date structure. Instead of passing the date in as three separate parameters, we use the 4D structure of PA_Date and access the structure to get the different values for the Day, Month, and Year.

As we mentioned at the beginning queries with the 4D API are rather straightforward once you get use to the syntax of the commands. For all queries you have an opening, PA_OpenQuery, the query commands, PA_QueryXxx, and a closing, PA_CloseQuery.

International | Company | Contact 4D | Site Map | Privacy Policy | © 4D UK 1995-2008 | Change font size: [A] [A] [A] |