How to load decimals using copy from program in the IIAPI

[randolph]

I'm still having problems understanding IIAPI precision but now the problem is in the copy.

I shunt data between machines. A select is done on machine A and the result data is moved to machine B, which uploads it using the copy-from-program technique.

Normally this is fine, but when the select results in a decimal (eg "select 1220/1.0")
I am having trouble loading it correctly in to the destination DB on machine B, even though the destination machine's table is expecting a decimal(18,17).
The result displays as 0.0000001220000...

So I assume that I must send the precision and scale to the copy.
However, Its not at all clear from the documentation how to do this.
The cp_fileDescr.fd_prec expects an undocumented format and both it and cp_dbmsDescr.ds_scale segfault when I try to use them.

Whats the proper way to solve this?

(VW 1.5 on Ubuntu 10.10)

[kschendel]

The cp_dbmsDescr info describes the table data. The cp_fileDescr info describes what you put into the COPY statement as the contents of the data file. What the program has to do is convert the data from cp_fileDescr type to cp_dbmsDescr type.

cp_fileDescr.fd_prec has the precision in the high byte and the scale in the low byte, i.e. it's (prec*256)+scale. In the IIAPI_DESCRIPTOR (cp_dbmsDescr), the precision and scale are broken out separately for you.

The copy map includes all the file datatype info so that a generic copy front-end can be written. If your program knows the incoming data formats, you can ignore the cp_fileDescr part of the copy map, but it's still up to your program to convert the data into table row format (cp_dbmsDescr) in the proper column order. It sounds like you need to rescale the incoming decimal values to match whatever is in the table (decimal(18,17) apparently), and you'll need to deal with overflow somehow.

[randolph]

Thanks, that helps.

For clarity; I know that the table format will always be the correct size.
And the data coming from machine A is in internal api format, so I wouldn't have thought it needed conversion...

[thogo01]

For most SQL operations, the client describes the data it has and the DBMS converts parameters into whatever is needed for how the parameter is used.

The Ingres COPY statement is different in that it provides the format of the data to be passed. If the precision or scale is different between the two installations, then a conversion is required. If the precision is different, then the length of data storage is likely different (length is dependent on precision) and an incorrect length will cause all kinds of problems. If only the scale is different, then the result will be scaled improperly based on the difference in scale values.

You should be able to use IIapi_convertData() or IIapi_formatData() to convert from one decimal format to the other.

[randolph]

This is a bug in VW 1.5 (if not other versions)

Reproducible by:

Create table x (duration int);
insert into x values (12200);
create table y as select sum(duration / 1.0) from x;

this runs fine but:
select * from y;

returns :
E_VW1079 Packed decimal overflow

My program was invisibly caught up in this.

Is this known and/or fixed in a later version ?

[geraintjones]

It does look to be fixed in a later version.

Using 2.0.1 (a64.lnx/119)NPTL +p11903 I get the result:

select * from y;

col1
12200.000000000000000000000000000


GJ

[randolph]

Great. thanks all.