FMTBCDField on a DBGrid displays huge numbers lazarus / 16-Databases ---------------------------------------------------------------------- benade · 2014-04-07 18:25 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 · 2014-04-07 19:24 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 · 2014-04-07 19:30 Quote from: karaba on April 07, 2014, 09:24:05 pm3) 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. Quote4) 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 · 2014-04-07 19:34 Quote from: karaba on April 07, 2014, 09:24:05 pm1) 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 · 2014-04-07 20:21 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 · 2014-04-07 21:01 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 · 2014-04-08 06:13 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 · 2014-04-08 08:13 I can't reproduce your problem. I used  Embedded Firebrid 2.5 with SQLdb. Even using persistent fields give me the right numbers. ---------------------------------------------------------------------- benade · 2014-04-08 14:11 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 · 2014-04-08 14:14 Quote from: mangakissa on April 08, 2014, 10:13:11 amI can't reproduce your problem. Could you upload the project+database file or DDL? Thanks. Quote from: benade on April 08, 2014, 04:11:03 pmAre 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 · 2014-04-08 14:24 Quote from: benadeAre you running 64 or 32 bit embedded? I will try the embedded and see what happens.32bit embedded ---------------------------------------------------------------------- benade · 2014-04-08 15:32 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 · 2014-04-08 16:00 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 · 2014-04-08 18:07 Quote from: BigChimp on April 08, 2014, 06:00:26 pmNo.... 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 · 2014-04-09 07:45 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/8691291e911d4746b821bb12 d1084b9e66a42931/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 ---------------------------------------------------------------------- benade · 2014-04-10 06:02 Quote from: BigChimp on April 09, 2014, 09:45:57 am1. 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/8691291e911d4746b821bb12 d1084b9e66a42931/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 Thanks, both give the same incorrect big numbers.... what is interesting though is when I create an identical field in the same table the original will return the incorrect values and the new field will return the correct values. Dankie vir al jou moeite. Benade ---------------------------------------------------------------------- BigChimp · 2014-04-10 08:35 Ok - would still be interesting to know whether there's indeed something fishy going on in that database... but that's more a Firebird than a FreePascal/Lazarus issue.... ---------------------------------------------------------------------- benade · 2014-04-10 10:17 Quote from: BigChimp on April 10, 2014, 10:35:04 amOk - would still be interesting to know whether there's indeed something fishy going on in that database... but that's more a Firebird than a FreePascal/Lazarus issue.... I have recreated and populated the specific field in the same table and it is working fine. I agree with you that the problem might be in Firebird.  Thanks for all your input. ---------------------------------------------------------------------- benade · 2014-04-11 06:40 Although the numbers are now displaying correctly, the DisplayFormat property does not work at all setting it to '0.00000'.  It is a bit frustrating because the IBX works fine. Maybe I should switch to using IBX. It would be nice to get some feedback on what other programmers are using and their experiences. From my experience I can say the following: 1. I have used IBX successfully with Delphi for years and dropped it when Interbase was open sourced with no IBX support for Firebird . It looks like IBX has now made a come back with Lazarus? 2. From IBX I moved on to FIBPlus components from Devrace which works 100% but they unfortunately don't support Lazarus and I had some bad experiences with slow upgrades whenever Delphi releases a new version. 3. To have a backup plan, I got the IBDAC components for Delphi from Devart which is excellent and I can recommend them over that of FIBPlus.  I have tried to install their latest version in Lazarus without any success. 4. I have spent a lot of time on the built in sqldb components in Lazarus and I really like them but I have encountered two problems so far which doesn't give me enough confidence to use them to port my projects to Lazarus. I would appreciate feedback from anybody that is currently using Lazarus with Firebird extensively. I am pretty sure that I am not the only one that is looking for the best components to use with Firebird. ---------------------------------------------------------------------- BrunoK · 2014-04-11 07:41 There has been troubles with Delphi TBCD type in the past, I don't think it is either a database problem, neither, per see, a FPC problem. If you have source (preferably pascal) code what displays correctly, I would suggest you extract it. 1 - In a loop thru the records    2 - Loop thru the fields for TBCD fields    a - use the code that DISPLAYS CORRECTLY to get a string representation lStr {:FmtBCDStringtype }    b - lBcd{:TBcd}:={fmtbcd.pp.} StrToBCD(lStr);  // Renormalise it using FPC    c - TFMTBCDField(TheField).Value:=lBcd; // Replace the field from lBcd after normalization in the record        end;    rewrite the record {end loop} Have a look if it makes things better. ---------------------------------------------------------------------- LacaK · 2014-04-11 09:44 Quote from: benade on April 11, 2014, 08:40:10 amAlthough the numbers are now displaying correctly, the DisplayFormat property does not work at all setting it to '0.00000'. What number is in TFmtBCDField and what is displayed in grid ? What output you get if you use f.e.:   var bcd: TBCD;   bcd:=strtobcd('type your number here');   writeln(FormatBCD('0.00000', bcd)); ---------------------------------------------------------------------- benade · 2014-04-11 12:09 Quote from: LacaK on April 11, 2014, 11:44:19 amQuote from: benade on April 11, 2014, 08:40:10 amAlthough the numbers are now displaying correctly, the DisplayFormat property does not work at all setting it to '0.00000'. What number is in TFmtBCDField and what is displayed in grid ? What output you get if you use f.e.:   var bcd: TBCD;   bcd:=strtobcd('type your number here');   writeln(FormatBCD('0.00000', bcd)); 1. The number in the grid is 27.7 and it should be 27.70000. There is no way that I could get it to add 5 decimal places. 2. The FormatFloat('0.00000', fvar) function does work outside the grid. ---------------------------------------------------------------------- ttomas · 2014-04-11 12:27 Do you try to set DisplayFormat := '0.00000' to your TFmtBCDField ---------------------------------------------------------------------- LacaK · 2014-04-12 20:04 I have checked it again and correct formating of TFmtBCDField was introduced only in FPC 2.6.4 or 2.7.1 (because of missing FormatBCD function in fmtbcd unit.) ---------------------------------------------------------------------- benade · 2014-04-13 13:10 Quote from: ttomas on April 11, 2014, 02:27:47 pmDo you try to set DisplayFormat := '0.00000' to your TFmtBCDField Yes, I did with no effect. Everything is working fine except for the BCD fields. ---------------------------------------------------------------------- benade · 2014-04-13 13:15 Quote from: LacaK on April 12, 2014, 10:04:17 pmI have checked it again and correct formating of TFmtBCDField was introduced only in FPC 2.6.4 or 2.7.1 (because of missing FormatBCD function in fmtbcd unit.) Do I understand you correctly.... The current version of FPC bundled with Lazarus is 2.6.2 (which is the one that I am using) and that FPC 2.6.4 will maybe solve my problem?  If this is the case, how do I link the newer compiler in my IDE? ---------------------------------------------------------------------- taazz · 2014-04-13 15:55 Quote from: benade on April 13, 2014, 03:15:50 pmDo I understand you correctly.... The current version of FPC bundled with Lazarus is 2.6.2 (which is the one that I am using) and that FPC 2.6.4 will maybe solve my problem?  If this is the case, how do I link the newer compiler in my IDE? go to "tools\options" select the "environment\files" leaf and look at the attachment for the relevant parts. After setting those to the new location probably a tools\Rescan FPC Source Directory for good measure. ---------------------------------------------------------------------- benade · 2014-04-14 07:35 I have used FPC 2.6.4 with better results.... but not there yet 1. At design time the values are correct although the DisplayFormat property has no effect. 2. At run time the huge numbers are still showing with the DisplayFormat partially working. ---------------------------------------------------------------------- avra · 2014-04-14 08:12 Quote from: benade on April 10, 2014, 12:17:00 pmI have recreated and populated the specific field in the same table and it is working fine. I agree with you that the problem might be in Firebird.Have you tried to backup the whole database and then restore it from backup? ---------------------------------------------------------------------- benade · 2014-04-14 11:50 Quote from: avra on April 14, 2014, 10:12:58 amQuote from: benade on April 10, 2014, 12:17:00 pmI have recreated and populated the specific field in the same table and it is working fine. I agree with you that the problem might be in Firebird.Have you tried to backup the whole database and then restore it from backup? Yes, I did.  What was interesting is that when I dropped the specific field from the Firebird db and recreated it the correct numbers were displayed correctly, but the DisplayFormat property on the Grid did not work. This, however was only temporary because at some point the big numbers reappeared. ---------------------------------------------------------------------- LacaK · 2014-04-14 12:27 If you can prepare reproducible test unit , then we can test it on our versions ... ---------------------------------------------------------------------- benade · 2014-04-14 15:16 Quote from: LacaK on April 14, 2014, 02:27:22 pmIf you can prepare reproducible test unit , then we can test it on our versions ... I have included a test project.  Please note: 1. I have put everything in the testdb folder under c: 2. Again, this table will display the values correctly, because as I have mentioned is that this is the case when you recreate the table. 3. The DisplayFormat property does not work for the TFMTBCDFields. I can, if needed, send you a link to download my complete project so far.  This will be a couple of Mb's though. Thanks ---------------------------------------------------------------------- LacaK · 2014-04-16 05:16 I do not have Firebird 2.5 installed, so I did test with Firebird 2.1. I have create table and fill some data. Result is IMO correct, see attached screenshot. So I can not reproduce your problem. (but I use FPC 2.7.1) ---------------------------------------------------------------------- benade · 2014-04-16 07:47 Quote from: LacaK on April 16, 2014, 07:16:50 amI do not have Firebird 2.5 installed, so I did test with Firebird 2.1. I have create table and fill some data. Result is IMO correct, see attached screenshot. So I can not reproduce your problem. (but I use FPC 2.7.1) I was just thinking that I should go back to using IBX, but my feeling is that sqldb will be better in the long run and the coding is really straight forward.  I tried to get FPC 2.6.4 going but can't get it right yet and I am searching the internet on the "how to" at the moment. So I will now focus on getting FPC 2.7.1 up and running and see what happens. Thanks for your input. ---------------------------------------------------------------------- avra · 2014-04-16 09:11 Quote from: benade on April 16, 2014, 09:47:04 am[...I will now focus on getting FPC 2.7.1 up and running and see what happens.It's easy with FPCUP: https://bitbucket.org/reiniero/fpcup ---------------------------------------------------------------------- benade · 2014-04-17 07:46 Quote from: avra on April 16, 2014, 11:11:55 amQuote from: benade on April 16, 2014, 09:47:04 am[...I will now focus on getting FPC 2.7.1 up and running and see what happens.It's easy with FPCUP: https://bitbucket.org/reiniero/fpcup Thanks, FPCUP worked just fine and the FMTBCDField displayed correctly with the FPC 2.7.1 version. Unfortunately I ran into trouble with a number of other issues like an active transaction that I can't close and I had to stop the Firebird service to get out of it.  Using Ctrl- spacebar will jump to the unit instead of showing me the list of available properties....  I assume this happens because Laz IDE 1.3 & FPC 2.7.1 is still in beta? ---------------------------------------------------------------------- avra · 2014-04-18 22:19 Quote from: benade on April 17, 2014, 09:46:02 amFPCUP worked just fine and the FMTBCDField displayed correctly with the FPC 2.7.1 version. Unfortunately I ran into trouble with a number of other issues like an active transaction that I can't close and I had to stop the Firebird service to get out of it.  Using Ctrl-spacebar will jump to the unit instead of showing me the list of available properties....  I assume this happens because Laz IDE 1.3 & FPC 2.7.1 is still in beta?It seams you have tried both LAZ and FPC from SVN. There is a big difference between SVN and beta. SVN is less stable, and from time to time it might even not work at all. If you play a little with FPCUP, you will be able to try LAZ 1.2 with FPC SVN (because it looks like FPC SVN fixed some of your problems, but maybe LAZ 1.3 introduced new ones).