Sqlite3 with Pascal - console
lazarus / 16-Databases

----------------------------------------------------------------------
nugax · 2018-01-19 15:48

I have Linux FP console. I am able to insert and create tables.

I am unsure what function/procedure/etc i need to use in order to
query (SELECT) and retrieve data (STRING).

Can anyone point me in the right direction? Google has failed me.

----------------------------------------------------------------------
Handoko · 2018-01-19 15:53

Hello nugax,
Welcome to the forum.

Maybe this SqlDBHowto tutorial is what you need:
http://wiki.freepascal.org/SqlDBHowto

----------------------------------------------------------------------
nugax · 2018-01-19 16:21

I looked at that but im not sure where i need to be looking to obtain
the data and run a SELECT statement.

----------------------------------------------------------------------
rvk · 2018-01-19 16:33

Quote from: nugax on January 19, 2018, 05:21:02 pmI looked at that but
im not sure where i need to be looking to obtain the data and run a
SELECT statement.
You need to use TSQLite3Connection, TSQLTransaction and a TSQLQuery.

I recently made a small example which can run in console.
(you'll notice I gave SQLTransaction and SQLQuery the owner
SQLConnection so I only needed to fee the last one)

Most of this you should have already because you said you can already
create tables and insert data.
(but have a look anyway)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1; uses sqlite3conn, sqldb, DB, SysUtils; var 
SQLite3Connection1: TSQLite3Connection;  SQLTransaction1:
TSQLTransaction;  SQLQuery1: TSQLQuery;  SQL: String;begin  
Deletefile('test.db'); // remove if you want to keep de db  
SQLite3Connection1 := TSQLite3Connection.Create(nil); 
SQLite3Connection1.DatabaseName := 'test.db';  SQLTransaction1 :=
TSQLTransaction.Create(SQLite3Connection1); 
SQLite3Connection1.Transaction := SQLTransaction1;   if not
FileExists('test.db') then  begin    SQL := 'CREATE TABLE Addresses(ID
INTEGER PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR(25), Name
VARCHAR(25), Category VARCHAR(30), Address VARCHAR(50), PostalCode
VARCHAR(10), City VARCHAR(25), Remarks MEMO, Telephone VARCHAR(15),
Email VARCHAR(50));';    SQLite3Connection1.ExecuteDirect(SQL);    SQL
:= 'INSERT INTO Addresses (FirstName, Name, City) VALUES(''Donald'',
''Trump'', ''Washington'');';   
SQLite3Connection1.ExecuteDirect(SQL);    SQL := 'INSERT INTO
Addresses (FirstName, Name, City) VALUES(''Barack'', ''Obama'',
''Washington'');';    SQLite3Connection1.ExecuteDirect(SQL);   
SQLTransaction1.Commit;  end;  SQLite3Connection1.Connected:=true;  
// From here is the code you want !!   SQLQuery1 :=
TSQLQuery.Create(SQLite3Connection1);  SQLQuery1.Database :=
SQLite3Connection1;  SQLQuery1.Upda]"]>Blockedde := upWhereKeyOnly; 
SQLQuery1.Transaction := SQLTransaction1;   SQL := 'SELECT FirstName,
Name, City FROM Addresses';  SQLQuery1.SQL.Text := SQL; 
SQLQuery1.Open;  while not SQLQuery1.EOF do  begin   
writeln(SQLQuery1.Fieldbyname('FirstName').asString + ' ' +
SQLQuery1.Fieldbyname('Name').asString + ' in ' +
SQLQuery1.Fieldbyname('City').asString);    SQLQuery1.Next;  end;  
SQLite3Connection1.Close;  SQLite3Connection1.Free;  writeln('Press
the return key');  readln; end.

----------------------------------------------------------------------
nugax · 2018-01-19 18:04

Thanks! I found that prior to your answer.

I  havent been able to find the docs on this.

One question though, what does fieldbyname.AsBoolean do? I thought if
it returned a value (wasnt empty) it would return TRUE.

it doesnt seem to do that.

asString does work for me.

----------------------------------------------------------------------
rvk · 2018-01-19 18:07

What does asString return for that field?

If the booleanfield has an invalid value it would return FALSE. It
only returns TRUE for values recognized as true.

If the field contains F or 0, the value is obviously false.

----------------------------------------------------------------------
nugax · 2018-01-19 18:10

If the field is a string and does in fact contain a string (verified
by looking at the return value from .asString)-> does it return true?
it doesnt seem to because my if statements checking fail.

----------------------------------------------------------------------
nugax · 2018-01-19 18:19

The Code:


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unction
verify_user_by_handle(var sHandleGiven: string): boolean;var  
Connect: TSQLite3Connection;  Trans: TSQLTransaction;  Query:
TSQLQuery;  db_name: string[100];  sqlhandle: string[255]; 
sFirstNameFound: String[255]; begin   sFirstNameFound := #0;   //Setup
SQL   db_name := cbbs_info.sDataDir + '/cbbs_users.dbl';  sqlhandle :=
'SELECT firstname FROM cbbs_users WHERE handle = ' + '"' +
sHandleGiven + '"';   //Code to update password from handle  Connect
:= TSQLite3Connection.Create(nil);  Query := TSQLQuery.Create(nil); 
Trans := TSQLTransaction.Create(Connect);  Connect.Transaction :=
Trans;  Connect.DatabaseName := db_name;  Trans.StartTransaction;  //
opens Connect, EInOutError if SQLite not installed  Query.SQL.Text :=
sqlhandle;  Query.Database := Connect;  //Connection for SQL 
Query.Open;   //Code to select items     while not Query.EOF do 
begin     //Check for password    sFirstNameFound :=
Query.FieldByName('firstname').AsString;      write('bool from sql'); 
  write(sFirstNameFound);     if (sFirstNameFound <> ' ') then   
begin      write('returning true');      exit(True); //User handle
existed     end;     if (sFirstnameFound := #0) Then    begin     
write('returning false');      exit(false); //user Handle did not
exist    end;   end;     Query.Next;  Query.Close;  Trans.Commit; 
Query.Free;  Trans.Free;  Connect.Free; end;

----------------------------------------------------------------------
rvk · 2018-01-19 18:29

That will return the firstname from the record where handle is the
supplied sHandleGiven.

With (sFirstNameFound <> ' ') you indeed check if the record found has
a name other then empty.

Although I would do it differently:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
//...Query.Open;Found := (Query.RecordCount > 0) and
(Query.FieldByName('firstname').AsString <>
'');Query.Close;Query.Free;Connect.Free;Result := Found;
No need to commit (your didn't "write" anything) and no need to free
Trans because you gave it Connect as owner and it will be freed
automatically with Connect.

You also had a few problems in your code. You checked for a space " ".
And you need to close before you exit.

Just checking the recordcount assures that there is a record for given
sHandleGiven. Isn't that enhough?

If you can have empty firstname and multiple same handles you can also
check for Firstname <> '' in the SQL.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlhandle
:= 'SELECT firstname FROM cbbs_users WHERE firstname <> '''' AND
handle = ' + '"' + sHandleGiven + '"'; 
And then
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
//...Query.Open;Found := (Query.RecordCount >
0);Query.Close;Query.Free;Connect.Free;Result := Found;Then there is
no need to check for firstname = '' in your code.

----------------------------------------------------------------------
nugax · 2018-01-19 18:42

Variable:= (Query.RecordCount > 0);


Does this return a boolean?

----------------------------------------------------------------------
rvk · 2018-01-19 18:44

Quote from: nugax on January 19, 2018, 07:42:26 pmVariable:=
(Query.RecordCount > 0);
Does this return a boolean?
Yep, that returns a boolean (and variable should be of type boolean).

I used Found as temporary boolean variable because you can't directly
use exit(false) because you need to close the table and free the
objects. So first assign Found := (Query.RecordCount > 0); then close
the table and free the objects and then you can do Result := Found;

----------------------------------------------------------------------
nugax · 2018-01-19 19:34

Yep, got it all working. You gave me the information needed about the
RecordCount.

I thank you!