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;