How to do this from Lazarus?
lazarus / 16-Databases

----------------------------------------------------------------------
Gosh · 2015-04-27 17:41

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 · 2015-04-27 18:36

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;

----------------------------------------------------------------------
fpcoder · 2015-04-27 19:36

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.

----------------------------------------------------------------------
Gosh · 2015-04-27 19:39

Thank you guys. I'll check your code soon and put some answers if any

----------------------------------------------------------------------
Gosh · 2015-04-28 11:36

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.

----------------------------------------------------------------------
sky_khan · 2015-04-28 14:48

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;

----------------------------------------------------------------------
fpcoder · 2015-04-28 18:09

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

----------------------------------------------------------------------
Gosh · 2015-04-28 19:45

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?

----------------------------------------------------------------------
fpcoder · 2015-04-28 21:30

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.

----------------------------------------------------------------------
Gosh · 2015-04-29 13:37

I've tried to google about virtual ListView but I couldn't find
anything concrete which would explain how to use them.

----------------------------------------------------------------------
fpcoder · 2015-04-30 10:50

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?

----------------------------------------------------------------------
Gosh · 2015-04-30 12:52

Yes, they did. Thank you.

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