Building a query with the API

version 2003

The API gives the plug-in developer routines to build and perform a search within a table.

To perform a search, the developer will use 3 steps.

	1/ Prepare the search by calling PA_OpenQuery,
	2/ Build it by calling PA_QueryXxx, where Xxx is the kind of the field to search (String, integer, Date, long integer) for each line that the the searching operation request
	3/ Launch the search by calling PA_CloseQuery and checking its result.

You can have up to 32000 lines of search. Beyond this limit, an error is returned.

To dispatch the standard 4th Dimension Search dialog box, use PA_QueryDialog.

The plug-in first initiates the query by calling PA_OpenQuery(tNum), specifying the table where the search must be done. This call tells the API to perform private initialization before receiving the lines of the search.

Then it calls PA_QueryXxx (up to 20 times), where Xxx is a kind of field, for each fields it needs to make the search. The API gives the developer accessors for every field that can be searched: PA_QueryString, PA_QueryDate, PA_QueryBoolean (blob and Pictures field cannot be searched). The first added line must have the query operator constant eQO_NoOperator (see below).

At the end, the plug-in calls PA_CloseQuery, which starts the search and de-initializes any private data.

The PA_QueryOperator parameter of PA_QueryXxx indicates the logical connection between a line of search definition and the temporarily result brought by the previous line(s). The possible values for this field are defined in PublicTypes.h C header file:

// Values for the field fOperator of LineBlock data structure
// These values define the logical connection between the lines of a query definition
typedef enum
{
   eQO_NoOperator = 0,   // Always 1st line of a query definition
   eQO_LogicalAND,   // AND     (& operator)
   eQO_LogicalOR,   // OR      (| operator)
   eQO_Except   // EXCEPT  (# operator)
} PA_QueryOperator;

The PA_QueryOperator parameter must always be set to eQO_NoOperator for the first line of a search definition. The API takes handles this and always sets the first operator to eQO_NoOperator.

The 4D plug-in passes to PA_QueryXxx the table and field numbers on which the search will be performed. Automatic many-to-one relations can be used during a search.

Picture, BLOB, and SubTable Fields cannot be searched. If this is attempted the line will be ignored and no error will be returned.

The PA_QueryComparison parameter of the PA_QueryXxx routines indicates the comparison operator for the line. The possible values for this field are defined in the PublicTypes.h C header file:

// Values for the field fComparison of LineBlock data structure
// These values define the comparison operator for the line of query definition   
typedef enum
{
   eQC_NoOperation = 0,      // No query line can have this value
   eQC_IsEqual,      // Is equal to
   eQC_IsDifferent,      // Is not equal to
   eQC_IsGreater,      // Is greater than
   eQC_IsGreaterOrEqual,      // Is greater or equal to
   eQC_IsLess,      // Is less than
   eQC_IsLessOrEqual,      // Is less or equal to
   eQC_Contains,      // Contains
   eQC_NotContains      // Does not contains
} PA_QueryComparison;

Depending on the type of the Field for the line, certain operations are not permitted. The table below summarizes the possible operations for each Field type:

Field Type		Valid search operator
	>, >=	<, <=	Contains	=, #
			Doesn't contain
Alphanumeric	•	•	•	•
Text	•	•	•	•
Real	•	•		•
Integer	•	•		•
Long Integer	•	•		•
Date	•	•		•
Time	•	•		•
Boolean	•	•		•

If the 4D plug-in specifies another logical operator for the first line or specifies

eQC_NoOperation

Depending on the type of the Field for a line the 4D plug-in calls the appropriate PA_QueryXxx routine ((PA_QueryString, PA_QueryDate)

- Although alphanumeric fields may contain up to 80 characters, and text fields may contains up to 32000 characters, the 4D plug-in can only specify up to 40 characters per line in a search definition.

- Time expressions are expressed as a sum of seconds. For example a delay of 1 hour 1 MN 1 sec is represented by 4th Dimension as the numeric value 3600 + 60 + 1 = 3661 seconds.

During the search operation, and depending on previous calls to PA_Get/Set/MessagesStatus, the thermometer window may be displayed. If it is displayed, then the user may interrupt the operation by clicking on the Cancel button. In such case, PA_GetLastError() returns eER_NoErr to signal that the search was correctly started even though it may have been interrupted. In addition PA_GetLastError() also returns eER_NoErr when the user clicks on the "Cancel" button of the search dialog (PA_QueryEditor).

Consequently to check if a search operation has been successfully completed, thePlugin must test both the PA_GetLastError() and the 4th Dimension OK System Variable (Note that if the thermometer window is not displayed, you can just check PA_GetLastError). This can be accomplished using PA_GetVariable. If the search correctly completed, the OK System Variable is set equal to 1. If the search was not corrected the OK System Variable is set equal to 0.

The code of a utility routine that returns the current value of OK is shown later.

Given a database whose second Table is [Pizzas], whose fourth field is the date of creation of the record. To search all Pizzas created during the year 2000, you could use this code (which assumes that there is no invisible Field) that returns an error number and set *recFound to the number of found records:

short FindY2KPizzas (long *recFound)
{
   short   err;
   PA_QueryRef ref;

// Initialize the number of founded records
   *recFound = 0L;

// Prepare the query
   ref = PA_OpenQuery(2);

// Serach [Pizzas] during year 2000
   // IMPORTANT: FIRST LINE USES eQO_NoOperator
   PA_QueryDate(ref, 2, 4, eQO_NoOperator, eQC_IsGreaterOrEqual, 1, 1, 2000);
   PA_QueryDate(ref, 2, 4, eQO_LogicalAND, eQC_IsLessOrEqual, 31, 12, 2000);

// Start the search
   PA_CloseQuery(ref);

// get the result
   err = PA_GetLastError();
   if(!err)
   {
   // No error in the search. Check if the user cancelled the search
   // by checking the value of the System Variable OK
      if(GetOK()) // The search was not cancelled
         *recFound = PA_RecordsInSelection(2);
   }

   return err;
   

} //

FindY2KPizzas

char GetOK ()
{
   PA_Variable   ok;
   char         okValue = (char) 0;
   
// Note that in interpreted mode, OK may be undefined
   ok = PA_GetVariable("OK"); // using C strings here

   if(PA_GetLastError() == eER_NoErr)
   {
      switch (PA_GetVariableKind(ok))
      {
         case eVK_Real:
            if (PA_GetRealVariable(ok) != 0.0 )
               okValue = 1;
            break;
         case eVK_Longint:
            if (PA_GetLongintVariable(ok) != 0)
               okValue = 1;
            break;
      }
   }
   
   return okValue;
}