FMTBCDField on a DBGrid displays huge numbers

[benade]

I have finally decided to make the move from Delphi to Lazarus on Windows porting a project with 100 000+ lines of code. I am using Firebird 2.5 with the Lazarus SQLdb controls. Everything was plain sailing until I got stuck with a FMTBCDField on a DBGrid which displays huge numbers and I don't have a solution.  All my searches were fruitless...:-(

The alternative that I have in mind is the IBDAC component from Devart.... the other bridges that I need to cross are ftp, zip and e-mail before I can set myself free from Delphi.

Any help or suggestions will be appreciated. 

[karaba]

1) please provide some information for the problem you are seeing, "displays huge numbers" is not a problem more like a format miss, the standard answer is try the mask property forcing a specific display format.
2) As far as I know Devart component support lazarus for at least the last couple of years, I see no problem using them. On top of that there mercury data objects in sourceforge for firebird only support as well as zeos that support multiple batabases.
3) INDY 10 supports lazarus on windows and linux, I have no idea what happens on OS X or android etc, os consider your self covered on the ftp and email front, there are multiple tcp/network libraries that can be used synapse, lnet to name the most known.
4) there is abbrevia in sourceforge from turbopower that has support for multiple compression methods and formats, also there is build in support for zip and gz I think using streams.

[marcov]

3) INDY 10 supports lazarus on windows and linux, I have no idea what happens on OS X or android etc, os consider your self covered on the ftp and email front, there are multiple tcp/network libraries that can be used synapse, lnet to name the most known.

Quite a while ago, I tested Indy10 with FreeBSD and OSX. FreeBSD just worked, OS X (PPC at the time) I only tested the clients, but they worked too.

4) there is abbrevia in sourceforge from turbopower that has support for multiple compression methods and formats, also there is build in support for zip and gz I think using streams.

If only Decompression is needed Free Pascal comes with two unzip units.  paszlib does both compression and decompression

[marcov]

1) please provide some information for the problem you are seeing, "displays huge numbers" is not a problem more like a format miss, the standard answer is try the mask property forcing a specific display format.

FMTBCD support is fairly new, so there might be bugs. If Lazarus is FPC 2.6.2 based, first try 2.6.4
 

[benade]

I am using lazarus  1.2.0 with FPC 2.6.2 and porting from Delphi RAD XE3 Prof with FIBPlus controls for my db access.

The numbers displayed in the specific column are all the same size with 1 decimal eg. 46279271245835.9.  The DisplayFormat property has no effect and neither does changes to the Precision(18) nor the Size (5).  The Firebird field definition is "INGELYS    NUMERIC(15,5)".   I have 104 tables in my Firebird db of which only a few uses the Numeric(15,5) data type.

I am really impressed with Lazarus IDE and the total development platform including the SQLdb Firebird controls.

[karaba]

is the number shown correct? does it it have problem with the decimal placement? ee it should be four places to the left if I understand correctly? give us an example what it should show and what it shows make it real one so we can see the problem.

[benade]

I use one of my live db's to compare the results for every table that I port.

Correct values before :

17.20000
15.10000
15.20000
17.20000
17.20000
0.00000

After porting:

46255345872815.5
46246901625661.7
46247464572393.9
46255345874963
46255345874963
0

I need 5 decimals of accuracy for this field and that is why I use the Numeric(15,5) data type in Firebird.  The specific field saves scheduled areas for a water distribution management application and adding thousands of these records shouldn't lead to rounding off errors on the 5th decimal. 

[mangakissa]

I can't reproduce your problem. I used  Embedded Firebrid 2.5 with SQLdb. Even using persistent fields give me the right numbers.

[benade]

This is actually good news because it means that I might be able to solve my problem.  I have tried IBX from MWA software and it's working but I prefer the sqldb controls which have everything that I need and it is bundled with Lazarus.

Are you running 64 or 32 bit embedded?
I will try the embedded and see what happens.

Thanks for your input, I really hope to get this one running.

[BigChimp]

I can't reproduce your problem.

Could you upload the project+database file or DDL? Thanks.

Are you running 64 or 32 bit embedded?
I will try the embedded and see what happens.

Please don't run 64 bit FPC 2.6.x, especially with database libraries:
http://wiki.lazarus.freepascal.org/Windows_Programming_Tips#FPC_2.6.x.2FLazarus_warning_.28Missing_support_for_SEH.29

[mangakissa]

Are you running 64 or 32 bit embedded?
I will try the embedded and see what happens.

32bit embedded

[benade]

Now this is an interesting one....

1. From the replies I received I decided to create and identical table T_TEST as shown below and copy all the data into this table to have two identical tables in the same database.
2. I then tested both in Lazarus using the same connection swapping only the SQL statement as shown below.
3. The test table worked 100%
4. The original table displayed the huge numbers.

The only difference between the two tables are that the t_test table has no primary or foreign keys.

Any ideas?


rocedure TForm1.OpenInlysBtnClick(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add('SELECT * FROM INLYS');
  SQLQuery1.Open;
end;

procedure TForm1.OpenTestBtnClick(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add('SELECT * FROM T_TEST');
  SQLQuery1.Open;
end;

procedure TForm1.CloseBtnClick(Sender: TObject);
begin
  IBConnection1.Close;
end;       

CREATE TABLE T_TEST (
    SKEMA      SMALLINT NOT NULL,
    PERSEELID  VARCHAR(10) NOT NULL,
    TIPE       CHAR(1) NOT NULL,
    JAAR       SMALLINT NOT NULL,
    INGELYS    NUMERIC(15,5),
    WINTER     INTEGER,
    SOMER      INTEGER,
    OTHER      INTEGER,
    TOTAL      COMPUTED BY (WINTER + SOMER + OTHER),
    QUOTA      INTEGER,
    QUOTATOT   COMPUTED BY (INGELYS * QUOTA)
);

[BigChimp]

No.... except perhaps corruption in your Firebird DB... Edit: but I don't think so as you said other tools do work ok

Perhaps a good idea to show the INGELYS ddl.

[benade]

No.... except perhaps corruption in your Firebird DB... Edit: but I don't think so as you said other tools do work ok

Perhaps a good idea to show the INGELYS ddl.

1. I have made an exact copy of the inlys table shown below and duplicating all 3133 records also. The copy works but this one don't. The only difference now is the dependencies which includes a couple of stored procedures and views.  I will delete the original, rename the working table and put the dependencies back and see what happens.
2. I will make a snapshot and restore to get rid of the corruption possibility.

CREATE TABLE INLYS (
    SKEMA      SMALLINT NOT NULL,
    PERSEELID  VARCHAR(10) NOT NULL,
    TIPE       CHAR(1) NOT NULL,
    JAAR       SMALLINT NOT NULL,
    INGELYS    NUMERIC(15,5),
    WINTER     INTEGER,
    SOMER      INTEGER,
    OTHER      INTEGER,
    TOTAL      COMPUTED BY (WINTER + SOMER + OTHER),
    QUOTA      INTEGER,
    QUOTATOT   COMPUTED BY (INGELYS * QUOTA)
);
ALTER TABLE INLYS ADD PRIMARY KEY (SKEMA, PERSEELID, TIPE, JAAR);
ALTER TABLE INLYS ADD CONSTRAINT INLYS_PLOT FOREIGN KEY (SKEMA, PERSEELID) REFERENCES PERSEEL (SKEMA, PERSEELID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX INLYS_JAAR ON INLYS (JAAR);
CREATE INDEX INLYS_TIPE ON INLYS (TIPE);
GRANT ALL ON INLYS TO FINANCE;
GRANT ALL ON INLYS TO RDB$ADMIN;
GRANT ALL ON INLYS TO WATER;

[BigChimp]

1. Could you try to check whether the problem lies
- in or below the FPC sqldb components or
- between sqldb and the Lazarus GUI components
by compiling & running pasql
https://bitbucket.org/reiniero/smalltools/src/8691291e911d4746b821bb12d1084b9e66a42931/pasql/pasql.lpr?at=default
connect to the db (follow screen instructions) and run

Code: [Select]

select ingelys from inlys order by skema, ingelys;
go

2. Depending on results of 1 you could also check if the error has been fixed in FPC trunk by running pasql compiled under trunk from
https://bitbucket.org/reiniero/smalltools/downloads/pasql.exe

Dankie,
BigChimp