Recent

Author Topic: database basics  (Read 6809 times)

Rockhaggis

  • Newbie
  • Posts: 4
database basics
« on: May 17, 2014, 04:39:48 pm »
Hi
I want the user to be able to save various 'settings' as a profile, which can be later selected from a DbComboBox and Loaded/Saved etc .

What are the basic database components I should use?
Is there a tutorial or example of this?

Thanks

kapibara

  • Hero Member
  • *****
  • Posts: 517
Re: database basics
« Reply #1 on: May 18, 2014, 05:18:45 am »
Its possible that some of the very experienced programmers here will give you different and better suggestions. Myself have used the SQLite database for app settings. There are also other choices in Lazarus, like TDBF, that you can read about in the wiki.

The wiki covers how to do most things, but I don't remember any example specifically on how to save/load application settings.

If you want to try it with SQLite, here are some pointers. Not too long ago I was new to databases, so I wrote this to check if I remember.. :) Untested, can be some glitch. And if you are totally new to SQL and databases, expect to spend a few months to get proficient.

SQLiteMan, http://sourceforge.net/projects/sqliteman/ can create databases, tables and so on. And also add content.

For an example, make a new database, call it database.db3, then make a table called profiles. Add 3 fields to start with:

An autoincrement primary key named profile_id,
a VARCHAR(30) called profile_name
and a integer called setting_1

The SQLiteMan is good but it doesnt seem possible to create a VARCHAR without choosing "Advanced SQL" from the "Create Table" dialog. There one can just type in VARCHAR(30) instead of the "Text" or "Integer" or whatever is there. You need varchar because thats what the dbcombobox wants.

This is how it should look:

CREATE TABLE "profiles" (
    "profile_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "profile_name" VARCHAR(30) NOT NULL,
    "setting_1" INTEGER,
 )

From SQLiteMan, add some rows with data in your profiles table so you have something to display later.

Ok, to get the values in and out of the combobox I use the SqlDB components.

Here's a quick run-through how to do it:

Make a new test project, drop a TDBComboBox on the form and save. Go to file -> new... and chose DataModule. Save the DataModule a uDM.pas and give it the name DM in project inspector. This module is useful to keep database components and code separate from the form code etc.

Now go to the SqlDB tab in Lazarus and drop the following components on the DataModule.

TSQLite3Connection (name it Conn)
TSQLTransaction (name it TX)
TQuery (name it qryProfiles)

Set:
Conn.Databasename: c:\myproject\database.db3
Conn.Transaction: TX

You can now test the connection to the SQLite database file by toggling the property 'Connected' to true.

Now check the TX component and you'll see that 'Database' is already filled in, that happened when you chose Transaction in the Connection component.

Now go to qryProfiles and set the 'Database' property to 'Conn' and put
'SELECT * FROM profiles' in the SQL property. After adding this SQL statement it is possible to set the query (qryProfiles) to 'Active', do that.

To get the data into the dbcombobox you need a link between the dataset (which is qryProfiles) and the dbcombobox. That link is a TDataSource component. Drop one on the DataModule and name it dtsProfiles. Set the DataSet property to qryProfiles and chose a datafield item to display in the dbcombobox.

The TDBComboBox is not on the form yet so drop such a one on the main form, NOT on the datamodule. Choose DM.dtsProfiles as DataSource. If all the components above are wired together you should see the content of the first record of the database in the dbcombobox.

Now you have the following chain between your data in the database and the program:
Connection->Transaction->Query->DataSource->DBAware Visual Component (DBComBox)

If you run the application with the database in connected state in Lazarus you will get an error. You must set Connected to false before you run the app. Call DM.qryProfiles.Open from the DataModules OnCreate event. I think you should add uDM to the uses clause of the main unit.

Setting qryProfiles (the Query) to active (Open) automatically activates the transaction and opens the database. No need to open database and activate transaction manually.

Be aware that TDBComboBox doesn't populate the list of items that you select from. You have to do that yourself. Which is easy. But TDBLookupComboBox can do this automatically. As I understand it, the plain DBComboBox is used when you have just a few choices that you know beforehand. Someone correct me if I've misunderstood.

I'm running out of time now, so here's a cut & paste from some old code I have for populating the dbcombobox list. You have to change it to match your need, or ask if you dont understand.

Code: [Select]
procedure TfrmMain.FormCreate(Sender: TObject);
begin
  DM.qryCountries.Open;
  PopulateComboBox(cbCountries);
end;


Code: [Select]
procedure TfrmMain.PopulateComboBox(AComboBox: TDBComboBox);
const
  COUNTRYNAME_FIELD = 0;
begin
  with DM.qryCountries do
  begin
    SQL.Text:='SELECT country_name FROM common.countries';
    try
      Open;
      while(not EOF) do
      begin
        AComboBox.Items.Add(Fields[COUNTRYNAME_FIELD].AsString);
        Next;
      end;
      Close;
      if AComboBox.Items.Count > 0 then
        AComboBox.ItemIndex:=0;
    except
      ;
    end;
  end;
end;

The dbcombobox OnChange event could need:

Code: [Select]
procedure TfrmMain.cbCountriesChange(Sender: TObject);
begin
  with Sender as TDBComboBox do
    DM.qryCountries.Locate('country_name', Text, []);
end;

Adding an item to database:

Code: [Select]
procedure TfrmMain.btnAddCountryClick(Sender: TObject);
var
  CountryName: string;
begin
  CountryName:= InputBox('Add New Country', 'Enter Name:','');
  try
    DM.TX.Commit;
    DM.TX.StartTransaction;
    DM.Conn.ExecuteDirect('INSERT INTO common.countries (country_name) VALUES ('''+CountryName+''')');
    DM.TX.Commit;
    DM.qryCountries.Open;
  except
    ;
  end;
  cbCountries.Items.Clear;
  PopulateComboBox(cbCountries);
end;
« Last Edit: May 18, 2014, 05:32:55 am by kapibara »
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

Rockhaggis

  • Newbie
  • Posts: 4
Re: database basics
« Reply #2 on: May 18, 2014, 12:26:28 pm »
Excellent reply ... much to read through. Many thanks!  :D

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: database basics
« Reply #3 on: June 21, 2014, 12:12:29 pm »
If you haven't found it already, there's a ton of info on the wiki as well:

see
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
and the other tutorials linked there
and in general
http://wiki.lazarus.freepascal.org/Category:Databases

There's also
http://wiki.lazarus.freepascal.org/Adventures_of_a_Newbie
which I found very interesting as it shows some of the pitfalls newcomers may fall into... and their solutions.

By the way: you posted this in the FreePascal section... however you're talking about GUI components. GUI components are included in Lazarus, not in FPC - which contains low level and text mode components only.
Not a big deal, just wanted to let you know as once you start submitting bug reports for annoyances, improvements and bugs you'll need to know where to post ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified