SQLite3 + FPC. native!

[Seenkao]

Всем привет!
Есть ли примеры, где используется SQLite3 без лишних обёрток? Мне не нужен ни какой графический интерфейс, мне нужно работать с базой данных в консоли.

-------------------------------
Google translate:
Hi all!
Are there any examples where SQLite3 is used without unnecessary wrappers? I don't need any GUI, I need to work with the database in the console.

[marcov]

Read the source of the wrappers?

[TRon]

And that is besides the fact that you can access SQL databases using their wrappers without an actual gui and from the console.

[Seenkao]

Я пишу про примеры! 

Я могу использовать сам SQLite3 в консоли. Я вижу пример на другом ЯП. Я хочу состыковать и понять как это сделано на Паскале.
Я просто не хочу изобретать очередной велосипед: брать код из C/C++ и перекидывать его на Паскаль. Но с подобными ответами, видимо это и придётся делать. 
Неужели ни кто этого не делал?

-------------------------------
Google translate:
I write about examples!

I can use SQLite3 itself in the console. I see an example in another language. I want to connect and understand how this is done in Pascal.
I just don’t want to reinvent another wheel: take code from C/C++ and transfer it to Pascal. But with such answers, apparently this is what will have to be done.
Has no one ever done this?

[Thaddy]

I don't know if he translated sqlite3 too, but a full translation of sqlite2 has been made by
https://www.yunqa.de/delphi/
I mean, full translation, not headers. pure pascal code.
but it is not free.

[TRon]

I write about examples!

Oh, yes... you are right and I missed that. Apologies.

Has no one ever done this?

I think I have an idea what those c examples look like (edit see Awkward's post below this/mine) and I wonder if you would want to punish yourself that way.

Database components are non visual components so can be used as such.

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

1. program dbtest;
2.  
3. {$MODE OBJFPC}{$H+}
4.  
5. uses
6.   classes, sysutils, dateutils, db, sqldb, sqlite3conn;
7.  
8. var
9.   DBConnection  : TSQLite3Connection;
10.   DBTransaction : TSQLTransaction;
11.   DBQ           : TSQLQuery;
12.  
13.   {$IFDEF LCL}
14.   // For Lazarus Change MainForm.YourLogMem to point to a Memo on your Form
15.   LogMemo   : TMemo absolute MainForm.YourLogMemo;
16.   {$ENDIF}
17.  
18.   procedure WriteLog(S:String);inline;begin{$IFDEF LCL}LogMemo.Append{$ELSE}WriteLn{$ENDIF}(S);end;
19.   procedure WriteLog;begin WriteLog('')end;
20.   procedure WriteLog(S:String;const a:array of const);begin WriteLog(Format(S,a));end;
21.  
22.  
23. procedure CreateDB(ADataBaseName: string);
24. begin
25.   DBConnection := TSQLite3Connection.Create(nil);
26.  
27.   DBConnection.DatabaseName := ADataBaseName;
28.   DBTransaction := TSQLTransaction.Create(DBConnection);
29.   DBTransAction.Database := DBConnection;
30.  
31.   DBQ             := TSQLQuery.Create(DBConnection);
32.   DBQ.Database    := DBConnection;
33.   DBQ.Transaction := DBTransaction;
34. end;
35.  
36. procedure DestroyDB;
37. begin
38.   DBConnection.Free;
39. end;
40.  
41.  
42. procedure test;
43. var
44.   TableName: string;
45.   TableNames: TStringList;
46.   n : integer;
47. begin
48.   WriteLog('DBConnection.DatabaseName = "%s"', [DBConnection.DataBaseName]);
49.   TableNames := TStringList.Create;
50.   DBConnection.GetTableNames(TableNames);
51.   n := 1;
52.   for TableName in TableNames do
53.   begin
54.     WriteLog('Tablename[%d] = %s', [n, TableName]);
55.     inc(n);
56.   end;
57.  
58.   TableNames.Free;
59. end;
60.  
61. procedure FIllDB;
62. var
63.   n: integer;
64. begin
65.   DBConnection.ExecuteDirect('CREATE TABLE "data" (num INTEGER);');
66.   DBQ.SQL.text := 'INSERT INTO "data" (num) values (:number)';
67.   for n := 1 to 100 do
68.   begin
69.     DBQ.Params.ParamByName('number').AsInteger := n;
70.     DBQ.ExecSQL;
71.   end;
72.   DBTransaction.Commit;
73. end;
74.  
75. procedure test2;
76. var
77.   numfield: TField;
78.   x:integer;
79. begin
80.   writeln('select');
81.   DBQ.SQL.Text := 'select * from data';
82.   writeln('open');
83.   DBQ.Open;
84.   writeln('last');
85.   DBQ.Last;
86.   writeln('first');
87.   DBQ.First;
88.   numfield := DBQ.FieldByName('num');
89.   while not DBQ.EOF do
90.   begin
91.     x := numfield.AsInteger;
92.     writeln('x = ', x);
93.     DBQ.Next;
94.   end;
95. end;
96.  
97. procedure RunningMan;
98. begin
99.    WriteLog('begin');
100.    try
101.      CreateDB('test.s3db');
102.      if not fileexists('test.s3db')
103.        then FillDB;
104.      writeln('test1');
105.      test;
106.      writeln('test2');
107.      test2;
108.    finally
109.      DestroyDB;
110.    end;
111.    WriteLog('end');
112. end;
113.  
114. begin
115.   RunningMan;
116. end.
117.  

[Awkward]

What about internet search and C API using? Like

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

1. function GetTextValue(db:PSQLite3; const atable, afield, acond:string):string;
2. var
3.   lSQL:string;
4.   vm:pointer;
5. begin
6.   result:='';
7.   if db<>nil then
8.   begin
9.     lSQL:='SELECT '+afield+' FROM '+atable+' WHERE '+acond;
10.     if sqlite3_prepare_v2(db, PAnsiChar(lSQL),-1, @vm, nil)=SQLITE_OK then
11.     begin
12.       if sqlite3_step(vm)=SQLITE_ROW then
13.       begin
14.         result:=sqlite3_column_text(vm,0);
15.       end;
16.       sqlite3_finalize(vm);
17.     end;
18.   end;
19. end;
20.  

[Handoko]

I have a SQLite demo written using Lazarus, without using TDBNavigator, TDBEdit, TDBMemo, etc. I am not sure but maybe it can be useful:

https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.html#msg496461

[Seenkao]

TRon, Благодарю!
Это достаточно подробный пример!

Awkward, мне приходится смотреть разные ЯП. Не критично как выглядит в них код, зачастую критично понимание кода. И смотря код с использованием LCL я не достаточно ясно понимаю как всё работает и мне приходится по крупинкам брать всё из разных примеров.

Handoko, Благодарю! Гляну.



Google translate:
TRon, Thank you!
This is quite a detailed example!

Awkward, I have to watch different languages. It is not critical how the code looks in them; understanding the code is often critical. And looking at the code using LCL, I don’t clearly understand how everything works and I have to take everything bit by bit from different examples.

Handoko, Thank you! I'll take a look.

[cdbc]

Hi
I dunno... But maybe this unit can be of help to you...
It's been my SQLite3 workhorse for years and it needs to be refactored, ah well, that's for a rainy day 
Regards Benny

[TRon]

TRon, Thank you!

You are most welcome.

The part that is important to understand from that is is that you can use about any non visual component that way (provided the components does not have any GUI related dependencies)

This is quite a detailed example!

I used it as an example for someone else on this forum so I am sorry for any possible additional noise in the code that doesn't really belong there (just try to ignore that).

You can use almost any example from the wiki to accomplish the same task without using a GUI (with the exception of visual components like f.e. a dbedit or dbgrid etc).

I prefer to setup my databases manually (even those with a GUI) to make it clear to the reader of the code what is done to initialize the database components. Using the object inspector to set the database properties and attaching events is usually not the most brightest idea and doing it this way allows to initialize the database components at any given time in your code (e.g. only when necessary though you can achieve similar results with using datamodules for/with GUI applications).

@cdbc:
Nice ! I use something similar when developing (smaller) projects that require database access.

[Thaddy]

I still hope the newspaper boy will release his translation as open source.