Server Side Functions

Unofficial Content

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.

Description

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.

Functions that Can Be Evaluated by DBMS

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

Considerations

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.

SQL Server Specific

  • 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.

Oracle Specific

  • 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.

Scope

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