Recent

Author Topic: How to do this from Lazarus?  (Read 4609 times)

Gosh

  • Jr. Member
  • **
  • Posts: 72
How to do this from Lazarus?
« on: April 27, 2015, 07:41:42 pm »
I want to convert a smaller project from VB.net to Lazarus IDE. I didn't work with pascal before and therefore I am not sure how to finish some things.
 
  Generally, I need to fill a ListView control from database. In .net, I was using a datareader object for that purpose. I am not sure how to do the same thing from Lazarus/FP.

Can someone try to show how to convert this code to a FP equivalent?  I'm not sure that datareader exists in this programming language (Free Pascal) and if it is true, how to finish this job without them?
   
 
Quote
           conn.Open()
            comm = New OleDbCommand("SELECT * FROM updb", conn)
            dr = comm.ExecuteReader

            ListView1.Items.Clear()
            ListView1.BeginUpdate()

            Do While dr.Read
                item1 = ListView1.Items.Add(dr("ID"))
                With item1               
                    .SubItems.Add(dr("FName"))
                    .SubItems.Add(dr("LName"))
                    .SubItems.Add(dr("Score"))      
                End With
            Loop
            ListView1.EndUpdate()

paweld

  • Hero Member
  • *****
  • Posts: 970
Re: How to do this from Lazarus?
« Reply #1 on: April 27, 2015, 08:36:19 pm »
Code: [Select]
zconnection1.Connect; //zeoslib connection
zquery1:=TZQuery.Create; //zeoslib query
zquery1.Connection:=zconnection1;
zquery1.SQL.Text:='SELECT * FROM updb';
zquery1.Open;
zquery1.First;
ListView1.Items.Clear;
ListView1.BeginUpdate;
while not zquery1.EOF do
begin
  item1:=ListView1.Items.Add;
  item1.Caption:=zquery1.FieldByName('ID').AsString;
  item1.SubItems.Add(zquery1.FieldByName('FName').AsString);
  item1.SubItems.Add(zquery1.FieldByName('LName').AsString);
  item1.SubItems.Add(zquery1.FieldByName('Score').AsString);
  zquery1.Next;
end;
zquery1.Close;
ListView1.EndUpdate;
« Last Edit: April 27, 2015, 09:04:43 pm by paweld »
Best regards / Pozdrawiam
paweld

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: How to do this from Lazarus?
« Reply #2 on: April 27, 2015, 09:36:56 pm »
Similar, but using standard SQLdb:

Code: [Select]
procedure TForm1.PopulateListView(Sender: TObject);
var
  ListItem: TListItem;
begin
  with TSQLQuery.Create do
  begin
    DataBase:=Conn;
    Transaction:=Conn.Transaction;
    SQL.Text:='SELECT * FROM updb';
    Open;
    ListView1.Items.BeginUpdate;
    try
      ListView1.Items.Clear;
      while not EOF do
      begin
        ListItem := ListView1.Items.Add;
        ListItem.Caption := FieldByName('ID').AsString;
        ListItem.SubItems.Add(FieldByName('FName').AsString);
        ListItem.SubItems.Add(FieldByName('LName').AsString);
        ListItem.SubItems.Add(FieldByName('Score').AsString);
      end;
    finally
      Close;
      Free;
      ListView1.Items.EndUpdate;
    end;
  end;
end;


Dont forget to add sqldb to the uses clause.
« Last Edit: April 27, 2015, 09:42:51 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

Gosh

  • Jr. Member
  • **
  • Posts: 72
Re: How to do this from Lazarus?
« Reply #3 on: April 27, 2015, 09:39:26 pm »
Thank you guys. I'll check your code soon and put some answers if any :)

Gosh

  • Jr. Member
  • **
  • Posts: 72
Re: How to do this from Lazarus?
« Reply #4 on: April 28, 2015, 01:36:22 pm »
uhmmm.... I tried this example with SQLdb (more simple solution, at least) but I have a lot of errors.

I added sqldb as you suggested but it is obviously not enough for now.
Should I put some sqldb components to the form previously and if yes, which exactly?

Next, what is 'database' in this code: DataBase:=Conn; ?
how I have do declare them and where?

PopulateListView is user's procedure which have to be called from another place to finish its job?

There's a lot questions more but this should be enough for the beginning.

I know that my questions sounds a little bit stupidly but I am rather confused with new syntax.

« Last Edit: April 28, 2015, 08:50:04 pm by Gosh »

sky_khan

  • Guest
Re: How to do this from Lazarus?
« Reply #5 on: April 28, 2015, 04:48:55 pm »
steps:
from component palette "SQLdb" tab , put one of these components on your form
-a database connection component (e.g TSQLite3Connection)
-a TSQLTransaction component
-a TSQLQuery component

-select connection component on your form and set its Transaction property to SQLTransaction1 from Object Inspector and set other required properties like DatabaseName
-select query component and set its Database property to SQLite3Connection1 (if you choose another type of connection its name will be different of course)
-set SQL property of query component to 'SELECT * FROM updb'

now. assuming you have a TListView component named ListView1 and a TButton component  named Button1 on your form,
you can fill listview this way:

procedure TForm1.Button1Click(Sender: TObject);
var
  ListItem : TListItem;
begin
  SQLQuery1.Open;
  ListView1.Items.BeginUpdate;
  try
    ListView1.Items.Clear;
    while not SQLQuery1.EOF do
    begin
      ListItem := ListView1.Items.Add;
      ListItem.Caption := SQLQuery1.FieldByName('ID').AsString;
      ListItem.SubItems.Add(SQLQuery1.FieldByName('FName').AsString);
      ListItem.SubItems.Add(SQLQuery1.FieldByName('LName').AsString);
      ListItem.SubItems.Add(SQLQuery1.FieldByName('Score').AsString);
      SQLQuery1.Next;
    end;
  finally
    ListView1.Items.EndUpdate;
  end;
end;

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: How to do this from Lazarus?
« Reply #6 on: April 28, 2015, 08:09:00 pm »
Your questions are good. Go for sky_khan's approach for now. He removed the "with", and that makes it easier for someone who is new to understand the code. SQLdb always need a connection and a TSQLTransaction. I erroneously took for granted you had them ready for use.

PopulateListView is a procedure I added to the TForm1 class. (You can add any routines you need under the private and public section in the class interface). Sky_khan again simplified it for you and put all the code in a buttons eventhandler so you just have to double click the button and paste your code.

Otherwise you should have added PopulateListView like this:

  TForm1 = class(TForm)
  //...
  private
    procedure PopulateListView;
  public

  end;

After that you press CTRL+SHIFT+C to generate the procedure in the implementation section, and add your code there.

implementation

procedure TForm1.PopulateListView;
begin
  //Add your code here..
end;

The Lazarus WIKI has a lot about databases, a place to start:

http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

Gosh

  • Jr. Member
  • **
  • Posts: 72
Re: How to do this from Lazarus?
« Reply #7 on: April 28, 2015, 09:45:55 pm »
Thank you guys for these detailed explanations. In general' I've understood some main principles how to work with DB under pascal. I just need some time to feel myself more comfortable in the new IDE.

In general, this code works, ListView is being populated with ID field from database but without other columns. When I changed View style property from vsList to vsReport was completely blank. When I added columns manually , everything started to work like a charm.

There's just one thing which I couldn't understand. Why in this line:

ListItem.Caption := SQLQuery1.FieldByName('ID').AsString;

we have 'caption' property instead of adding of the first row of our ListView control?

EDIT: Why when I click on the button again Listview control becomes blank?
« Last Edit: April 28, 2015, 09:49:51 pm by Gosh »

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: How to do this from Lazarus?
« Reply #8 on: April 28, 2015, 11:30:51 pm »
When you click the second time, the SQLQuery1 has already its cursor at the end because we have already looped through all the rows to EOF. The query is never closed, so the second call to Open has no effect and the cursor stays where it is and the while-loop never loops and the ListView is never filled. But if you look at the code you see that a call to Clear is done.

So, after the line with

Code: [Select]
SQLQuery1.Open
add
Code: [Select]
SQLQuery1.First
That puts the query's cursor back to the first row, like it was when you clicked the first time. And you should see data in the ListView.


The TListItem is a separate class and the SubItems is of the TStrings class. Those who created this design probably had some reason. Perhaps someone else knows more.

A tip that can be handy: if you have many thousands of rows in the ListView and you think it becomes slow, you can put the ListView into virtual mode. It will then be very fast however many rows there are. Its just a few lines of code and is described in some forum threads.
« Last Edit: April 29, 2015, 12:26:20 am by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

Gosh

  • Jr. Member
  • **
  • Posts: 72
Re: How to do this from Lazarus?
« Reply #9 on: April 29, 2015, 03:37:45 pm »
I've tried to google about virtual ListView but I couldn't find anything concrete which would explain how to use them.

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: How to do this from Lazarus?
« Reply #10 on: April 30, 2015, 12:50:15 pm »
Instead of Google, you can search in this forum directly. There are information about most things Lazarus in earlier threads.

Listview in virtual mode / load on demand:

http://forum.lazarus.freepascal.org/index.php/topic,22238.0.html

Did your data load alright last time?
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

Gosh

  • Jr. Member
  • **
  • Posts: 72
Re: How to do this from Lazarus?
« Reply #11 on: April 30, 2015, 02:52:36 pm »
Yes, they did. Thank you.

I will check this code for LV virtual mode  in the next few days.

 

TinyPortal © 2005-2018