constructQuery()

You can use sqlLib.constructQuery() to return an SQL condition (to be used with a WHERE statement) based on a dictionary that contains comparison operators and values.

Syntax

  sqlLib.constructQuery(
    sqlRecord Record inOut,
    dictionary Dictionary inOut,
    matchByName BOOLEAN in )
  returns (condition STRING)
sqlRecord
Provides the SQL column names that correspond to each of the record fields and associated data types.
dictionary
Contains name-value pairs, where the names match field names from the sqlRecord element. The values contain operators and comparison values.
matchByName
If matchByName is set to YES, the dictionary names are matched with the sqlRecord element names and the query is generated. If matchByName is set to NO, the first element in the dictionary is used for the first element in the record and so on. EGL ignores a mismatch in the number of elements.
condition
Assign this to a STRING variable and use it in a WHERE clause. For more information, see "Example" in this topic.

Example

The following sample program creates a condition string (myCondition) from a customer record and a dictionary.

package com.companyb.gl;

record CustomerRecord type SQLRecord  
   {tableNames = [["ADMINISTRATOR.CUSTOMER", "L1"]], 
    keyItems = [customerNumber]}
   
   customerNumber STRING      {column="C_NUMBER", maxLen=6};
   customerName STRING        {column="C_NAME", isSQLNullable=yes, maxLen=25};
   customerAddr1 STRING       {column="C_ADDR1", isSQLNullable=yes, maxLen=25};
   customerAddr2 STRING       {column="C_ADDR2", isSQLNullable=yes, maxLen=25};
   customerAddr3 STRING       {column="C_ADDR3", isSQLNullable=yes, maxLen=25};
   customerBalance MONEY      {column="C_BALANCE", isSQLNullable=yes};
   
end

program calc3
myDictionary Dictionary {};
myCustomer CustomerRecord;
myCondition STRING;

   function main()
   
      myDictionary.customerNumber = ">A20045";
      myDictionary.customerBalance = "!= 0";
      
      myCondition = sqlLib.constructQuery(myCustomer, myDictionary, YES);
      prepare stmtID from "SELECT * FROM customer WHERE " +  myCondition;
      get customerArray with stmtID;
      
   end // main
end // program

The contents of myCondition are "C_NUMBER > 'A20045' AND C_BALANCE <> 0".


Feedback