Recent

Author Topic: Help me with a simple SQLite3 program to bring in 1,000+ new Laz Programmers  (Read 3049 times)

tan

  • New Member
  • *
  • Posts: 33
Although I think I am probably about the least competent Laz programmer in the world, I’ve decided to write some documentation on how easy it is to display a simple database  in Lazarus’ GUI.   The catch is I still don’t know exactly how easy it is to do that myself, because the current state of Laz-SQLite3 documentation is so ... inadequate.  The inadequacy has partially to do with a large mass of obsolete or significantly contingent documentation; partially to do with the huge scope of Laz and the multitude of databases and OS implementations; partially to do with my own inadequacies (and assumptions that I already know lots of things I don't already know); and partially to do with there, apparently, simply not being a COMPLETE example of a whole GUI-SQLite3 program for me to copy and run. 

What gives me hope, however, is that the state of Laz itself - at least on Windows 7 where I’ve been able to run it - is insanely great and stable (the developers have me in awe)...and Laz being written in Laz speaks for itself!

So, my hypothesis is that IF I/we can properly show new programmers (in particular people who are currently candidates for Python as a first language) how easy it is do something real and substantial in Laz and have it display in a gorgeous GUI “batteries included”, there is going to be a lot of interest ... a lot of people suddenly VERY seriously interested in Laz/FP.  I would expect it to be pretty easy to bring in a thousand or more new programmers pretty quickly if we pull this off.

My proposal is that SQLite3 and Windows 7 will be THE medium case and the program be a very short program working with a pre-existing (or easy to create outside of Laz) single table database.  My goal is NOT to teach anybody anything much about Pascal itself, nor about SQL, nor Windows, but to give them a working example that they can bring up in a few minutes in order to get the thrill that originally was available to anyone who sat down with a microcomputer 40 years ago: it just works.  Thereafter they can spend hours modifying the code incrementally, if they wish, but the issue of whether Laz is awesome and worth the effort to explore will no longer be at issue.

What I would like to see, personally, right now is a COMPLETE program which opens an existing single table of a database and displays all the records inside a grid component.  I'd like to be able to cut and paste it and run it.  Afterwards I'll figure out, or ask, how to create it using drag and drop if there is anything which perplexes me.

Doing a lot of hand waving in various directions, presenting parts of a working program and otherwise just playing 52 card pickup with someone who needs help (in this case me), isn’t helpful.  If you don’t want to be, or can’t be, fully helpful and fully supportive of this project then please don’t post anything on this thread.

As I said, this isn’t something I wanted to do personally -- I admit upfront I am incompetent in Laz. But I am educable and I want to see this through instead of just complaining any further. I previously offered to pay someone to write the documentation I think Laz desperately needs to begin a bootstrap process.  Now that is off the table and I am just going to do it myself (or, at least, write the documentation/article myself). 

But I sure would appreciate it if someone would simply throw together a bare-bones but complete program to open and display a preexisting SQLite3 table under Windows 7 and display it in a grid. That would save me, doubtless, many more hours of trial and error and 52 card pickup frustration.  And it would take you - what? - 30 minutes??   I don’t want to use any outside packages, such as Zeos (that has wasted a goodly amount of my time already all by itself).  I'm hoping the eventual solution is super-simple and contingency free.  I have already made a table with DB Browser for SQLite and populated it and placed it, along with the Windows 64 dll in C:/laz-sql/ where I expect the program to go.  DB Browser, as with Laz itself, is super simple to bring up.   Anyway, please post COMPLETE, TESTED programs only.  Thank you!

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Have you tried the examples that bundled with Lazarus installation?
Lazarus main menu > Tools > Example Projects > on the Project filter box, type "sql"

Note:
Don't run it directly from there but save them first to a new location.

Akira1364

  • Hero Member
  • *****
  • Posts: 561
I don't think SQLite3 is generally considered to be anywhere close to as cool as you think it is.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
I wrote a simple program, operating on one SQLite3 database with two tables in it.
One table is Person, and another one is EAddresses.  This database is relational in that a person might one or more addresses.    Person.id = EAddresses.Person_id.

On the main form, you have two tabsheets. Both of them have two DBGrids and one DBNavigator. First DBGrid shows the content of Person  table. Second DBGrid shows EAddresses contents of the "PERSON". 

First tabsheet uses two TSQLite3DataSet.  Please check MasterSource, MasterFields, and IndexFieldNames of second dataset (i.e. tblAddr).


On the second tabsheet, I use an SQLDB component, TSQLiteConnection and TSQLQuery.  The first DBGrid shows the content of tblPerson as the first tabsheet does. The second DBGrid is linked to datasource3, which is hooked to SQLQuery1.

The SQL of SQLQuery1 is:
   Select * from EAddresses where person_id = :id

So you have to fill the parameter id.   It is done at event of datasource1datachange. The content is :

  SQLQuery1.Close;
  SQLQuery1.ParamByName('id').AsInteger := tblPerson.FieldByName('id').AsInteger;
  SQLQuery1.Open;

So, whenever you change the active? focused? current? row of tblPerson (regardless of at tabsheet1 or tabsheet2), SQLQuery1 is closed, parameter value is changed, and then re-opened.

Hope this is useful to you.


Two notices:

1)  At specifying database file name of TSQLite3DataSet, I had to write the full path.
2)  Before specifying the MasterFields and IndexFieldNames, you'd better to close the dataset. Or you have to close and reopen it.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Code: Pascal  [Select][+][-]
  1. The SQL of SQLQuery1 is:
  2.    Select * from EAddresses where person_id = :id
  3.  
  4. So you have to fill the parameter id.   It is done at event of datasource1datachange. The content is :
  5.  
  6.   SQLQuery1.Close;
  7.   SQLQuery1.ParamByName('id').AsInteger := tblPerson.FieldByName('id').AsInteger;
  8.   SQLQuery1.Open;
  9.  
If you working with relations like that, you haven't understand the SQL base. Field 'id' must be primary and a second field 'personid' is the trigger of the relation to get the related records.
Code: Pascal  [Select][+][-]
  1.   SQLQuery1.ParamByName('personid').AsInteger := tblPerson.FieldByName('id').AsInteger;
  2.  
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

egsuh

  • Hero Member
  • *****
  • Posts: 1273

SQLQuery1 doesn't have any Param named personid.

EAddresses table does not have a field named 'id' nor primary key. It has field named person_id, and I'm trying to link the two tables with those fields.

I'm not a full-time programmer, and doubt whether I really understand relational database.  So if you have any recommendations or corrections for me, I welcome any time. 

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Code: SQL  [Select][+][-]
  1. CREATE TABLE persons (
  2.   id INTEGER NOT NULL PRIMARY KEY,
  3.   lastname VARCHAR(30),
  4.   firstname VARCHAR(30)):
  5.  
  6. CREATE adresses (
  7.   id INTEGER NOT NULL PRIMARY KEY,
  8.   personid INTEGER NOT NULL,
  9.   address VARCHAR(40),
  10.   zipcode VARCHAR(10),
  11.   city VARCHAR(30),
  12.   country VARCHAR(30),
  13.   INDEX 'adresses_personid' ON personid));
  14.  
These two tables has a 1 - n relation. The field 'personid' relies on the field 'id'. To create an index on 'personid' your records are found very quickly. Normally a person doesn't have multible addresses. But if you create a table contacts, it's more usefull. Because a person can have several contacts. Just like facebook or whatsapp.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

tan

  • New Member
  • *
  • Posts: 33
It will likely take me a few days to work through the suggestions so far, but I want to acknowledge everyone who has contributed to the discussion so far without delay!

Handoko:  I didn't even know there were working example projects under "tools", much less that there might be something to model.  I was able to compile and run the first application.  It is more complicated than I would prefer, and has some problems when I ask it to use SQLite specifically, but it RUNS and DISPLAYS.  That is a huge step in the right direction, makes it worthy of more careful study, and THANK YOU.  I will look at all the examples and get back to you with any questions I can't resolve.

egsuh: That might be about perfect as a teaching tool, as it is SQLite3 specific and simpler than most projects.   Einstein supposedly once chided his community "Things should be as simple as possible, and no simpler".  He also supposedly said that when you read something and can't understand it because of all manner of polysyllabic words and long sentences, it doesn't mean that you are incompetent; it means that the author is incompetent.  i will get back to you!

With regard to the other comments so far, I sincerely appreciate people wanting to make things "better". But worrying about "better" before good-enough is even delivered is an assured way to make pretty much everything stillborn: from a multi-million-dollar company to a lazarus article ... the 1000+ new programmers Lazarus could really use right now.   

Goethe: "The thing that matters most must not be at the mercy of the things which matter less."

 

TinyPortal © 2005-2018