Database example without GUI

[Tanto]

Hello guys,
I am looking for an example to create a database connection without using a graphical interface.
I have already searched google and searched everything here in the forum, but always find only examples with GUI
Do any of you have a page or example that I can work with?

Thank you very much

[tr_escape]

Did you search google?

https://gist.github.com/pakLebah/277e0875a9ff50b9186fa9e166667add

[MarkMLl]

I suggest looking at the tutorials etc. under "S" at https://wiki.freepascal.org/Category:Databases

Broadly speaking, with the exception of the explicitly GUI stuff most of the classes etc. are in the FCL (Freepascal Class Library) rather than in the LCL (Lazarus Class Library).

MarkMLl

[Tanto]

thx to:
tr_escape
and
MarkMLl
i will show and try and give an answer

thx

[Handoko]

I am looking for an example to create a database connection without using a graphical interface.

The 49 lines of code testfirebird provided by rvk, tested and worked:
https://forum.lazarus.freepascal.org/index.php/topic,63142.msg477919.html#msg477919

[MarkMLl]

i will show and try and give an answer

Keep at it, we're definitely not trying to give you the brushoff and I for one agree that it's something that's under-discussed.

Having said which, there's some things that a database's backend typically allows you to do (database and user/role creation as examples) that have portability problems, so don't expect that you'll be able to write a single text frontend and apply it to all of the available servers. I've successfully done that targeting PostgreSQL + Firebird, but to get much further with... well, quite frankly anything you're going to need to tell us what you're using.

MarkMLl

[Tanto]

Hello Handoko and the other guy's,

i've tried the example (and a lot more), by adapting it for sqlite,
but everytime i got an "Error: Access violation"
by line see '<<--'

here my example code wich i wrote by all examples of everything i show:

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

1. unit DataBaseModul1;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, fpWeb, db, sqldb, sqlite3conn;
9.  
10. type
11.   TMyDataBaseConnector = class(TObject)
12.     sqlite3: TSQLite3Connection;
13.     dbquery: TSQLQuery;
14.   private
15.     { will be come later }
16.   public
17.     constructor Create;
18.     function CheckUserEntry(uname: String; ukey: String): String;
19.   end;
20.  
21. var
22.   DBModul: TMyDataBaseConnector;
23.  
24. implementation
25.  
26.   constructor TMyDataBaseConnector.Create;
27.   begin
28.    
29.   end;
30.  
31.   function TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String): String;
32.   begin
33.     sqlite3:= TSQLite3Connection.Create(nil);
34.  
35.     sqlite3.Transaction:= TSQLTransaction.Create(sqlite3);
36.     sqlite3.Transaction.DataBase:= sqlite3;
37.     sqlite3.LoginPrompt:= False;
38.     sqlite3.DatabaseName:= 'test.db';
39.     sqlite3.Connected:= True;  // <<-- here comes the error
40.  
41.     dbquery:= TSQLQuery.Create(sqlite3);
42.     dbquery.DataBase:= sqlite3;
43.     dbquery.Transaction:= sqlite3.Transaction;
44.  
45.     try
46.       if FileExists(sqlite3.DatabaseName) then
47.          WriteLn('File EXISTS');
48.  
49.       if sqlite3.Connected then
50.          WriteLn('CONNECTED');
51.  
52.       WriteLn('SELECT');
53.       dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';
54.  
55.       WriteLn('> Execute SQL');
56.       dbquery.Open;
57.  
58.       WriteLn('Read EOF');
59.       while not dbquery.EOF do
60.       begin
61.         WriteLn(dbquery.FieldByName('username').AsAnsiString);
62.         dbquery.Next;
63.       end;
64.     except on E: Exception do
65.       WriteLn('Something happens: ', E.ToString);
66.     end;
67.  
68.  
69.     CheckUserEntry:= '';
70.   end;
71.  
72. end.
73.  
74.  

i hope anybody can help me to solve the problem

thx at all

[dseligo]

Try this for sqlite:

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

1. program sqlite_sqldb;
2.  
3. uses SysUtils, sqlite3conn, sqldb;
4.  
5. var
6.   SQLite3Connection: TSQLite3Connection;
7.   Query: TSQLQuery;
8.   Transaction: TSQLTransaction;
9.  
10. begin
11.   SQLite3Connection := TSQLite3Connection.Create(nil);
12.   Transaction := TSQLTransaction.Create(nil);
13.   Query := TSQLQuery.Create(nil);
14.   try
15.     Transaction.Action := caCommit;
16.     Transaction.Options := [stoUseImplicit];
17.  
18.     // choose database (file) name
19.     SQLite3Connection.DatabaseName := 'mytest.db3';
20.     SQLite3Connection.Transaction := Transaction;
21.     try
22.       SQLite3Connection.Connected := True;
23.     except
24.       on E: Exception do
25.       begin
26.         WriteLn('Error: ', E.Message);
27.         Exit;
28.       end;
29.     end;
30.  
31.     Query.DataBase := SQLite3Connection;
32.     Query.Transaction := Transaction;
33.  
34.     // check if 'mytablename' exists
35.     Query.SQL.Text := 'select name ' +
36.                       'from sqlite_schema ' +
37.                       'where type = ''table'' ' +
38.                       'and name = ''mytablename''';
39.     Query.Open;
40.     If Query.EOF then
41.     begin
42.       Query.Close;
43.       Query.SQL.Text := 'create table mytablename ' +
44.                         '(myid integer, somedata integer, primary key (myid))';
45.       Query.ExecSQL;
46.  
47.       Query.SQL.Text := 'insert into mytablename ' +
48.                         '(somedata) values ' +
49.                         '(:somedata)';
50.  
51.       Query.ParamByName('somedata').AsInteger := 5;
52.       Query.ExecSQL;
53.       Query.ParamByName('somedata').AsInteger := 10;
54.       Query.ExecSQL;
55.       Query.ParamByName('somedata').AsInteger := 15;
56.       Query.ExecSQL;
57.       Query.ParamByName('somedata').AsInteger := 20;
58.       Query.ExecSQL;
59.       Query.ParamByName('somedata').AsInteger := 25;
60.       Query.ExecSQL;
61.       Query.ParamByName('somedata').AsInteger := 30;
62.       Query.ExecSQL;
63.     end;
64.  
65.     Query.Close;
66.     Query.SQL.Text := 'select myid, somedata ' +
67.                       'from mytablename';
68.     Query.Open;
69.     While not Query.EOF do
70.     begin
71.       WriteLn(Query.FieldByName('myid').AsInteger, ': ', Query.FieldByName('somedata').AsInteger);
72.       Query.Next;
73.     end;
74.     Query.Close;
75.   finally
76.     Query.Free;
77.     Transaction.Free;
78.     SQLite3Connection.Free;
79.   end;
80. end.

If you want to use Zeos components you can do it like this:

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

1. program sqlite_zeos;
2.  
3. uses
4.   SysUtils, ZConnection, ZDataset;
5.  
6. var
7.   ZConn: TZConnection;
8.   ZQuery: TZQuery;
9.  
10. begin
11.   try
12.     ZConn := TZConnection.Create(Self);
13.     ZConn.Database := 'mytest.db3';
14.     ZConn.Protocol := 'sqlite';
15.     // ZConn.LibraryLocation := 'sqlite-3.dll'; // you can put path and name here to sqlite dll if it doesn't find it
16.     ZConn.Properties.Append('ExtendedErrorMessage=1'); // show detailed errors
17.     try
18.       ZConn.Connect;
19.     except
20.       on E: Exception do
21.         WriteLn('Error connecting: ' + E.Message);
22.     end;
23.  
24.     if not ZConn.Connected then
25.       Exit;
26.  
27.     ZQuery := TZQuery.Create(nil);
28.     try
29.       ZQuery.Connection := ZConn;
30.  
31.       ZQuery.SQL.Text :=
32.         'select * ' +
33.         'from mytablename';
34.       ZQuery.Open;
35.  
36.       While not ZQuery.EOF do
37.       begin
38.         WriteLn(ZQuery.Fields[0].AsString);
39.         ZQuery.Next;
40.       end;
41.     finally
42.       ZQuery.Free;
43.     end;
44.   finally
45.     ZConn.Disconnect;
46.     ZConn.Free;
47.   end;
48. end.

Be sure that you have sqlite dll in the same directory (and that is of the correct bitness).

[MarkMLl]

Try this for sqlite:

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

1. program sqlite_sqldb;
2.     SQLite3Connection.Transaction := Transaction;
3.  

That I think is the missing line.

MarkMLl

[dseligo]

i've tried the example (and a lot more), by adapting it for sqlite,
but everytime i got an "Error: Access violation"
by line see '<<--'

here my example code wich i wrote by all examples of everything i show:

It would be helpful if you make complete compilible project so we can test it.
As MarkMLI wrote, you seem to lack TSQLTransaction - it is required for SQLDB components.

[Handoko]

but everytime i got an "Error: Access violation"

Have you made sure your SQLite already installed correctly? If you're using Linux you need to have libsqlite3-dev package installed. But if you're using Windows, you need to put sqlite3.dll in the same folder of the exe file.

[Tanto]

@dseligo
here a simple project that will run:

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

1. program project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   {$IFDEF UNIX}
7.   cthreads,
8.   {$ENDIF}
9.   Classes,
10.   databasemodul2;
11.  
12. var
13.   MyDataBase : TMyDataBaseConnector;
14. begin
15.   MyDataBase:= TMyDataBaseConnector.Create;
16.   MyDataBase.CheckUserEntry('', '');
17. end.
18.  

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

1. unit databasemodul2;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, fpWeb, db, sqldb, sqlite3conn,
9.   HelpModul1;
10.  
11. type
12.   TMyDataBaseConnector = class(TObject)
13.     sqlite3: TSQLite3Connection;
14.     dbquery: TSQLQuery;
15.     dbtrans: TSQLTransaction;
16.   private
17.     GHVar : TGlobalHelpModul;
18.   public
19.     constructor Create;
20.     function CheckUserEntry(uname: String; ukey: String): String;
21.   end;
22.  
23. var
24.   DBModul: TMyDataBaseConnector;
25.  
26. implementation
27.  
28.   constructor TMyDataBaseConnector.Create;
29.   begin
30.     GHVar:= TGlobalHelpModul.Create;
31.   end;
32.  
33.   function TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String): String;
34.   begin
35.     try
36.       sqlite3:= TSQLite3Connection.Create(nil);
37.       dbtrans:= TSQLTransaction.Create(nil);
38.  
39.       dbtrans.Action := caCommit;
40.       dbtrans.Options := [stoUseImplicit];
41.  
42.       sqlite3.DatabaseName:= 'keys.sq3';
43.       sqlite3.Transaction := dbtrans;
44.  
45.       try
46.         WriteLn('Connect');
47.         sqlite3.Connected:= True;
48.  
49.         dbquery:= TSQLQuery.Create(nil);
50.         dbquery.DataBase:= sqlite3;
51.         dbquery.Transaction:= dbtrans;
52.  
53.         if FileExists(sqlite3.DatabaseName) then
54.            WriteLn('File EXISTS');
55.  
56.         if sqlite3.Connected then
57.            WriteLn('CONNECTED');
58.  
59.         WriteLn('SELECT');
60.         dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';
61.  
62.         WriteLn('> Execute SQL');
63.         dbquery.Open;
64.  
65.         WriteLn('Read EOF');
66.         while not dbquery.EOF do
67.         begin
68.           WriteLn(dbquery.FieldByName('username').AsAnsiString);
69.           dbquery.Next;
70.         end;
71.       except on E: Exception do
72.         WriteLn('Something happens: ', E.ToString);
73.       end;
74.     finally
75.       sqlite3.Free;
76.     end;
77.  
78.  
79.     CheckUserEntry:= '';
80.   end;
81.  
82. end.
83.  
84.  

@Handoko
yes, i have the sqlite3.dll on the next to the program and on lazarus, by installing the packages for sqlite3

@MarkMLl
i have the transaction in line 35

thx

[rvk]

here a simple project that will run:

A few small remarks. (you didn't include TGlobalHelpModul but we can just strip it)

*) You don't have a "inherited;" in the TMyDataBaseConnector.Create. Although TObject.Create doesn't do much... it's best practice to always call inherited;

*) You have some major leaks
You do TSQLite3Connection.Create(nil); That's ok because you do sqlite3.Free at the end, so you can use nil.
But you also have TSQLTransaction.Create(nil); and TSQLQuery.Create(nil); You don't free those.
So either use an owner, like TSQLTransaction.Create(sqlite3); and TSQLQuery.Create(sqlite3); , so the sqlite3.free will also free the transaction and sqlquery.
Or... free them separately in the finally section. The first method is easier (because then you can't forget ).

/edit/ I see you did SQLQuery.Create(sqlite3); etc with the earlier example but changed it in nil (creating the memory leak).

*) You have CheckUserEntry := ''; at the end of the function. Although you can use the function-name for the result... it might be easier to read if you use Result := ''; (or other string).

*) You don't need var DBModul: TMyDataBaseConnector; because in project1 you use MyDataBase as 'local' variable.

But overall... yes, you're on your way... 

[Tanto]

@rvk
thx

i want to give you the information, i am working on windows 11 with the latest lazarus and fpc 3.2.2

best regards

[dseligo]

@dseligo
here a simple project that will run:

As rvk already said, it doesn't run.

Here is changed unit that runs:

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

1. unit databasemodul2;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, fpWeb, db, sqldb, sqlite3conn;
9.  
10.   //,  HelpModul1;
11.  
12. type
13.   TMyDataBaseConnector = class(TObject)
14.     sqlite3: TSQLite3Connection;
15.     dbquery: TSQLQuery;
16.     dbtrans: TSQLTransaction;
17.   private
18.     //GHVar : TGlobalHelpModul;
19.   public
20.     constructor Create;
21.     function CheckUserEntry(uname: String; ukey: String): String;
22.   end;
23.  
24. var
25.   DBModul: TMyDataBaseConnector;
26.  
27. implementation
28.  
29.   constructor TMyDataBaseConnector.Create;
30.   begin
31.     //GHVar:= TGlobalHelpModul.Create;
32.   end;
33.  
34.   function TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String): String;
35.   begin
36.     try
37.       sqlite3:= TSQLite3Connection.Create(nil);
38.       dbtrans:= TSQLTransaction.Create(nil);
39.       dbquery:= TSQLQuery.Create(nil);
40.       try
41.         dbtrans.Action := caCommit;
42.         dbtrans.Options := [stoUseImplicit];
43.  
44.         sqlite3.DatabaseName:= 'keys.sq3';
45.         sqlite3.Transaction := dbtrans;
46.  
47.         dbquery.DataBase:= sqlite3;
48.         dbquery.Transaction:= dbtrans;
49.  
50.         WriteLn('Connect');
51.         sqlite3.Connected:= True;
52.  
53.         if FileExists(sqlite3.DatabaseName) then
54.            WriteLn('File EXISTS');
55.  
56.         if sqlite3.Connected then
57.            WriteLn('CONNECTED');
58.  
59.         WriteLn('SELECT');
60.         dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';
61.  
62.         WriteLn('> Execute SQL');
63.         dbquery.Open;
64.  
65.         WriteLn('Read EOF');
66.         while not dbquery.EOF do
67.         begin
68.           WriteLn(dbquery.FieldByName('username').AsAnsiString);
69.           dbquery.Next;
70.         end;
71.       except on E: Exception do
72.         WriteLn('Something happens: ', E.ToString);
73.       end;
74.     finally
75.       sqlite3.Free;
76.       dbtrans.Free;
77.       dbquery.Free;
78.     end;
79.  
80.     CheckUserEntry:= '';
81.   end;
82.  
83. end.

I get this output (last line is expected since I don't have tables created):

Code: Text  [Select][+][-]

1. Connect
2. File EXISTS
3. CONNECTED
4. SELECT
5. > Execute SQL
6. Something happens: ESQLDatabaseError: TSQLite3Connection : no such table: web_app_admin

Try my code and post exact error messages. Did you put sqlite3.dll in directory with your program and check if bitness matches to your program?
Do you use Lazarus or only FPC?