Path: utzoo!attcan!uunet!dev!dgis!jkrueger
From: jkrueger@dgis.dtic.dla.mil (Jon)
Newsgroups: comp.databases
Subject: Re: Ingres money functions?
Message-ID: <879@dgis.dtic.dla.mil>
Date: 11 Jun 90 18:52:36 GMT
References: <147@stephsf.UUCP>
Organization: Defense Technical Information Center (DTIC), Alexandria VA
Lines: 130

wengland@stephsf.UUCP (Bill England) writes:

> In Ingres esql my query is returning a type money into a c type of double.
> How can I manipulate the money value??  ie add, sub, mul ??
> How can I convert it into a float or truncate it into an integer?

> Code examples would be greatly appreciated.

The following has been tested on Pyramid 98x under OSx 4.4 on both
INGRES 5.0/05a (pyr.u42/04) and INGRES 6.1/01u (pyr.u42/04).  This
does not imply a commitment by Ingres, Pyramid, the Department of
Defense, or J. R. "Bob" Dobbs.

Simple single table database:

      +-----+
      | emp |   name        salary
      +-----+----------+-------------+
            |   Huck   |    $10000.00|
            |   Tom    |    $50000.00|
            |   Sid    |     $5000.00|
            +------------------------+

ESQL/C code:

#include <stdio.h>
#define		NOT_FOUND	100
exec sql include sqlca;

main(argc,argv) 
int argc;
char *argv[];
{
	exec sql connect testdb1;
	list_table();
	exec sql disconnect;
}

list_table()
{
	exec sql begin declare section;
		char	emp_name[12];
		double	emp_sal;
	exec sql end declare section;

	exec sql declare csr cursor for
		select trim(name), salary from emp;
	exec sql open csr;
	if (sqlca.sqlcode < 0) ing_error("can not open cursor csr");

	while (!sqlca.sqlcode) {
		exec sql fetch csr into :emp_name, :emp_sal;
		if (sqlca.sqlcode < 0)
			ing_error("can not fetch into cursor csr");
		else if (sqlca.sqlcode != NOT_FOUND)
			list_row(emp_name, emp_sal);
		else
			break;
	}
	exec sql close csr;
}


list_row(name, sal)
char	*name;
double	sal;
{
	float	f_sal;
	int	int_sal;
	char	str_sal[20];

	f_sal = sal;
	int_sal = sal;
	sprintf(str_sal, "%d", (int) sal);
	printf("emp: %s sal: %.2f = %.2f = %d = %d digits\n",
		name, sal, f_sal, int_sal, strlen(str_sal));
	printf("     +1K: %.2f -1K: %.2f doubled: %.2f\n",
		sal + 1000.0, sal - 1000.0, sal * 2.0);
}

ing_error(string)
char *string;
{
	error(string);
	exec sql disconnect;
	exit(-3);
}

error(string) 
char string[];
{
	exec sql begin declare section;
		char errmsg[258];
		char *err_stmt = string;
	exec sql end declare section;

	fprintf(stderr,"ERROR: %s\n", string);
	exec sql copy sqlerror into :errmsg WITH 256;
	fprintf(stderr, "Aborting with error:\n%s\n%s\n", err_stmt, errmsg);
}

The output from that code running on that database:

	emp: Huck sal: 10000.00 = 10000.00 = 10000 = 5 digits
     	+1K: 11000.00 -1K: 9000.00 doubled: 20000.00
	emp: Tom sal: 50000.00 = 50000.00 = 50000 = 5 digits
     	+1K: 51000.00 -1K: 49000.00 doubled: 100000.00
	emp: Sid sal: 5000.00 = 5000.00 = 5000 = 4 digits
     	+1K: 6000.00 -1K: 4000.00 doubled: 10000.00

Warnings:

Money is NOT the same as float; rather, database objects of type money
are returned into C objects of type float.  Floats underflow; money
columns do not.  Floats lose precision; money maintains fixed point
precision.  However, the limits of IEEE double precision floats are
about those of the current INGRES money ADT.  The range of the floats
is much higher, however.  It would be possible, for instance, to add up
everyone's salary into a float which could then be too large to store
into the database in a money column.  This is a specific example of a
more general problem of better query languages, programming languages,
and interfaces between them.  While we're waiting for them to arrive,
be ye warned!  Floats are subtle and quick to anger, and do not behave
identically to money.  They are the "least worst" choice for money
in most currently available general purpose programming languages.

-- Jon
-- 
Jonathan Krueger    jkrueger@dtic.dla.mil   uunet!dgis!jkrueger
Drop in next time you're in the tri-planet area!