Sqlite & Zeos

[Petrus Vorster]

Greetings all.

I am posting this under beginners since on this subject i know nothing.

I have used Sqlite in Basic, but with a nice class build by some genius. No data aware controls.
Here is LPC i have found DBF files to be much simpler and faster to deploy little apps.

I want to build something bigger, but my Sqlite sucks.
I have read a number of tutorials and most suggested I use ZEOS.

Well, numerous downloads, LPK installs and so forth, nothing shows up in the controls, rebuilds have no effect, and I am just frustrated. There are a bunch of remarks on how to install Zeos, and i am no further than when i started.

This work PC is on Win7 64bit. (I have to work with what i get.)

Before I go totally nuts and completely destroy my compiler like last time, can someone please give me an idiots tour on how to get zeos to show up in the control selector?

Thanks you for the time and patience.

-Peter

[cdbc]

Hi
In lieu of the first thing you said, about a class and no dbware controls, you could have a 'looksee' at the attached demo-project, a proof of concept of sorts...
Regards Benny

eta: An updated version 2 is available in reply #8 below

[Zvoni]

and contrary to everyone else, i'll suggest to go with sqldb (DB-aware or not) instead of ZEOS.
Completely sufficient for sqlite

[Thaddy]

Zeos is not really mainstream, but simply has some very active proponents.
If you want to use a deprecated format like DBF, simply use TDBF. It is fully native and very lightweight.
OTOH what is wrong with Sqlite3? It is more modern and you are likely to get better advice for it.

[Petrus Vorster]

Hi All

Perhaps I should go the route of not-data aware tools.
Although for the the tools i made that is very handy.

I had a try with db aware tools and Sqlite and ran into some errors I cannot imagine.
It will teach me a lesson to use other people's tools and classes and not to fully understand the underlying code.

Let me work through that Zip file, and perhaps come and bother you all later again.

Thanks for the help.

-Peter

[CharlyTango]

Hi

" have used Sqlite in Basic, but with a nice class build by some genius." means for me that you used code from someone else which tells me that you are not very experienced. No Problem.
But especially in the area of front-end development for a database, there are a few points to consider

I think because you mentioned "class" and "no db aware" @cbdc suggested a nice method connecting to a SQLite database avoiding graphical representation including a Singleton solution. Nice strategy which i also use in my larger projects.

BUT .. Without knowing what to do in principle, this can be a bit of a difficult task.

I'd suggest to try it the other way round and understand how it really works so that you are enabled to act when in trouble.

As a prerequisite you need a sqlite3.dll file (for your Windows OS) with the correct bitness -- which means 64bit in your case.
one copy in the projects directory and the other in the directory in which lazarus sits (there are other possibilities but that should be sufficient)

As an advice: each and every table in SQLite should have a primary key field (i use an integer field which ist filled automatically)

in the attached project i use db-aware controls to show data from the sqlite db. cbdc and me cover only the fetching aspect in the demo projects just to show that a connection to the DB is possible -- writing to DB ist quite another story.

in myproject there is includes a sql log so you can see wht really happens between the application and the database.

BTW: learning to handle a SQL database has a steep learning curve bit it pays in the end. DBF is an outdated format even if "I want to build something bigger"

To adapt the demo project to your needs you simply have to change the constant cSQLiteDBName in the file dmmain.pas.

[Petrus Vorster]

Thank you very much for the examples.
I will work through it today.

I do come from an era without db aware controls.
I love the DB aware controls in LPC using DBF files. They work great.
I have managed to build a number of useful tools with that approach.

Sqlite is next on this journey.

I will ask more questions later!

-Peter

[Handoko]

I do come from an era without db aware controls.
I love the DB aware controls in LPC using DBF files. They work great.

DB aware is good for prototyping. But for serious project, I personally avoid DB aware controls because I want to have full control of all the controls' behavior. For example you set a TDBGrid to a database. User changes the value of a cell in the grid then s/he press arrow down key. Now you play a guessing game. A database post command will be called and then set active record to the next record. It is not hard to guess what database operations will be processed. But now try it harder, if the user fill some invalid data into a cell and press arrow down key. An database error will be triggered and what's next? ... and so on. There will be too many possibilities. How do you make sure everything works as what you want? How do you validate all data before save it to the database. How do you prevent database exceptions?

Not using any DB aware controls may seem stupid, but full control, less headache, more peaceful sleep.

[cdbc]

Hi
Well, my proof of concept was a little lean on features, so I've finished the small app 
Attached is version 2, the now fully working countries app, check it out while it's Hot
Regards Benny

[Handoko]

Attached is version 2, the now fully working countries app ...

Nice, properly commented code.

But I wonder why you didn't use property? For examples, these were in your code:

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

1. function TLiteDb.get_Connection: TSQLite3Connection;
2. begin
3.   Result:= fDb;
4. end;
5.  
6. function TLiteDb.get_Transaction: TSQLTransaction;
7. begin
8.   Result:= fTrans;
9. end;
10.  
11. function TLiteDb.get_Exec: TSQLQuery;
12. begin
13.   Result:= fExec;
14. end;
15.  
16. function TLiteDb.get_Query: TSQLQuery;
17. begin
18.   Result:= fQuery;
19. end;

[cdbc]

Hi
@Handoko: I'm not entirely sure, that I know what you mean?!? Those are getters for props...
I thought that 'wrapper'-class was self-explanatory 
Regards Benny

[Handoko]

Oops sorry, my words were confusing.

I meant instead of writing like that, why not just skip the getters like this:

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

1.     property Connection: TSQLite3Connection read fDb;
2.     property Transaction: TSQLTransaction read fTrans;
3.     property Exec: TSQLQuery read fExec;
4.     property Query: TSQLQuery read fQuery;
5.  

Less code means easier to maintain and less risk for bugs.

[cdbc]

Hi
All right, now I understand...
I plan on making it an interface, when I find the time, that's why. 
Add to that that I use interfaces _A LOT_, it's second nature to me to write getters and setters 
I've made a Plugin-system, that I use like Delphi's runtime-packages AMO.
...Maybe I'll publish it someday 
Regards Benny

[Handoko]

I've made a Plugin-system, that I use like Delphi's runtime-packages AMO.

Interesting. I was and still collecting information on how to write a plugin. Still not capable, but I will do it in the future.