Sqlite3 with Pascal - console

[nugax]

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]

Hello nugax,
Welcome to the forum.

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

[nugax]

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

[rvk]

I 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][+][-]

1. program project1;
2.  
3. uses sqlite3conn, sqldb, DB, SysUtils;
4.  
5. var
6.   SQLite3Connection1: TSQLite3Connection;
7.   SQLTransaction1: TSQLTransaction;
8.   SQLQuery1: TSQLQuery;
9.   SQL: String;
10. begin
11.  
12.   Deletefile('test.db'); // remove if you want to keep de db
13.  
14.   SQLite3Connection1 := TSQLite3Connection.Create(nil);
15.   SQLite3Connection1.DatabaseName := 'test.db';
16.   SQLTransaction1 := TSQLTransaction.Create(SQLite3Connection1);
17.   SQLite3Connection1.Transaction := SQLTransaction1;
18.  
19.   if not FileExists('test.db') then
20.   begin
21.     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));';
22.     SQLite3Connection1.ExecuteDirect(SQL);
23.     SQL := 'INSERT INTO Addresses (FirstName, Name, City) VALUES(''Donald'', ''Trump'', ''Washington'');';
24.     SQLite3Connection1.ExecuteDirect(SQL);
25.     SQL := 'INSERT INTO Addresses (FirstName, Name, City) VALUES(''Barack'', ''Obama'', ''Washington'');';
26.     SQLite3Connection1.ExecuteDirect(SQL);
27.     SQLTransaction1.Commit;
28.   end;
29.   SQLite3Connection1.Connected:=true;
30.  
31.   // From here is the code you want !!
32.  
33.   SQLQuery1 := TSQLQuery.Create(SQLite3Connection1);
34.   SQLQuery1.Database := SQLite3Connection1;
35.   SQLQuery1.UpdateMode := upWhereKeyOnly;
36.   SQLQuery1.Transaction := SQLTransaction1;
37.  
38.   SQL := 'SELECT FirstName, Name, City FROM Addresses';
39.   SQLQuery1.SQL.Text := SQL;
40.   SQLQuery1.Open;
41.   while not SQLQuery1.EOF do
42.   begin
43.     writeln(SQLQuery1.Fieldbyname('FirstName').asString + ' ' + SQLQuery1.Fieldbyname('Name').asString + ' in ' + SQLQuery1.Fieldbyname('City').asString);
44.     SQLQuery1.Next;
45.   end;
46.  
47.   SQLite3Connection1.Close;
48.   SQLite3Connection1.Free;
49.   writeln('Press the return key');
50.   readln;
51.  
52. end.

[nugax]

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]

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]

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]

The Code:

Code: Pascal  [Select][+][-]

1. unction verify_user_by_handle(var sHandleGiven: string): boolean;
2. var
3.  
4.   Connect: TSQLite3Connection;
5.   Trans: TSQLTransaction;
6.   Query: TSQLQuery;
7.   db_name: string[100];
8.   sqlhandle: string[255];
9.   sFirstNameFound: String[255];
10.  
11. begin
12.  
13.   sFirstNameFound := #0;
14.  
15.   //Setup SQL
16.  
17.   db_name := cbbs_info.sDataDir + '/cbbs_users.dbl';
18.   sqlhandle := 'SELECT firstname FROM cbbs_users WHERE handle = ' + '"' + sHandleGiven + '"';
19.  
20.   //Code to update password from handle
21.   Connect := TSQLite3Connection.Create(nil);
22.   Query := TSQLQuery.Create(nil);
23.   Trans := TSQLTransaction.Create(Connect);
24.   Connect.Transaction := Trans;
25.   Connect.DatabaseName := db_name;
26.   Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not installed
27.   Query.SQL.Text := sqlhandle;
28.   Query.Database := Connect;  //Connection for SQL
29.   Query.Open;
30.  
31.   //Code to select items
32.      while not Query.EOF do
33.   begin
34.  
35.     //Check for password
36.     sFirstNameFound := Query.FieldByName('firstname').AsString;
37.  
38.  
39.     write('bool from sql');
40.     write(sFirstNameFound);
41.  
42.     if (sFirstNameFound <> ' ') then
43.     begin
44.       write('returning true');
45.       exit(True); //User handle existed
46.  
47.     end;
48.  
49.     if (sFirstnameFound := #0) Then
50.     begin
51.       write('returning false');
52.       exit(false); //user Handle did not exist
53.     end;
54.  
55.   end;
56.  
57.  
58.    Query.Next;
59.   Query.Close;
60.   Trans.Commit;
61.   Query.Free;
62.   Trans.Free;
63.   Connect.Free;
64.  
65. end;                
66.  
67.  

[rvk]

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][+][-]

1. //...
2. Query.Open;
3. Found := (Query.RecordCount > 0) and (Query.FieldByName('firstname').AsString <> '');
4. Query.Close;
5. Query.Free;
6. Connect.Free;
7. 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][+][-]

1. sqlhandle := 'SELECT firstname FROM cbbs_users WHERE firstname <> '''' AND handle = ' + '"' + sHandleGiven + '"';
2.  

And then

Code: Pascal  [Select][+][-]

1. //...
2. Query.Open;
3. Found := (Query.RecordCount > 0);
4. Query.Close;
5. Query.Free;
6. Connect.Free;
7. Result := Found;

Then there is no need to check for firstname = '' in your code.

[nugax]

Variable:= (Query.RecordCount > 0);


Does this return a boolean?

[rvk]

Variable:= (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]

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

I thank you!