Jump to content

Serge_G

Members
 View Profile  See their activity

  • Content Count

    311

  • Joined

  • Last visited

  • Days Won

    1

Posts posted by Serge_G

  2. Posted · Edited by Serge_G

    Hi,

    First, know that :

    1. there are many internal functions in Firebird SUBSTR should be replaced advantageously by SUBSTRING (even if a little more "verbose" syntax)
    2. Udf are deprecated in Firebird 4.0 (P.S. don't ask me about UDR, I am still with 2.5 in production, 3 running only for test, 4 only installed)
    3. read 5.10 chapter of Firebird 4.0 Language reference

     

    In my mind, Firebird 3 is  closer Interbase

    For the declaration of the UDF, I think you have to first check firebird .conf file (default parameter “UdfAccess” set to “None” ) , 

     

    UDFs directories and bitness  of the library also involved

    • Like 1

  3. Posted

    Use SET TERM

     

      

    SET TERM $ ;
CREATE PROCEDURE CUSTITEMMOVEMENT 
(
  CUSTNO INTEGER
)
RETURNS
(
  RNO INTEGER,
  TNAME VARCHAR(5),
  TDATE DATE,
  DOCNO VARCHAR(12),
  QTY NUMERIC(18, 2),
  NETPRICE NUMERIC(18, 4),
  ITEMNO VARCHAR(20),
  ITEMNAME VARCHAR(40)
)
AS
 BEGIN
    FOR SELECT  IM.RNO,IM.TNAME,IM.TDATE,IM.DOCNO,IM.QTY,
    IM.NETPRICE,IM.ITEMNO, IT.ITEMNAME
    FROM ITEMMOVEMENTS IM
    JOIN ITEMS IT ON IT.ITEMNO=IM.ITEMNO
    WHERE (IM.CUSTNO= :CUSTNO)
    ORDER BY IM.TDATE, IM.DOCNO
    INTO :RNO,:TNAME,:TDATE,:DOCNO,:QTY,:NETPRICE,:ITEMNO,:ITEMNAME
    DO SUSPEND;  
END $
SET TERM ; $

    By the way, don't use  quotation marks except if you want columns names to be case-sensitive

    and, for purpose of maintenance I suggest you not to name your columns xxxNO if type is not a number

    • Like 1

  5. Posted · Edited by Serge_G

    8 hours ago, Tang said:

    But I guess Serge's delete button seems not a native FMX ListView delete button. Did you added by your own? That may cause the ‘access error’.

    Hi,

    No, the image I post was a "classic" ImageListItemRightButton Item appearance (with TextButton visible) , and ImageListItemRightButtonDelete for the ItemEdit appearance.

     

    The "access error", I got it when I try to use a ListView1.DeleteItem(AItem.Index); beforehand declared as an helper (only way I found to raise OnDelete/Ondeleting event with touch input unavailable. 

      TListViewHelper  = class helper for TListView
    public function DeleteItem(const ItemIndex: Integer): Boolean; end;
    
{ TListViewHelper }

function TListViewHelper.DeleteItem(const ItemIndex: Integer): Boolean;
begin
inherited;
end;

    And using the OnButtonClick event of TListView (guilt, the  TListItem.MouseUp event)

     

    Using the OnItemClick to use DeleteItem 

    procedure TForm1.ListView1ItemClick(const Sender: TObject;
  const AItem: TListViewItem);
begin
ListView1.DeleteItem(AItem.Index);
end;

    I have no "access error"

     

    8 hours ago, Tang said:

    I did not have a 'access violation' ! error when delete the last record. But I did see some posts mentioned similar issue.

    Sorry, it was an "argument out of range" not an "access violation"  (guilty TListItemView.GetObject).

    My test (on Android), shows me that if there is only one item (the one to delete) this exception does not raise.

    8 hours ago, Tang said:

    Maybe need BeginUpdate/EndUpdate?

    Yes, perhaps a bypass but in wich event ?

     

     

  6. Posted · Edited by Serge_G
    not an 'access violation' error but an 'Out of range' one

    So, I test this on Android. @Tang  I can submit you this solution

    Add a private variable (in my code   key : integer;) 

    procedure TForm1.ListView1DeleteItem(Sender: TObject; AIndex: Integer);
begin
if FDMemtable1.Locate('id',key) then fdmemtable1.Delete; // delete the record
end;

procedure TForm1.ListView1DeletingItem(Sender: TObject; AIndex: Integer;
  var ACanDelete: Boolean);
begin
key:=listView1.Items[aIndex].Tag; // get the key to delete
ACandelete:=true;
end;

    But, yes, there is one, I think there is a bug I wrote in the prior post :

     

    23 hours ago, Serge_G said:

     Deleting last item of the list raise an  'Out of range' error !

     

  7. Posted

    51 minutes ago, Uwe Raabe said:

    Wipe Left to Delete

    Ok, but I try this on a windows pc how can I "simulate" this wipeleft ?

     

    On another hand, with a "classic" windows app (D11 Alexandria). I found that Deleting last item of the list raise an  'access violation' ! 

    procedure TForm1.LinkListControlToField1FilledListItem(Sender: TObject;
  const AEditor: IBindListEditorItem);
begin
(AEditor.CurrentObject as TListItem).Tag :=
     FDTable1.FieldByName('ID').AsInteger;
end;

procedure TForm1.ListView1ButtonClick(const Sender: TObject;
  const AItem: TListItem; const AObject: TListItemSimpleControl);
begin
if FDtable1.Locate('Id',AItem.Tag) then FDtable1.Delete;
end;

     

    Capture.PNG

  8. Posted

    Hi,

    Well, I don't understand the goal, I never use this DeletingItem method, but I will investigate.

    So to be clear : You have  a TListView  filled with livebindings, how do you :

    14 hours ago, Tang said:

    I left wipe an item to delete.

    A gesture, I think, but the code? (I am not a gesture expert) 

    Sorry to disappoint, but I'm not that far (I have not encountered this need in my professional life)

     

  9. Posted · Edited by Serge_G

     

    Please read all my post before posting this! I forgot nothing, you don't follow my steps.

     

    You miss the :
     

    Quote

     

    Another way if you want to use FDtable and Filter is to add to your  DBtable (MySQL I assume) a generated column for example 

    
ALTER TABLE mytable ADD c_date integer AS (YEAR(mydate)*10000+MONTH(mydate)*100+DAY(mydate))

    now you can use this computed column for your filter  

     

    It was an example, not involving date format.  And this is SGBD dependent.

     

    So, it's not your field MyDateTime which is involved in the filter I wrote, but a computed column  I named c_date, computed column you have to add to your table. You jump to a bad conclusion.

     

    Filter expression is not preprocessed I think,  so you can't use {fn CONVERT(mydate,DATE)}

     

     

  10. Posted

    I think the problem is deeper. 

    Using a FDquery (with parameter) instead of FDTable is for me the best solution, assuming your SGBD is MySQL 

    SELECT * FROM mytable where DATE(MyDateTime)=:paramdate

    by then instead of a filter building and activating you only have to write something like 
      

    FdQuery.Open('',[AdvDateTimePicker2.Date]);

    Contraction of
      

    fdquery.Close;

fdquery.prambyname('paramdate').asDateTime:= AdvDateTimePicker2.Date ;

fdquery.Open;

    Note to avoid flickering, surround the code with a fdQuery.DisableControls / Fdquery.EnableControls

     

    Another way if you want to use FDtable and Filter is to add to your  DBtable (MySQL I assume) a generated column for example 

    ALTER TABLE mytable ADD c_date integer AS (YEAR(mydate)*10000+MONTH(mydate)*100+DAY(mydate))

    now you can use this computed column for your filter  

      

    var dd,mm,yy : word;
begin
  decodedate(advdatetimePicker2.date,yy,mm,dd);
  fdtable.filtered:=false; 
  fdtable.filter:=Format('c_date=%d',[yy*10000+mm*100+dd]);
  fdtable.filtered:=true;

     

  11. Posted · Edited by Serge_G

    If it's a vote

    I use Flamerobin (light and open source) for all quick jobs including backup/restore, extracting data and more

    For design purpose (very rare) I used others 

    *DBeaver (not so fine for firebird) but I appreciate ER Diagrams

     

    You can have a list of third party firebird tools here

     

    23 hours ago, Henry Olive said:

    I'm planning to move from Interbase to Firebird

    Do my Interbase reviews have anything to do with your choice? 

    Keep in mind that some functionality of Interbase don't exist in Firebird (thinking about column crypto for example)  

    • Like 1

  12. Posted

    On 10/31/2021 at 1:07 PM, Gary said:

     

    That was Interbase last chance. Switched to Firebird 4.0, all is well.

    Yes, this is fine, I am a Firebird addict, but what if you want to migrate you Firebird App to mobiles (Androïd or IOS ?) 

    Firedac can't do this ! Ok, there are others third-party components (ANyDac, ZEOSDBO)  but keep this fact in mind.

    By the way, deploying Firebird on Androïd is a little messy 

  13. Posted

    OK, found it! I need mappings and then my "filter" works 

    procedure TDataModule2.FDBatchMove1WriteRecord(ASender: TObject;
  var AAction: TFDBatchMoveAction);
var m : String;
begin
AAction:=TFDBatchMoveAction.paInsert;
m:=FDBatchmove1.Mappings[0].ItemValue;
accept:=(Length(m)>=4) AND (Length(m)<=12);
if not Accept then AAction:=TFDBatchMoveAction.paSkip;
end;

    My file text need a first line =column definition. I just have an encoding problem to solve (attached my file) 

    and as you can see on image I have some problems  like these zygopétale, zygopétales

     

     

    liste_de_mots.francais.frgut.txt

    Capture.PNG

  14. Posted

    Hi,

     

    The goal, I have a text file with words and I want to export in a FDMemTable words with length between 4 and 12 ?

    I don't know how to get the value in onWriteRecord event, is there a way to ?

    I can get the value in the OnWriteValue one but fired after onWriteRecord it's unusefull.

     

    Any hints (not involving a localsql DELETE)

  15. Posted · Edited by Serge_G

    On 10/17/2021 at 12:33 PM, limelect said:

     No, it did not work D10.2.3 

    I disagree, it's entirely dependent on SGBD used

    20 hours ago, mvanrijnen said:

    the StrToDate shit,

    Ok but you can use formatsettings 

    function StrToDate(const S: string; const AFormatSettings: TFormatSettings): TDateTime;

     

  16. Posted

    Ah, I knew I faced this GetItemRect somewhere ! It was when I compare the new VCL TControlList to FMX.TListView.

    In fact, I was deploring the lack of the method (not lack but private one) GetItemRect (I found in FMX.TListview) in the VCL.TControlList 

    On 10/13/2021 at 7:38 AM, Serge_G said:

    I will look for in my ('old') sources code tests. 

    Was not so old finally, but other context . (webinaire sur TControlList partie sur le dragdrop)

     

    I quickly check and "joy" got the same results

    Quote

    ItemObj
       left 46
       top 260
       right 246
       bottom 294
    GetItemRect
       left 46
       top 260
       right 246
       bottom 294

     

    16 hours ago, sjordi said:

    I didn't try it yet on mobile devices though.

    No mousemove event will fire, I think. And tracking the movement of the finger  should be a real challenge

  17. Posted

    Hi,

    I have to put water in my wine even though it's a wine heresy

    I just read that the interbase 2020 update 2 allows recursive CTEs  https://docwiki.embarcadero.com/InterBase/2020/en/What's_New_in_InterBase_2020_Update_2

    so I do a first try (with firebird)  not a good one SQL but as first attempt not so bad 

    WITH RECURSIVE
     R AS (SELECT CUSTNO,1 AS LNo,Item,Price FROM TABLE_1 
           UNION all
           SELECT r.CUSTNO,r.LNo+1,Item,Price FROM R WHERE r.LNo< (SELECT COUNT(*) LINES FROM TABLE_1 WHERE CUSTNO=r.CUSTNO) 
          )             
SELECT CUSTNO,LNO,item,price FROM r

    Give me a first look, not a good one but an approximation

  19. Posted

    I don't found the source I had in mind, but ...

     

    If there are no headers or footers 

    procedure TForm1.ListView1ItemClick(const Sender: TObject;
  const AItem: TListViewItem);
begin
if AItem.Purpose=TListItemPurpose.None then
  begin
   memo1.Lines.Clear;
   memo1.Lines.Add('Item width '+(Listview1.width).ToString);
   memo1.Lines.Add('real Item width '+(Listview1.width-Listview1.ItemSpaces.Left-ListView1.ItemSpaces.Right).ToString);
   memo1.Lines.Add('Item Height '+Listview1.ItemAppearance.ItemHeight.toString);
   memo1.Lines.Add('real Item height '+(Listview1.ItemAppearance.Itemheight-Listview1.ItemSpaces.top-ListView1.ItemSpaces.bottom).ToString);
   memo1.Lines.Add('cursor '+Listview1.ScrollViewPos.ToString);
   memo1.Lines.Add(' Item toppos in list '+(AItem.index*Listview1.ItemAppearance.ItemHeight-Listview1.ScrollViewPos).ToString);
   memo1.Lines.Add(' real Item toppos in list '+(AItem.index*Listview1.ItemAppearance.ItemHeight-       Listview1.ScrollViewPos+Listview1.ItemSpaces.top).ToString);
  end;
end;

    With this, it's easy to make a bitmap extracted from ListView1.MakeScreenShot like this 

    procedure TForm1.ListView1ItemClick(const Sender: TObject;
  const AItem: TListViewItem);
var aBitmap : TBitmap;
    rect : Trect;
begin
if AItem.Purpose=TListItemPurpose.None then
  begin
    Rect:=TRect.Create(TPoint.Zero);
    Rect.Left:=Trunc(ListView1.ItemSpaces.Left);
    Rect.Top:=trunc(AItem.index*Listview1.ItemAppearance.ItemHeight-Listview1.ScrollViewPos+Listview1.ItemSpaces.top);
    Rect.Right:=Rect.Left+Trunc(Listview1.width-ListView1.ItemSpaces.Right);
    Rect.Bottom:=Rect.Top+Trunc(Listview1.ItemAppearance.Itemheight-ListView1.ItemSpaces.bottom);
    aBitmap:=TBitmap.Create;
    try
    aBitmap.Width:=Rect.Width;
    abitmap.Height:=Rect.Height;
    aBitmap.CopyFromBitmap(Listview1.MakeScreenshot,Rect,0,0);
    aBitmap.SaveToFile('itemtest.bmp');
    finally
      abitmap.Free;
    end;
  end;
end;

    result : Ok, I just forget to discount Scrollbar width, but that's the idea

     

    Capture.PNG

    • Thanks 1

  20. Posted · Edited by Serge_G

    8 hours ago, sjordi said:

    Is there any way to actually determine the coordinates, width, height of the Item, and make a bitmap of it on the fly?

    Yes, even if I don't remember how I proceed. I will look for in my ('old') sources code tests. 

    I remember there are some private properties but, but these memories are polluted by more recent tests on VCL.TControlList (sorry)

    As soon as I find these sources, I come back.

  21. Posted

    Well, I install Interbase and was rather disappointed! No context variables (a bypass :  using a temporary table) nor windows functions :classic_wacko:. I don't investigate for Recursive CTE, but I am afraid there is not.

    How can that be possible as of today? I understand more Ann Harrison mother of Interbase/Firebird point of view.

    As quick as I install Interbase, I desinstall it 😖 really disappointed. I have no doubt there are good things in Interbase but my way is now Firebird (event if missing some crypto columns)

  22. Posted · Edited by Serge_G

    CTE, ok, but windows functions ? I am really disappointed if Interbase  don't applies new SQL standard.

     

    22 hours ago, Dany Marmur said:

    AFAIR rbd$get_context and rdb$set_context was introduced not too long ago (2.0, 2.5?) in FB. Perhaps IB got something similar, but i would not know.

    IB and FB was compatible for a long time after the first fork (1.0 for FB). Would be interesting with a comprehensive "split" chart. But who would have the time?

    Time is the key word, if I have some in future week  I will install manually (because of 3050 port) Interbase 2020 Delphi version just to check what's new

     

    But there are some comparisons available (not commercial Embarcadero ones)

    https://db-engines.com/en/system/Firebird%3BInterbase

     

    or you can read

    https://ib-aid.com/en/articles/differences-between-firebird-and-interbase/

    vs

    https://www.embarcadero.com/fr/products/interbase/compare/interbase_firebird

     

    And make a choice

     

    On 10/4/2021 at 11:46 AM, Henry Olive said:

    ( I'm planning to move Firebird )

    Well I do that years ago and had no problem with :classic_biggrin:

  23. Posted · Edited by Serge_G
    Post Scriptum : Interbase version ?

    Well, with Firebird it is, using variable context (old way). I do not remember if Interbase had this capacity (guess yes)

    P.S. By the way, what is your Interbase version ?
      

    SELECT CUSTNO,      

           rdb$get_context('USER_TRANSACTION', 'row#') as  LINENO,

           rdb$set_context('USER_TRANSACTION','row#',
                  coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),

          ITEMNO,

          PRICE

FROM table-1 ORDER BY CUSTNO

    But you need to add some context variable to check and reset to 1 when changing of CUSTNO

     

    New ways  

       - Recursive Common Table Expression (I will not expand because best way is below)

     

       - Windows function 

    SELECT  
 CUSTNO,
 ROW_NUMBER() OVER (partition by CUSTNO) LINENO,
 ITEM,
 PRICE 
FROM TABLE_1
ORDER BY CUSTNO

     

    I suggest you to search in Interbase (version ?) documentation with these two terms      

     

     

    By the way, I suggest you a best way to ask a SQL question : add a script 

    CREATE TABLE table_1
(
   CUSTNO INTEGER NOT NULL,
   ITEM CHAR(2),
   PRICE NUMERIC(10,2)
);
INSERT INTO TABLE_1 VALUES (1,'AA',100);
INSERT INTO TABLE_1 VALUES (1,'BB',150);
INSERT INTO TABLE_1 VALUES (2,'AA',100);
INSERT INTO TABLE_1 VALUES (2,'CC',200);
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'BB', '150.00');
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'AA', '100.00');
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'CC', '200.00');

    and then the expected result (ok, here I cheat it's the result of the windows function SQL using the data I used
     

    Quote

     

    1    1    AA    100.00
    1    2    BB    150.00
    2    1    AA    100.00
    2    2    CC    200.00
    3    1    AA    100.00
    3    2    BB    150.00
    3    3    CC    200.00

     

     

     

     

  25. Posted

    On 9/10/2021 at 11:22 PM, corneliusdavid said:

     

    For the Windows platforms, this works perfectly as expected. On iOS, there is no OnItemClick event fired. On Mac, I can use the arrow key, then the space bar to activate it but not the mouse. 

    I don't have any Apple devices but on Androïd OnItemClick works fine.

     

    for IOS : what about onTap or Gesture management

    • Like 1
×