Accessing Special Data Types

ECPG supports the numeric, decimal, date, timestamp, and interval data types. These data types cannot be mapped to primitive host variable types because they have a complex internal structure. Applications deal with these types by declaring host variables in special types and accessing them using functions in the pgtypes library. For details about the functions in the pgtypes library, see ECPG API Reference.

• timestamp and date
  First, the program must include the header file for the timestamp type.

  #include <pgtypes_timestamp.h>

  Then, declare a host variable of the timestamp type in the DECLARE section.

  EXEC SQL BEGIN DECLARE SECTION;
      timestamp ts;
  EXEC SQL END DECLARE SECTION;

  After the value is read to the host variable, the pgtypes library function is used for processing. In the following example, the PGTYPEStimestamp_to_asc() function is used to convert the timestamp value to the text format:

  EXEC SQL SELECT now()::timestamp INTO :ts;
  printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));

  A command output example is as follows:

  ts = 2022-06-27 18:03:56.949343

  In addition, the date type can be processed in the same way. The program must contain the pgtypes_date.h header file, declare a host variable as the date type, and use the PGTYPEdata_to_asc() function to convert the variable to the text format.

• interval

  The handling of the interval type is also similar to the timestamp and date types. However, to allocate memory for an interval type value explicitly, the memory space for the variable must be allocated from the heap memory.

  For example:

  #include <stdio.h>
  #include <stdlib.h>
  #include <pgtypes_interval.h>
  int main(void)
  {
  EXEC SQL BEGIN DECLARE SECTION;
      interval *in;
  EXEC SQL END DECLARE SECTION;
      /* Connect to the testdb database. The testdb database must be created in advance.*/
      EXEC SQL CONNECT TO testdb;
      in = PGTYPESinterval_new();
      EXEC SQL SELECT '1 min'::interval INTO :in;
      printf("interval = %s\n", PGTYPESinterval_to_asc(in));
      PGTYPESinterval_free(in);
      EXEC SQL COMMIT;
      EXEC SQL DISCONNECT ALL;
      return 0;
  }

• numeric and decimal

  The handling of the numeric and decimal types is similar to the interval type: It requires defining a pointer, allocating some memory space from the heap, and accessing the variable using the pgtypes library functions.

  Example:

  #include <stdio.h>
  #include <stdlib.h>
  #include <pgtypes_numeric.h>
  EXEC SQL WHENEVER SQLERROR STOP;
  int main(void)
  {
  EXEC SQL BEGIN DECLARE SECTION;
      numeric *num;
      numeric *num2;
      decimal *dec;
  EXEC SQL END DECLARE SECTION;
      /* Connect to the testdb database. The testdb database must be created in advance.*/
      EXEC SQL CONNECT TO testdb;
  
      num = PGTYPESnumeric_new();
      dec = PGTYPESdecimal_new();
  
      EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec;
      printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0));
      printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1));
      printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2));
      /* Convert decimal to numeric to show a decimal value. */
      num2 = PGTYPESnumeric_new();
      PGTYPESnumeric_from_decimal(dec, num2);
      printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0));
      printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1));
      printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2));
      PGTYPESnumeric_free(num2);
      PGTYPESdecimal_free(dec);
      PGTYPESnumeric_free(num);
  
      EXEC SQL COMMIT;
      EXEC SQL DISCONNECT ALL;
      return 0;
  }