As from GeneXus 9.0, the 'for each' group conditions involving horizontal formulas and/or functions will be evaluated by the database manager, thus optimizing the number of data returned to the client by these statements.
In GeneXus 8.0 this feature was available only for SQL Server.
In Client/Server applications, the 'for each' groups used to access data are solved with a SQL statement that is executed in the server and returns data to the client program for processing.
The general structure of these statements is the following:
SELECT <attributes> FROM <tables> WHERE <conditions>
Up to GeneXus 8.0, the functions (with the exception of Null and NullValue and when the DBMS was not SQL Server) were not part of the conditions sent to the server; instead, they were solved by the program.
As from this version, a list of functions that can be evaluated by the database handler is incorporated, thus optimizing the access to them. This applies to Db2 UDB, DB2 UDB for iSeries, MySQL, Oracle, SQL Server, SQL CE.
The same happened with horizontal formulas, as until GeneXus 7.5, the horizontal formulas involved in conditions were not sent to the server.
Below are the functions that can be evaluated in the server for the different DBMS.
| GeneXus Function |
SQLServer |
Oracle |
MYSQL |
DB2 |
DB2 ISERIES |
DBMS Function |
| Dates Handling |
|
| Day |
Yes |
Yes |
Yes |
Yes |
Yes |
day |
| Month |
Yes |
Yes |
Yes |
Yes |
Yes |
month |
| Year |
Yes |
Yes |
Yes |
Yes |
Yes |
year |
| Hour |
Yes |
Yes |
Yes |
Yes |
Yes |
hour |
| Minute |
Yes |
Yes |
Yes |
Yes |
Yes |
minute |
| Second |
Yes |
Yes |
Yes |
Yes |
Yes |
second |
| AddMth |
Yes |
NO |
Yes |
Yes |
Yes |
addmth |
| AddYr |
Yes |
Yes |
Yes |
Yes |
Yes |
addyr |
| Operator |
Yes |
Yes |
Yes |
Yes |
Yes |
addday |
| Age |
Yes |
Yes |
Yes |
Yes |
Yes |
age |
| Dow |
Yes |
NO |
Yes |
Yes |
Yes |
dow |
| Eom |
Yes |
Yes |
NO |
NO |
NO |
eom |
| Numeric Values Handling |
|
| Int |
Yes |
Yes |
Yes |
Yes |
Yes |
int |
| Val |
Yes |
Yes |
Yes |
Yes |
Yes |
val |
| Round |
Yes |
Yes |
Yes |
Yes |
Yes |
round |
| Trunc |
Yes |
Yes |
Yes |
Yes |
Yes |
trunc |
| Strings Handling |
|
| Asc |
Yes |
Yes |
Yes |
Yes |
Yes |
asc |
| Len |
Yes |
Yes |
Yes |
Yes |
Yes |
len |
| Lower |
Yes |
Yes |
Yes |
Yes |
Yes |
lower |
| Ltrim |
Yes |
Yes |
Yes |
Yes |
Yes |
ltrim |
| Padl |
Yes |
Yes |
Yes |
Yes |
Yes |
padl |
| Padr |
Yes |
Yes |
Yes |
Yes |
Yes |
padr |
| Rtrim |
Yes |
Yes |
Yes |
Yes |
Yes |
rtrim |
| Str |
Yes |
Yes |
Yes |
NO |
NO |
str |
| Strreplace |
Yes |
Yes |
Yes |
Yes |
Yes |
strreplace |
| Strsearch |
Yes |
Yes |
Yes |
Yes |
Yes |
strsearch |
| Strsearchrev |
Yes |
Yes |
NO |
NO |
NO |
strsearchrev |
| Substr |
Yes |
Yes |
Yes |
Yes |
Yes |
substr |
| Trim |
Yes |
Yes |
Yes |
Yes |
Yes |
trim |
| Upper |
Yes |
Yes |
Yes |
Yes |
Yes |
upper |
The fact that more conditions can be evaluated in the server may cause it to evaluate them differently from how GeneXus evaluated them in the client, mainly as regards to nulls and data accuracy.
The developer must take care of the fact that the functions return valid values. Otherwise there will be execution errors that cannot be captured.
- Regarding the VAL function, if the character type expression does not correspond to a numeric value, an execution error occurs: MicrosoftODBC SQL Server DriverSQL Server Error converting data type varchar to numeric.
- The behavior of the function LEN, STR when solved by SQL Server is different than when solved by the client. The server does not take into account blank spaces but the client does. Therefore, results returned by queries involving this operator may be different.
- The comparison between characters takes into account the length of the operands. To have the same behavior in the client and in the server you have to use the TRIM function.
Objects: Transactions, Work Panels, Web Panels, Procedures and Reports
Languages: Java, .NET, Visual Basic, .NET Mobile, Visual FoxPro
Interfaces: Web, Win
DBMS: DB2 UDB, DB2 UDB for iSeries, MySQL, Oracle, SQL Server, SQL CE