🚀 go-pugleaf

I've posted about this before but never got a reply. I am posting again in
the hope that someone can help me out.

Create a table in SQL*Plus like so:

SQL> CREATE TABLE mytab (mycol NUMBER(20,10));

and then insert a value into it

SQL> INSERT INTO mytab VALUES (1234.123456789);
SQL> COMMIT;

to check that the value is in there unmodified, make sure that numwidth is
set to something large:

SQL> SET NUMWIDTH 15;
SQL> SELECT * FROM MYTAB;

          MYCOL
---------------
 1234.123456789

Now fire up Borland Builder and create a button along with two labels, an
ADOConnection object and an ADOQuery object. Set this up to point at your
Oracle database using the Oracle OLE DB driver and add the following code:

void __fastcall TForm1::Button1Click(TObject *Sender)
{
  // connect
  myQuery->Close();
  myQuery->SQL->Clear();
  myQuery->SQL->Add("SELECT * FROM MYTAB");
  myQuery->Open();
  // read back field and assign it to the button's name
  myQuery->First();
  Label1->Caption = myQuery->FieldByName("MYCOL")->AsString;
  Label2->Caption = myQuery->FieldByName("MYCOL")->AsFloat;
  myQuery->Close();
  return;
}

Now if you run the code you will find that both Labels are set with the
value of 1234.1235.  I have searched the Methods and Properties for
something that would affect the return precision and came up with nothing.
I pretty sure that this is Borland and not Oracle since one of Oracles main
points is to store numbers up to 38 digits in length.

Since I can't fix it I have to work around it: The possibilities are to use
a string to store the value or to multiply the number by a fixed amount
before insertion and divide it again when the value comes out.  The first
possiblity has some very good merits - the first being that you can extract
the string straight to text file without needing conversion, or drop it
straight into an AnsiString and use it straight away within your code.  The
drawback to this method is that if some other programs/scripts access table
then they will have to be written to make the change.  This is hampered if
you are using a third party program where they expect a number to be
returned.  The second method is what we are using at the moment but that
gives us the problem of needing to detect which numbers have been multiplied
and which haven't.  Consequently I have several functions all doing similar
jobs all with multiple if() statements where I could have a single function
that extracts all the information I need which is streamlined and easy to
read.

--

Yours faithfully, Graham Reeds.
grahamr@omnieng.co.uk | http://omnieng.co.uk/