Using memory table or else

[limelect 48]

I have a Database grid that is using information from FDTable.

I would like to.

 

1. Load information into a memory table. (loading Records)

2. use it to show information onto the DBGrid. (disconnect FDTABLE and connect memory table)

3. Then return to the original fdtable >>DBGrid.

4 next time again make sure the memory table is empty.

 Which best component to use and how?

Just give me a general guide line

thanks

[Anders Melander 1815]

TFDTable should be able to do all this on its own. You don't need a separate memory dataset. See TFDDataSet.Offline

[limelect 48]

@Anders Melander  Thanks I will try your way although this means to clear

the table load with new information and after using that reload with the old one.

Let me explain further.

I have 2 databases of which one holds pointers (index) to the main one.

I SQL on this one to get new information on the main one.

The main one is used to show info on the database grid.

That it.

will it not take too much time to clear the table loading and showing it on the grid?

I thought of doing it on the memory table and then assigning the memory table to the grid to want it to be faster

and without the annoying!!!  of scrolling the grid.

 

[Guest]

Wouldn't this be a classic case of "Master->Details" usage?
FDQuery1 -> master table with a field to find the details
FDQuery2 -> detail table with master record information

 

FDQuery1 = select ID, ...* from TableMaster

 

FDQuery2 = select * from TableDetails where ID_FieldFromMaster = :ID

 

now, just fill the properties "MasterSource" and "MasterFields" on FDQuery2

[limelect 48]

@joaodanet2018

You might be right. I will check it thanks.

[limelect 48]

Ok it works like that

 

  FDQuery1.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,Category,FilesIndex');<< all fields from both tables
  FDQuery1.SQL.add('from Files,Projects');<< 2 tables
  FDQuery1.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%''' <<< the text to analize and get all index
    + 'and FilesIndex=SectionsId'); 

 

Thanks
 

[Guest]

FDQuery.SQL.Text := ' your statement';

FDQMain.SQL.Text := 'select ID,.... from tbMain';

FDQDetail.SQL.Text := 'Select fields... from tbDetail where idMainOnDetail =  :ID';

FDQDetail.SQL.Params.ParamByName('ID').asInteger or any other type = value;

[limelect 48]

My last problem

I do not seem to get rid of  WIDEMEMO

 

  FDQuery4.FieldByName('ProjectrealName').SetFieldType(ftWideMemo);<<< I changed to many to no avail
  FDQuery4.FieldByName('ProjectrealName').FieldKind:=fkData;
  FDQuery4.FieldByName('ProjectrealName').DisplayWidth:=10;
 
  DataSource1.DataSet := FDQuery4; << connect to DBGrid

 

P.S using a table on the same grid I get "REAL TEXT"
Any help?

Edited February 22, 2022 by limelect

[Guest]

32000 = it's so much... use another value!

SELECT CAST(SUBSTRING(BlobColumn FROM 1 FOR 32000) AS VARCHAR(32000)) AS NewBlobColumn

 

FDConnection can "map" fields source to another type compatible!

2x click on FDConn and look at "Format Options"

 

Edited February 22, 2022 by Guest

[limelect 48]

@joaodanet2018 Could you plz point to the knowledge behind your suggestion

since I cannot follow your answer to my line of code.

 

 FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,Category,Description,FilesIndex');
  FDQuery4.SQL.add('from Files,Projects');
   FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''
    + 'and FilesIndex=SectionsId');
 

Thanks , any link to the knowledge?

[Guest]

I dont understood because you need define your field ProjectRealName?

why this?

[limelect 48]

I show 2 fields only  ProjectrealName  and Category (MyDateTime, too but no problems with it)

as my grid is programed to show those 2 only +(date not important)

Those 2 are my problem

 

Edited February 22, 2022 by limelect

[Guest]

well, if any field it's a Blob on table  then, by default the DbGrid cannot show it, you need use a DbMemo to show/edit it.

to learn like the fields is created:

 

rightclick on fdtable and Fields Editor

now just ADD ALL FIELDS

now close editor fields

now press Ctrl+F12 to see Form as Text

to go back, press Ctrl+F12 again

 

you dont need create each field manually on code.

Edited February 22, 2022 by Guest

[limelect 48]

well these fields are normal text fields

 

 FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Projects (MyDateTime DateTime,ProjectName TEXT,ProjectrealName TEXT,Category TEXT NULL,FileMask TEXT NULL,Description BLOB,FilesIndex INTEGER)');
  FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Files (SectionsId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,MainDirectory TEXT NULL, Files TEXT NULL)');
 

[Guest]

see my sample for you... study the components on DataModule, mainly;

-- FDQuerys and FDUpdateObjects

-- almost none code it's necessary for use Master/Detail technic on FireDAC or any other similar.

read the units text and the TXT file on Database folder

-- include a FB_Test.FDB created on Firebird v4.0... if your is before than this, then you need re-create it using the script on same folder.

file 7zip v21.07 compressed no password!

VCL_Firebird_FireDAC_on_Delphi.zip

Edited February 22, 2022 by Guest

[limelect 48]

Thanks OK

[Guest]

I am sure i saw the parameter in FireDAC also. If a varchar field exceeds a certain (changeable) char count it will be instantiated as a BLOB.

So to cast a BLOB to a varchar using a substring inside the cast can speed up and simplify client things a lot if all pitfalls are covered.

[limelect 48]

@dany thanks i will return to this problem in a wile

(some thing urgent came up)

[limelect 48]

This is OK

 FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,Category   ,Description,FilesIndex');

But then

 FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,CAST(Category AS CHAR)  ,Description,FilesIndex');
CAST(Category AS CHAR) does not find category

Any idea?

[Guest]

CAST(Category as varchar(128))

CAST(substring(Category from 1 for 128) as varchar(128))

 

Not checked.

Edited February 23, 2022 by Guest

[limelect 48]

1. category not found

2. Error near from

 

I know this is why I need help

[Guest]

@limelect, perhaps if you log the complete SQLproperty before execution? Or use some SQL monitoring.

And paste exactly what is sent to the server, we can perhaps help.

 

Edited February 23, 2022 by Guest

[limelect 48]

Thanks dany

[limelect 48]

Ok guys I fixed my problem like that

I added all my needed fields to my  FDQuery4

then I changed for each field display value to dvfull

P.s I could not reach display value programmatically !!!

 

Thank you all

Fixed

[Serge_G 87]

20 hours ago, limelect said:

1. category not found

2. Error near from

 

I know this is why I need help

you need an alias column 

 CAST(substring(Category from 1 for 128) as varchar(128))  Category 

else, column should be known as CASTn (where n is a number)