src/interfaces/ecpg/test/sql/dyntest.pgc

/* dynamic SQL test program
 */

#include <stdio.h>
#include <stdlib.h>

exec sql include sql3types;
exec sql include sqlca;
exec sql include ../regression;

static void
error (void)
{
  printf ("\n#%ld:%s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
  exit (1);
}

int
main ()
{
  exec sql begin declare section;
  int COUNT;
  int INTVAR;
  int INDEX;
  int INDICATOR;
  int TYPE, LENGTH, OCTET_LENGTH, PRECISION, SCALE, RETURNED_OCTET_LENGTH;
  int DATETIME_INTERVAL_CODE;
  char NAME[120], BOOLVAR;
  char STRINGVAR[1024];
  double DOUBLEVAR;
  char *QUERY;
  exec sql end declare section;
  int done = 0;

  exec sql var BOOLVAR is bool;

  ECPGdebug (1, stderr);

  QUERY = "select * from dyntest";

  exec sql whenever sqlerror
  do
      error ();

  exec sql allocate descriptor MYDESC;

  exec sql connect to REGRESSDB1;

  exec sql set datestyle to german;

  exec sql create table dyntest (name char (14), d float8, i int,
				 bignumber int8, b boolean, comment text,
				 day date);
  exec sql insert into dyntest values ('first entry', 14.7, 14, 123045607890, true, 'The world''''s most advanced open source database.', '1987-07-14');
  exec sql insert into dyntest values ('second entry', 1407.87, 1407, 987065403210, false, 'The elephant never forgets.', '1999-11-5');

  exec sql prepare MYQUERY from :QUERY;
  exec sql declare MYCURS cursor for MYQUERY;

  exec sql open MYCURS;

  while (1)
    {
      exec sql fetch in MYCURS into sql descriptor MYDESC;

      if (sqlca.sqlcode)
	break;

      exec sql get descriptor MYDESC:COUNT = count;
      if (!done)
	{
	  printf ("Found %d columns\n", COUNT);
	  done = 1;
	}

      for (INDEX = 1; INDEX <= COUNT; ++INDEX)
	{
	exec sql get descriptor MYDESC value :INDEX
		:TYPE = type,
		:LENGTH = length,
		:OCTET_LENGTH = octet_length,
		:RETURNED_OCTET_LENGTH = returned_octet_length,
		:PRECISION = precision,
		:SCALE = scale,
		:NAME = name,
		:INDICATOR = indicator;
	  printf ("%2d\t%s (type: %d length: %d precision: %d scale: %d = " , INDEX, NAME, TYPE, LENGTH, PRECISION, SCALE);
	  switch (TYPE)
	    {
	    case SQL3_BOOLEAN:
	      printf ("bool");
	      break;
	    case SQL3_NUMERIC:
	      printf ("numeric(%d,%d)", PRECISION, SCALE);
	      break;
	    case SQL3_DECIMAL:
	      printf ("decimal(%d,%d)", PRECISION, SCALE);
	      break;
	    case SQL3_INTEGER:
	      printf ("integer");
	      break;
	    case SQL3_SMALLINT:
	      printf ("smallint");
	      break;
	    case SQL3_FLOAT:
	      printf ("float(%d,%d)", PRECISION, SCALE);
	      break;
	    case SQL3_REAL:
	      printf ("real");
	      break;
	    case SQL3_DOUBLE_PRECISION:
	      printf ("double precision");
	      break;
	    case SQL3_DATE_TIME_TIMESTAMP:
	    exec sql get descriptor MYDESC value :INDEX
	    	:DATETIME_INTERVAL_CODE = datetime_interval_code;
	      switch (DATETIME_INTERVAL_CODE)
		{
		case SQL3_DDT_DATE:
		  printf ("date");
		  break;
		case SQL3_DDT_TIME:
		  printf ("time");
		  break;
		case SQL3_DDT_TIMESTAMP:
		  printf ("timestamp");
		  break;
		case SQL3_DDT_TIME_WITH_TIME_ZONE:
		  printf ("time with time zone");
		  break;
		case SQL3_DDT_TIMESTAMP_WITH_TIME_ZONE:
		  printf ("timestamp with time zone");
		  break;
		}
	      break;
	    case SQL3_INTERVAL:
	      printf ("interval");
	      break;
	    case SQL3_CHARACTER:
	      if (LENGTH > 0)
		printf ("char(%d)", LENGTH);
	      else
		printf ("text");
	      break;
	    case SQL3_CHARACTER_VARYING:
	      if (LENGTH > 0)
		printf ("varchar(%d)", LENGTH);
	      else
		printf ("varchar()");
	      break;
	    default:
	      if (TYPE < 0)
		printf ("<OID %d>", -TYPE);
	      else
		printf ("<SQL3 %d>", TYPE);
	      break;
	    }
	  printf (")\n\toctet_length: %d returned_octet_length: %d)\n\t= ",
		  OCTET_LENGTH, RETURNED_OCTET_LENGTH);
	  if (INDICATOR == -1)
	    printf ("NULL\n");
	  else
	    switch (TYPE)
	      {
	      case SQL3_BOOLEAN:
	      exec sql get descriptor MYDESC value :INDEX :BOOLVAR = data;
		printf ("%s\n", BOOLVAR ? "true" : "false");
		break;
	      case SQL3_INTEGER:
	      case SQL3_SMALLINT:
	      exec sql get descriptor MYDESC value :INDEX :INTVAR = data;
		printf ("%d\n", INTVAR);
		break;
	      case SQL3_DOUBLE_PRECISION:
	      exec sql get descriptor MYDESC value :INDEX :DOUBLEVAR = data;
		printf ("%.*f\n", PRECISION, DOUBLEVAR);
		break;
	      case SQL3_DATE_TIME_TIMESTAMP:
	      exec sql get descriptor MYDESC value :INDEX
	      	:DATETIME_INTERVAL_CODE = datetime_interval_code,
		:STRINGVAR = data;
		printf ("%d \"%s\"\n", DATETIME_INTERVAL_CODE, STRINGVAR);
		break;
	      case SQL3_CHARACTER:
	      case SQL3_CHARACTER_VARYING:
	      exec sql get descriptor MYDESC value :INDEX :STRINGVAR = data;
		printf ("\"%s\"\n", STRINGVAR);
		break;
	      default:
	      exec sql get descriptor MYDESC value :INDEX :STRINGVAR = data;
		printf ("<\"%s\">\n", STRINGVAR);
		break;
	      }
	}
    }

  exec sql close MYCURS;

  exec sql deallocate descriptor MYDESC;

  return 0;
  }