KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Data type match matters when calling 4D methods from SQL statements
PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win
Published On: July 30, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

When constructing an SQL statement that will include a 4d_function_call, it is important to ensure that the data types between your method's return value and your SQL data type match. Specifically note what is specified in the SQL statement and what the $0 variable is declared to be in the 4D method so these two values agree. This is especially important if the SQL statement is going to be executed on an external 4D database.

Consider the function:

`Method name: Find_Nr_Of_Actors
C_LONGINT($0;$Count_L)
C_LONGINT($MovieID_L;$1)

$MovieID_L:=$1

SET QUERY DESTINATION(Into variable;$Count_L)
QUERY([MOVIE_ACTOR];[MOVIE_ACTOR]Movie_ID=$MovieID_L)
$0:=$Count_L


If called internally from an SQL statement that includes {fn Find_Nr_Of_Actors(ID) AS NUMERIC}, it will work as expected; 4D will handle the type mismatch.

However, if the SQL statement is being executed on an external 4D database, there is a potential mismatch in the data types that could cause an error. In the case above the mismatch is:

  • The function is going to return a 4 byte value (C_LONGINT)
  • The SQL statement is expecting an 8 byte value (NUMERIC)

To correct this problem in the example above either the $0 variable needs to be declared as a C_REAL or the SQL statement needs to be changed to {fn Find_Nr_Of_Actors(ID) AS INT32}.