Help with Query at run time

[JonRobertson 72]

1 hour ago, Gary said:

it will throws an error when trying to open database

Same for me. I suspect your code is still hardcoding the path to the database.

[Columbo 1]

Hmmmm,  I checked the params in the TFDConnection component and it had the full path to the database so it is hard coded.  I tried putting just the database name (dino.db) thinking that it would look in the current directory for the file but doesn't work.  It then flags an error that my images cannot be found as well. The path for the images is

dino_images.Picture.LoadFromFile('dino_Images\ ' + sName + '.jpg');

dinoSize.Picture.LoadFromFile('dino_Images\ ' + sName  + '_size' + '.jpg')

so it should be able to find them from in the current directory.

 

I zipped the entire project but it is too big to attach here. 

 

Try this link.

Edited December 22, 2023 by Columbo

[JonRobertson 72]

The first thing to change is to set conPLife.Connected to False before building the project. When Dinobase launches and loads your main form, it is attempting to immediately open the database connection because Connected is set to True at design-time. Setting Connected to True is very useful while you are developing, but it needs to be set to False before building a project that will run anywhere except on your development machine. This is a pain and there are ways to automate it. But that is a different post. ;)

 

I would have expected removing the path in Params.Database to resolve the path issue. But I do not have experience with SQLite or FireDAC. If you are certain the database will always be in the same folder as the .EXE, the path can be adjusted before opening the database. Here is one approach, which I put at the top of FormCreate:

procedure TfrmDino.FormCreate(Sender: TObject);  //Form creation
begin
  conPLife.Params.Database := IncludeTrailingPathDelimiter(TDirectory.GetCurrentDirectory) + conPLife.Params.Database;
  conPLife.Connected := True;
  tblPLife.Active := True;

Note that System.IOUtils needs to be added to your uses clause to resolve the reference to TDirectory.

 

This is still not ideal, but it works for your first project. An ideal solution would be to store the database and images in a folder intended for app data, such as TPath.GetPublicPath (typically C:\ProgramData). There are other folders that you could use. Through the years, Microsoft has changed gears on app data folders a few times.

 

After that, I copied the dino_images and dino_size folders from your earlier zip to my project folder and was able to run your project, navigate the db, and see the images.

 

I have a couple of quick suggestions from a brief look at your form's code. Your Exit button calls "self.Close". "self." is not needed here. There are times that it is useful for clarity, but not necessary. The btnExitClick method is a member of TfrmDino, so calling Close calls the Close method of TfrmDino.

 

You declared sName, recno, and recnum as globals in the var section of the unit. You should get in the habit of declaring "form" related variables as members of the form, as private members when possible. (Although it seems recno and recnum are not currently needed.)

 

  private
    sName: String;

Lastly, pay attention to warnings from the compiler:

 

[dcc32 Warning] Dinobase_u.pas(126): W1036 Variable 'iSelect' might not have been initialized

How should lbxDataClick "behave" if lbxData.ItemIndex is not greater than -1?

 

Jon

Edited December 22, 2023 by JonRobertson

[Remy Lebeau 1392]

23 minutes ago, JonRobertson said:

If you are certain the database will always be in the same folder as the .EXE, the path can be adjusted before opening the database. Here is one approach, which I put at the top of FormCreate:

  conPLife.Params.Database := IncludeTrailingPathDelimiter(TDirectory.GetCurrentDirectory) + conPLife.Params.Database;

 

You should not use TDirectory.GetCurrentDirectory() to get the EXE path, as the CWD is dynamic. It is subject to whatever path the launcher of the EXE wants to use, and it can also change value during the EXE's runtime.  Instead, use TApplication.ExeName or ParamStr(0) to get the full path to the EXE file, and then strip off the filename with ExtractFilePath()/ExtractFileDir() or TPath.GetDirectoryName().  Though, you really should not be storing ANY mutable application data in the EXE's folder to begin with.

Edited December 22, 2023 by Remy Lebeau

[JonRobertson 72]

You have a misunderstanding of Delphi list controls. Nearly everything in Delphi is zero indexed. Strings (UnicodeString, AnsiString, etc) are a notable exception.

    iSelect := lbxData.ItemIndex +1;  //Get the item number of the slected item in the listbox.  Add 1 because item list starts at zero (0).

You use iSelect to retrieve the selected item from lbxData:

 

selectedItem := lbxData.Items[iSelect];

If the last item in the list is selected, an "Index out of bounds" error will occur. Since the "item list starts at zero", you need to use a zero-based index for lbxData.Items[].

 

I wanted to show how you can use the dataset's events to your advantage. I have attached a modified Dinobase_u.pas/.dfm that implements TFDTable.AfterScroll to eliminate duplicating code/work in other methods.

Dinobase_u.zip

[Columbo 1]

Jon,   In my code everything was working but as soon as I set connected to 'False' and do a build, the .exe can't find the images.  If I go back and change the 'Connected' to true and try to run it from within design mode it can no longer find the images in design mode either. Also, when using your code, when I select a animal from the Listbox it is not getting the correct record.  If I select record 9, I get record 8.

Quote

You have a misunderstanding of Delphi list controls. Nearly everything in Delphi is zero indexed.

I did understand that the index starts at 0, and that is why I used the

iSelect := lbxData.ItemIndex +1;
	

so iSelect would match the record number in the databse.   In the database, Cephalaspis is record number 6.  Using your code, if I select Cephalaspis in the Listbox I get record number 5, Megalograptus.

Edited December 22, 2023 by Columbo

[Stano 143]

My humble remarks
// I would kill for this. You don't know what I'm doing and what's on my screen.
  WindowState := wsMaximized;    //Maximize the window
Set CustomTitleBar.Enabled = True is not a good idea.  

procedure TfrmDino.FormCreate(Sender: TObject);  //Form creation
begin
  lbxData.Visible := False;  //  That was missing here

Rename the RECORD field to IDDINO or DINOID or PKDINO or DINOPK
I modified the record search using ComboBox.

Remark:
For my own use I changed the DB in conPLife. Because the suggested dynamic path setting gives me nonsense. I'm not that familiar with it.
D:\Delphi_11_Community\MyProjects\Dinobase\dino.db
My path d:\Dokumenty\Z internetu\PrehistoricLife\dino.db

Dinobase_u.zip

[JonRobertson 72]

1 minute ago, Stano said:

For my own use I changed the DB in conPLife. Because the suggested dynamic path setting gives me nonsense. I'm not that familiar with it.

That works if you always run it from the location where Delphi writes the .EXE. If you want it use it on another machine or give it to your friends, that doesn't work so well.

[JonRobertson 72]

52 minutes ago, Columbo said:

so iSelect would match the record number in the databse.   In the database, Cephalaspis is record number 6.  Using your code, if I select Cephalaspis in the Listbox I get record number 5, Megalograptus.

The code below assigned ItemIndex + 1 to iSelect, then assigned lbxData.Items[iSelect] to selectedItem. In the source you posted, selectedItem is not used for anything. However, that assignment does result in a "List index out of bounds" error if you click on the last item in the list box.

  iSelect := lbxData.ItemIndex +1;  //Get the item number of the slected item in the listbox.  Add 1 because item list starts at zero (0).
  lboxNum := iSelect -1;
  selectedItem := lbxData.Items[iSelect];

This attached version builds the paths for the DB and images using Application.ExeName, as Remy suggested. It shows an alternate way of building the full path for both images.

 

It still expects your database and image folders to be in the same folder as Dinobase_p.exe.

 

Jon

Dinobase_u.pas

Edited December 22, 2023 by JonRobertson

[Stano 143]

But in my PC there is not exists this path. That's why I don't understand it.

 

That would be because it's created using the CE version and I haven't changed anything. I would have to destroy the automatically created files. I don't know in which one the information in question is located.

Edited December 22, 2023 by Stano

[Stano 143]

Table names are always plural!

[Columbo 1]

Wow!   It's getting more and more confusing.  Anyway, I think that I have it working now however, there is still one problem that bothers me and that is the titlebar. I want my header image to be at the very top when the application runs.  In design mode it is at the very top and no Delphi titlebar.  When I compile it to an .exe the Delphi titlebar appears at the top with the Delphi logo and my header image is pushed down 30px.  Anyway to get rid of the Delphi titlebar? 

 

 

 

 

[JonRobertson 72]

The easy way is via frmDino.BorderStyle.

 

Forms.TFormBorderStyle

 

Value	Meaning
bsDialog	Not resizable; no minimize/maximize menu
bsSingle	Not resizable; minimize/maximize menu
bsNone	Not resizable; no visible border line
bsSizeable	Standard resizable border
bsToolWindow	like bsSingle but with a smaller caption
bsSizeToolWin	like bsSizeable with a smaller caption

[Gary 18]

@Columbo My 2 cents:

Beautiful UI BTW.

I don't like to do anything in a form's OnCreate event that I don't have to, it has caused me trouble in the past as things may not always be created before I use them. Nor do I ever open Databases there, use the OnShow if you must, even better have a dedicated button. Break things down into separate procedures and assign them to events. I added a combobox for the search and implemented the search with a simple RecNo assignment.

 

Things I changed it the Object inspector:

Form

  WindowState = wsNormal 

  Position = poScreenCenter

CustomTitleBar = False // You cannot move it around or Min Max with this set true

ComboBox

  DropDownCount = 15 //However many you want

 

unit Dinobase_u;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Imaging.jpeg, Vcl.ExtCtrls,
  Vcl.StdCtrls, Vcl.ComCtrls, System.ImageList, Vcl.ImgList, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
  FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs,
  FireDAC.Phys.SQLiteWrapper.Stat, FireDAC.VCLUI.Wait, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.Client, Data.DB,
  FireDAC.Comp.DataSet, Vcl.DBCtrls, Vcl.Mask, Vcl.Buttons;


type
  TfrmDino = class(TForm)
    pnlTitlebar: TPanel;
    Image1: TImage;
    pnlHeader: TPanel;
    Image2: TImage;
    lblName: TLabel;
    lblMeaning: TLabel;
    lblPronounce: TLabel;
    lblPeriod: TLabel;
    lblMainGroup: TLabel;
    pnlFooter: TPanel;
    Footer: TImage;
    lblSize: TLabel;
    lblLived: TLabel;
    lblDiet: TLabel;
    lblFossils: TLabel;
    PLTabSheet: TPageControl;
    TabSheet1: TTabSheet;
    SizeComp: TTabSheet;
    dinoImage: TImage;
    dinoSize: TImage;
    conPLife: TFDConnection;
    tblPLife: TFDTable;
    Datasource1: TDataSource;
    lblRecno: TLabel;
    btnExit: TBitBtn;
    dbeName: TDBEdit;
    dbeMeaning: TDBEdit;
    dbePronounciation: TDBEdit;
    dbePeriod: TDBEdit;
    dbeMainGroup: TDBEdit;
    dbeSize: TDBEdit;
    dbeLived: TDBEdit;
    dbeDiet: TDBEdit;
    dbeFossils: TDBEdit;
    dbmFactfile: TDBMemo;
    dbeRecno: TDBEdit;
    DBNavigator1: TDBNavigator;
    cbxSearch: TComboBox;
    procedure btnExitClick(Sender: TObject);
    procedure cbxSearchClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure tblPLifeAfterScroll(DataSet: TDataSet);
  private
    { Private declarations }
    procedure OpenConnection;
    procedure OpenTable;
    procedure LoadList;
    procedure LoadImages;
  public
    { Public declarations }
  end;

var
  frmDino: TfrmDino;

implementation

{$R *.dfm}

uses
  System.IOUtils,
  VCL.Dialogs;

const
  DINO_DB = 'dino.db';

procedure TfrmDino.btnExitClick(Sender: TObject);  //Exit Button
begin
  Close;
end;

procedure TfrmDino.cbxSearchClick(Sender: TObject);
begin
  //Search... is at ItemIndex[0] and we load the list from the table so
  //a 1 for 1 on the count so nop searching needs to be done just
  //set the Tables RecNo it is write as well as read property
  tblPLife.RecNo := cbxSearch.ItemIndex;
  //Set combobox back to Search...
  cbxSearch.ItemIndex := 0;
  //if you do not like the search box selected set focus to any control
  dbeName.SetFocus;
end;

procedure TfrmDino.FormShow(Sender: TObject);
begin
  OpenConnection;
  OpenTable;
  LoadList;
  //Images will be loaded when record changes after this
  LoadImages;
end;

procedure TfrmDino.LoadImages;
var
  lName : String;
begin
  lName := tblPLife.FieldByName('name').AsString;

  // Load the dino image and display it
  dinoImage.Picture.LoadFromFile('dino_images\' + lName + '.jpg');
  // Load the size image and display it
  dinoSize.Picture.LoadFromFile('dino_size\' + lName + '_size' + '.jpg');
end;

procedure TfrmDino.LoadList;
begin
  cbxSearch.Items.Clear;
  cbxSearch.Items.Add('Search...');
  //Disable all data aware controls so the UI does not repaint
  //as we load the list
  tblPLife.DisableControls;
  try
    tblPLife.First;
    while not tblPLife.Eof do
    begin
      cbxSearch.Items.Add(tblPLife.FieldByName('name').AsString);
      tblPLife.Next;
    end;
  finally
    tblPLife.First;
    tblPLife.EnableControls;
  end;
end;

procedure TfrmDino.OpenConnection;
begin
  conPLife.Params.Database := ExtractFilePath(Application.ExeName) + DINO_DB;
  try
    conPLife.Connected := True;
  Except
    on E: EDatabaseError do
    begin
      ShowMessage('Error connecting to: ' + conPLife.Params.Database + ': ' +
        E.Message);
    end;
  end;
end;

procedure TfrmDino.OpenTable;
begin
  try
    tblPLife.Open
  Except
    on E: EDatabaseError do
    begin
      ShowMessage('Error opening table with: ' + E.Message);
    end;
  end;
end;

procedure TfrmDino.tblPLifeAfterScroll(DataSet: TDataSet);
begin
  //if Controls are disabled we are loading the list and don't want
  //to load images
  if not tblPLife.ControlsDisabled then
    LoadImages;
end;

end.

 

[JonRobertson 72]

38 minutes ago, Gary said:

CustomTitleBar = False // You cannot move it around or Min Max with this set true

I have not explored the CustomTitleBar parameter or TTitleBarPanel component until today. Columbo's project gave me an opportunity to do so. When using an image that spans the width of the window, it covers TTitleBarPanel CustomButtons. I suspect buttons would need to be painted in OnPaint and "clicking" them handled in an OnClick handler. But I am still exploring those options...

 

However, moving the form using the mouse is possible by adding an OnMouseDown event handler to the Image1 component:

procedure TfrmDino.Image1MouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
const
  sc_DragMove = $F012;
begin
  ReleaseCapture;
  Perform(wm_SysCommand, sc_DragMove, 0);
end;

 

[Columbo 1]

I made a couple of changes to my application and I think it is ok now.  Not the best coding but it works and the improvements should come with further practice.  There are a few things in Delphi that bugs me, like making it such a task to get rid of the Titlebar and the Delphi icon.  I have given up on that for now but the app looks terrible with a brown and cream color scheme and a Blue titlebar.  If you could at least change the color from blue to brown to match the rest of the app it would be better but that seems to be impossible as well in Delphi.  But, I sincerely thank everyone for their help and patience.

 

You can get the finished version here.

Edited December 23, 2023 by Columbo

[JonRobertson 72]

31 minutes ago, Columbo said:

but that seems to be impossible as well in Delphi

Not impossible. If the "system buttons" to minimize, maximize, and close the application are not important, set the form's BorderStyle property to bsNone in the Object Inspector. Note that the window will not be movable or sizable without some additional code.

 

You could paint the caption bar yourself, although that is not trivial. See StackOverflow for one possible answer: Change the color of the applications title bar

 

I suggest experimenting more with CustomTitleBar and TTitleBarPanel. Use TTitleBarPanel instead of TPanel for pnlTitlebar, then set CustomTitleBar.Control to your TTitleBarPanel. Unfortunately I don't have specific advice on property values for CustomTitleBar. But experimenting is a great way to learn. You could start a new VCL Project and experiment with an empty form.

[Gary 18]

I added a panel to the top image set align to all right width to 50 Caption -- font color clCream Panel color to your label's font color $0000023F (keep trying for exact match to your brown) and got rid of all borders. Add code to Panel OnClick

Quote

procedure TfrmDino.pnlMinimizeClick(Sender: TObject);
begin
  Self.WindowState := wsMinimized;
end;

 

JonRobertson's suggestion may be better to minimize the form but I don't have experience either.

 

Add to top image OnMouseDown event for dragging

procedure TfrmDino.Image1MouseDown(Sender: TObject; Button: TMouseButton;
    Shift: TShiftState; X, Y: Integer);
const
  SC_DRAGMOVE = $F012;
begin
  if Button = mbLeft then
  begin
    ReleaseCapture;
    Perform(WM_SYSCOMMAND, SC_DRAGMOVE, 0);
  end;
end;

 

[Stano 143]

My 200 cents
You have to make the app the way the whole world should use it!
Throw out that maximized window. We already "require" it for two. You've got a picture of why at the end. It's for illustration purposes only.
Every app has to remember:

• the position of each window/form
• the size of the form
• the content of the form

Now this is premature, but you get the point.
Table "dino":

• everything should be capitalized
• name DINOS
• field "record": is a reserved word
• it must be a Primary Key (PK). Values for PK are generated automatically. They have nothing to do with the order of the record. During DB maintenance, the order of records in the table may change

Record lookup:

• Users are used to searching alphabetically
• you have entries written "randomly"

if the table (not only dino) will have a lot of records:

• ComboBox will have large responses. I used it as one of the options. Note the record lookup
• you have to have a search there by typing the first letters. I don't know if you meet that
• I solve it with VirtualStringTree (VST)
• at the same time I use it for basic record display. Instead of DBGrid. It has the following advantages:
• you have full control over everything - very important
• it is really fast
• you can sort the records by the selected column. Ascending or descending
• has a built-in node search by column
• you can filter the records

Disadvantage

1. I had to make my own DBManager for it. This can be worked around

Task for the future:

1. It belongs to make there the possibility to edit records: insert, update, delete...

Sorry, Google translator.

[Columbo 1]

@Stano, I appreciate the critique.

Quote

Throw out that maximized window. We already "require" it for two. You've got a picture of why at the end. It's for illustration purposes only.

The image is not large enough for me to read it properly so I'm not sure what is wrong with the app being maximized when run.

Quote

Now this is premature, but you get the point.
Table "dino":

• everything should be capitalized
• name DINOS 

I am not trying to be nasty or anything but what difference does it make if it is in lowercase?  Just curious as to the reasoning.

Quote

Record lookup:

• Users are used to searching alphabetically
• you have entries written "randomly" 

They are not really random. They are in the order of the era that they lived starting from when life first evolved up to when humans arrived.

Edited December 23, 2023 by Columbo

[Stano 143]

This is not a criticism. These are comments (advice) based on experience, read recommendations and comments from the experienced. I've had my fill of those comments. Some have been quite harsh.
Maximizing the window:

• I want to have a running application available - especially during development. See image. Can't you do a double-click on the image?
• I can't switch it to another mode - size
• can't move it
• you can't even collapse it
• be aware that with my monitor resolution of 3440 x 1440 it's driving me to despair

Table "dino":

• Capitalization is based on practice and some convention, convention.
• Note that all published SQL text and metadata is capitalized
• some DBs require it hardcoded
• it's good to stick to conventions

Record lookup:

• "randomly" I meant they are not alphabetical
• since I read the recent article, there has been a change in the breakdown of eras. I don't know when. And you may already have order problems *)
• those hints are meant in general. You're not supposed to apply them to one case

*) such things are dealt with e.g.

• the primary key must always be
• insert the next column with the order. Depending on the situation it may be:

1. close behind
2. with spaces

To repeat: the physical order of records in tables is not guaranteed. Query with frequent ORDER BY is always used for this!

[Columbo 1]

Merry Christmas to all.

I am trying to make a change to the app to use the Locate() Function but I am having a problem with the proper syntax.  My understanding from what I see in the docs and other data, it is Locate(name of field, what to find, []); but I keep getting an 'Undeclared Identifier Locate' error.  I tried using tblPLife.Locate(name of field, what to find, []); but while the Undeclared Identifier error goes away and the app runs, as soon as I select an item in the Listbox I get a 'Column or Function not found' error.

Here is the code that I was trying:

 

procedure TfrmDino.lbxDataClick(Sender: TObject);

iSelect: Integer;

begin

If lbxData.ItemIndex > -1 then

begin

iSelect := lbx.ItemIndex;

sName := lbxData.Items[iSelect];

tblPLife.Locate(name, sName, []);   //name is the column and sName is what I want to find.

end

Else

ShowMessage('Nothing Selected');

end;

 

Edited December 25, 2023 by Columbo

[Stano 143]

Try

if TableDino.Locate(name, iSelect, []) then
  ShowMessage('Succes')
ele
  ShovMessage('Issue');

Name := 'RECORD';

My previously attached sample shows this nicely.

[Columbo 1]

Hi Stano,  I tried you suggestion:

Quote

if TableDino.Locate(name, iSelect, []) then   //Had to change TableDino to tblPLife
  ShowMessage('Success')
else
  ShowMessage('Issue');

 

The app runs but as soon as I select a name in the Listbox I get an error.  [FireDAC][Stan][Eval]-100 Column of function [frmDino] is not found.

Edited December 25, 2023 by Columbo

[Gary 18]

First param is a string so name needs quotes 'name'

You can leave the last param empty, but then they have to match exactly. That works here where you are selecting from a list, however I like to be more forgiving as to case as it can frustrate users if values are capitalized and they just type lowercase.

You can eliminate both iSelect and sName and the assignment calls like this:

tblPLife.Locate('name', lbxData.Items[lbxData.ItemIndex], [loCaseInsensitive]);

Link is for 12.0 change Athens to Alexandria, but in this case no different

Data.DB.TDataSet.Locate - RAD Studio API Documentation (embarcadero.com)

Data.DB.TLocateOptions - RAD Studio API Documentation (embarcadero.com)