Get the COUNT(*) value back to Lazarus?

[HatForCat]

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

[mirce.vladimirov]

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;

[HatForCat]

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"

[Abelisto]

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.

[miab3]

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]

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.  

[HatForCat]

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.  

[ASerge]

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]

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.