Alan,

When you fetch a value from field of logical NUMERIC or DECIMAL type,
which is physically stored as SMALLINT, INTEGER or INT64,
you shall apply scaling - that's correct.

When you fetch a value from field of logical INTEGER or INT64 type -
you shall NOT apply scaling.

It is not exactly clear what do you mean by sending it to another
machine,
but what you usually do with fetched value - you either use it in
your application or use it in insert or update statement where
you specify it as string value, like INSERT ... VALUES( ..., 5.321 );

Regards,
Glebas



--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
wrote:

>
> In the API guide, under "Handling Numeric and Decimal Datatypes",

it

> discusses the requirement of adjusting retrieved values for the

scaling

> factor. I have some DSQL code which includes a Fetch function (see

below)

> As you may see, for DatTypes Short, Long and Int64 I test for the

factor and

> apply it if it exists (as the guide shows with example code)
>
> My problem is when I send thse fetched values to another database

withthe

> same datatype, the scaled value seems to have it's scaling applied

again. So

> 5.321 in a Numeric(7,3) field is retrieved as 53421 and scaled in

my code

> but the value then stored is 0.005 i.e. scaled upon storage.
> If, on the other hand I do not faactor the value upon fetching (see
> factoring code lines below commented out), and then store the

value in the

> destination DB, the value is stored correctly, as 5.321
>
> Can anyone shed some light on this? IS is the retrieval or the

strorage

> which is not expecting something or is it a change in the API when?
> thanks
> Alan McDonald
>
> function SQLFetch( hstmt :HSTMT) : RETCODE;export;
> var
> teb : isc_teb;
> retval : RETCODE;
> ret : isc_status;
> i,dtype,j: Integer;
> ovar,ivar: PXSQLVAR;
> BlobIdPtr,TmpPtr,BlobDataPtr : Pointer;
> bhndl : isc_blob_handle;
> SegLen : Short;
> TPtr,VarPtr : PChar;
> RInteger : ^Integer;
> BInteger : ^Int64;
> SShort : ^Short;
> factor,BlobSize : LongInt;
> BlobNode : PPtr;
> LenSize,LenValue : Word;
> PtrBool : ^Boolean;
> begin
> retval := SQL_SUCCESS;
> ret := isc_dsql_fetch( @(hstmt^.status), @(hstmt^.st_handle), 3,
> hstmt^.posqlda );
> if (ret <> 0) then begin
> if (ret = 100) then begin
> Dec(hstmt^.hdbc.OpenCursors);
> retval := 100; { No more rows to fetch }
> { Maybe some autocommit shit here in the future }
> end else
> retval := SQL_ERROR; { Error occurred on Fetch() }
> end else begin
> for i := 0 to (hstmt^.posqlda.sqld - 1) do begin
> ovar := @(hstmt^.posqlda.sqlvar[i]);
> dtype := ovar^.sqltype;
> if (ovar^.sqlind^ <> SQL_NULL) then { Is field null }

begin { No. }

> case dtype of
> SQL_DATE, SQL_DATE+1: begin
> {Translate IB dates into TM_STRUCTS }
> IBDateToTM( ovar^.sqldata, SearchLL

(hstmt^.ODateHead,i) );

> UpdateIsNullPtr(hstmt^.ODateHead,i,False);
> end;
> SQL_BLOB, SQL_BLOB+1: begin
> { We fetched the blob_id(ovar^.sqldata), now we need
> to get the blob data and copy it to the pointer
> given in SQLBindCol(hstmt^.OutBlobDataArr[i])}
> UpdateIsNullPtr(hstmt^.OBlobHead,i,False);
> BlobNode := BlobSearchLL(hstmt^.OBlobHead,i);
> BlobSize := IBBlobToPtr( hstmt,ovar^.sqldata,
> BlobNode^.Ptr,BlobNode^.SegSize);
> BlobNode^.TotalSize^ := BlobSize;
> end;
> SQL_TEXT,SQL_TEXT+1: begin
> UpdateIsNullPtr(hstmt^.OOthersHead,i,False);
> TPtr := PChar(ovar^.sqldata) + ovar^.sqllen;
> TPtr^ := Char(0);
> end;
> SQL_VARYING, SQL_VARYING+1: begin
> UpdateIsNullPtr(hstmt^.OVarying,i,False);
> VarPtr := SearchLL(hstmt^.OVarying,i);
> LenSize := sizeof(ovar^.sqllen);
> Move(ovar^.sqldata^,LenValue,LenSize);
> Move((PChar(ovar^.sqldata)+LenSize)^,VarPtr^,LenValue);
> (VarPtr + LenValue)^ := Char(0);
> end;
> SQL_LONG,SQL_LONG+1: begin
> UpdateIsNullPtr(hstmt^.OOthersHead,i,False);
> if (ovar^.sqlscale <> 0) then begin
> factor :=1;
> for j := 1 to -(ovar^.sqlscale) do
> factor := factor * 10;
> RInteger := ovar^.sqldata;
> // For some reason, there is no need to apply scale

factors

> // to numeric/decimal fields. They are replicated

exactly as

> is
> // rather than transforming them first
> // thus following line is commented out
> // RInteger^:= RInteger^ div factor;
> end
> end;
> SQL_INT64,SQL_INT64+1: begin
> UpdateIsNullPtr(hstmt^.OOthersHead,i,False);
> if (ovar^.sqlscale <> 0) then begin
> factor :=1;
> for j := 1 to -(ovar^.sqlscale) do
> factor := factor * 10;
> BInteger := ovar^.sqldata;
> // For some reason, there is no need to apply scale

factors

> // to numeric/decimal fields. They are replicated

exactly as

> is
> // rather than transforming them first
> // thus following line is commented out
> // BInteger^:= BInteger^ div factor;
> end
> end;
> SQL_SHORT,SQL_SHORT+1: begin
> UpdateIsNullPtr(hstmt^.OOthersHead,i,False);
> if (ovar^.sqlscale <> 0) then begin
> factor :=1;
> for j := 1 to -(ovar^.sqlscale) do
> factor := factor * 10;
> SShort := ovar^.sqldata;
> // For some reason, there is no need to apply scale

factors

> // to numeric/decimal fields. They are replicated

exactly as

> is
> // rather than transforming them first
> // thus following line is commented out
> // SShort^:= SShort^ div factor;
> end
> end;
> end;
> end else { It is null } begin
> case dtype of
> SQL_DATE, SQL_DATE+1:
> UpdateIsNullPtr(hstmt^.ODateHead,i,True);
> SQL_BLOB, SQL_BLOB+1:
> UpdateIsNullPtr(hstmt^.OBlobHead,i,True);
> SQL_VARYING, SQL_VARYING+1:
> UpdateIsNullPtr(hstmt^.OVarying,i,True);
> else
> UpdateIsNullPtr(hstmt^.OOthersHead,i,True);
> end;
> end;
> end;
> end;
> SQLFetch:= retval;
> end;
>