* * *

Author Topic: Get the COUNT(*) value back to Lazarus?  (Read 632 times)

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
Get the COUNT(*) value back to Lazarus?
« on: April 28, 2017, 11:55:25 pm »
Hi, I have been trying to get this to work and all the examples I have found return a value of 1.

Code: Pascal  [Select]
  1. begin
  2.   myDB.SQL.Text:='SELECT COUNT(*) FROM People;';    // Has 200 records
  3.   myDB.ExecSQL;
  4.   Label1.Caption:=IntToStr(myDB.Fields[0].AsInteger);           // returns 1
  5.   Label1.Caption:=IntToStr(myDB.FieldValues[0].AsInteger);   // returns 1
  6.   Label1.Caption:=myDB.Fields[0].AsString;                             // returns 1
  7.   // if I do
  8.   myDB.SQL.Text:='SELECT COUNT(*) AS Ctr FROM People;';    // Has 200 records
  9.   myDB.ExecSQL;
  10.   Label1.Caption:=myDB.FieldByName('Ctr').AsString; // "No such Field name as Ctr"
  11. end;
  12.  

What am I missing here?

Thanks
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

mirce.vladimirov

  • Full Member
  • ***
  • Posts: 209
Re: Get the COUNT(*) value back to Lazarus?
« Reply #1 on: April 29, 2017, 12:16:36 am »
You should use
Code: Pascal  [Select]
  1. myDB.ExecSQL;
for queries that do not return result, such as INSERT query, DELETE query, UPDATE, USE, ALTER....

When yo want to run a query which returns result, like you do with SELECT statement in your example then :
Code: Pascal  [Select]
  1. myDB.Open;

That's all

EDIT: forgot to say, always use aliasing of result-fields, always use
Code: Pascal  [Select]
  1. SELECT count(*) AS myfieldname FROM mytable

This way you can :
Code: Pascal  [Select]
  1. Label1.Caption:=myDB.FieldByName('myfieldname').Asstring;
« Last Edit: April 29, 2017, 12:21:19 am by mirce.vladimirov »

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
Re: Get the COUNT(*) value back to Lazarus?
« Reply #2 on: April 30, 2017, 12:41:49 am »
Thanks, mirce.vladimiro, but, none of that makes any difference.

"Open" or "ExecSQL" still return zero.

Line 8 of my code above is using in Aliasing and it still returns "Field Not Fond: myfieldname"
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

Abelisto

  • Jr. Member
  • **
  • Posts: 60
Re: Get the COUNT(*) value back to Lazarus?
« Reply #3 on: April 30, 2017, 01:35:25 am »
Everything works as expected:
Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.     try
  4.         with SQLite3Connection1 do
  5.         begin
  6.             DatabaseName := '/tmp/test.db';
  7.             Connected := True;
  8.             ExecuteDirect('create table People as select * from (values(1),(2),(3))');
  9.         end;
  10.  
  11.         with SQLQuery1 do
  12.         begin
  13.             Close;
  14.             SQL.Text := 'select count(*) from People';
  15.             Open;
  16.             ShowMessage(Fields[0].AsString); // Shows "3"
  17.             Close;
  18.             SQL.Text := 'select count(*) as cnt from People';
  19.             Open;
  20.             ShowMessage(FieldByName('cnt').AsString); // Shows "3" again
  21.             Close;
  22.         end;
  23.     finally
  24.         SQLite3Connection1.Connected := False;
  25.         DeleteFile('/tmp/test.db');
  26.     end;
  27. end;

Try to find the difference.
OS: Linux Mint + MATE, Compiler: FPC trunk (yes, I am risky!), IDE: Lazarus trunk

miab3

  • Jr. Member
  • **
  • Posts: 92
Re: Get the COUNT(*) value back to Lazarus?
« Reply #4 on: April 30, 2017, 12:22:33 pm »
Or with ZEOS 7.2
Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.     try
  4.         with ZConnection1 do
  5.         begin
  6.             Protocol:='sqlite-3';
  7.             Database := 'c:/tmp/test.db';
  8.             Connected := True;
  9.             ExecuteDirect('create table People as select * from (values(1),(2),(3))');
  10.         end;
  11.  
  12.         with ZQuery1 do
  13.         begin
  14.             Connection:=ZConnection1;
  15.             Close;
  16.             SQL.Text := 'select count(*) from People';
  17.             Open;
  18.             ShowMessage(Fields[0].AsString); // Shows "3"
  19.             Close;
  20.             SQL.Text := 'select count(*) as cnt from People';
  21.             Open;
  22.             ShowMessage(FieldByName('cnt').AsString); // Shows "3" again
  23.             Close;
  24.         end;
  25.     finally
  26.         ZConnection1.Connected := False;
  27.         DeleteFile('c:/tmp/test.db');
  28.     end;
  29. end;

Michal

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
Re: Get the COUNT(*) value back to Lazarus?
« Reply #5 on: April 30, 2017, 08:01:33 pm »
Everything works as expected:

Thanks for taking the time, but I get a

Syntax error: near "values"

I am quite new to SQL, so will do some searching to see if I can get that working first.

Am I using the correct components? All with default values.

Code: Pascal  [Select]
  1.   TForm1 = class(TForm)
  2.     Button1: TButton;
  3.     DataSource1: TDataSource;
  4.     SQLite3Connection1: TSQLite3Connection;
  5.     SQLQuery1: TSQLQuery;
  6.     SQLTransaction1: TSQLTransaction;
  7.     procedure Button1Click(Sender: TObject);
  8.   private
  9.     { private declarations }
  10.   public
  11.     { public declarations }
  12.   end;
  13.  
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
Re: Get the COUNT(*) value back to Lazarus?
« Reply #6 on: April 30, 2017, 08:13:57 pm »
OK, I have it working but it returns "0" for both ShowMessage(...

Is this a Lazarus/Ubuntu thing?

BUT - the "ShowMessage(FieldByName('cnt').AsString);" is not showing an error for the 'cnt' as it does in my original code.

Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.     try
  4.         with SQLite3Connection1 do
  5.         begin
  6.             DatabaseName := '/tmp/test.db';
  7.             Connected := True;
  8.             ExecuteDirect('CREATE TABLE People (c1 Integer, c2 integer,c3 integer);');
  9.         end;
  10.  
  11.         with SQLQuery1 do
  12.         begin
  13.             Close;
  14.             SQL.Text:='UPDATE People SET c1 = 5, c2 = 7, c3 = 9;';
  15.             ExecSQL;
  16.             Close;
  17.             SQL.Text := 'select count(*) from People';
  18.             Open;
  19.             ShowMessage(Fields[0].AsString); // Shows "0" for me
  20.             Close;
  21.             SQL.Text := 'select count(*) as cnt from People';
  22.             Open;
  23.             ShowMessage(FieldByName('cnt').AsString); // Shows "0" for me again
  24.             Close;
  25.         end;
  26.     finally
  27.         SQLite3Connection1.Connected := False;
  28.         DeleteFile('/tmp/test.db');
  29.     end;
  30. end;
  31.  
« Last Edit: April 30, 2017, 08:17:10 pm by HatForCat »
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

ASerge

  • Sr. Member
  • ****
  • Posts: 280
Re: Get the COUNT(*) value back to Lazarus?
« Reply #7 on: April 30, 2017, 08:32:14 pm »
OK, I have it working but it returns "0" for both ShowMessage(...
1. You create the table. OK. No records.
2. You update the table. OK. Because there are no records, then nothing is updated.
3.You see how many records in the table. Yes, indeed, zero.
And what's strange?

mangakissa

  • Hero Member
  • *****
  • Posts: 668
Re: Get the COUNT(*) value back to Lazarus?
« Reply #8 on: May 01, 2017, 09:06:52 am »
https://www.w3schools.com/sql/sql_insert.asp
https://www.w3schools.com/sql/sql_update.asp
https://www.w3schools.com/sql/sql_select.asp
https://www.w3schools.com/sql/sql_func_count.asp

Read this first before you go further. Tr to understand what you're doing.
You have already the answer if you understand what you try to accomplish.
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus