Lazarus y sqlite

En estos momentos estoy intentando aprender a programar una pequeña base de datos SQLlite con lazarus, para ello estoy utilizando la información de esta web:

http://alevel-computing.x10.mx/mw/index.php/Lazarus

http://www.alevel-computing.x10.mx/TutorialSoftwareRevision2.php

Lazarus Database Tutorial

Version 1

Contents

• Connect
• DBGrid and DBNavigator
• DBEdit
• DBMemo and DBImage and sound
• Using SQL
• Several tables
• Buttons instead of DBNavigator
• LazReport
• Summary

---

Top

Connect

Introduction Connecting to an external database is easy - as long as everything is in place before you try. You'll need sqlite3.dll, sqlite3.exe and the Student database from here. There is even a magnificent SQLite tutorial (which you will undoubtedly want to work through first here. if you are really keen.
Code You will find the SQLite3Connection component in the SQLDB component palette. It's invisible when the program runs. The best way is to leave the object inspector alone and set up the connection purely using code, as I have done. The key lines are

SQLite3Connection1.Database:='student.db
SQLite3Connection1.Connect:=true;

Don't forget you need 'student.db' and 'sqlite3.dll' in the same folder as your project.

Design Screenshot

 

Executable Screenshot

 
SourceCode Source code

---

Top

DBGrid and DBNavigator

Introduction You can build this on top of the 'Connect' project.
The DBGrid component makes it easy to see your database.
The DBNavigator makes it easy to maintain your database.
When you 'post' a change it changes the grid but not the actual database on disk. Just click 'COMMIT'to do that. Otherwise, they couldn't be easier to use.
Code

• SQLTransaction, SQLQuery (SQLDB palette) --> SQLConnection
• Datasource (DBAccess palette) --> SQLQuery
• DBGrid and DBNavigator (DBControls palette) --> Datasource.

You have a basic working database.

Design Screenshot

 

Executable Screenshot

 
SourceCode Source code

---

Top

DBEdit

Introduction You can build this on top of the 'DBNavigator' project.
The DBEdit component shows the database one entry at a tiem in edit boxes.

Code

• DBEdit (DBControls palette) --> Datasource.

You have a slightly better basic working database!

Design Screenshot

 

Executable Screenshot

 
SourceCode Source code

---

Top

DBMemo, DBImage and sound

Introduction Pictures and Sound in a database WOW!
The DBMemo lets you see longer comments or even whole books.
The DBImage lets you see all knds of graphics for each entry.
Sound uses the Windows command ShellExecute() which is Windows specific - you need to use TProcess for other operating systems
Code

• ShellExecute needs you to write 'uses shellapi' (see source code)
• ad your own images and sounds. The sound format is mp3, mid or WAV.
• DBMemo and DBImage (DBControls palette) --> Datasource.

You can put entire text files into an SQLite database directly, but having a link to them is musch simpler and neater. You now have a database deluxe.

Design Screenshot
 

Executable Screenshot
 

SourceCode Source code

---

Top

Using SQL

Introduction This uses what you have done so far and adds to it. It displays the data in two other ways - in a stringGrid and in a memo box. Some people feel more comfortable with those. It also lets you execute any SQL statement.
This is useful because you can run queries such as

'SELECT * FROM Student WHERE First like "M%"

'INSERT INTO Student VALUES (244, 'Charlie','Chaplin','13We'"

which will pick out everyone whose first name begins with 'M' or add the comedian to the database. Code
To create a new procedure you can put it in the 'Public' section of Form1. In its definition, don't froget to rite TForm1 as in
TForm1.InsertIntoDatabase;
There are new procedures for INSERT and SELECT type statements. There are new procedures for using the stringgrid and memo box.

SQLite3Connection1.Database:='student.db
SQLite3Connection1.Connect:=true;

Don't forget you need 'student.db' and 'sqlite3.dll' in the same folder as your project.

Design Screenshot
 

Executable Screenshot
 

SourceCode Source code

---

Top

Multiple tables

Introduction This uses what you have done in the previous tutorial and adds to it. It lets you view and edit any one of the four tables.

You will need to get Student2.db by making it from the Student2.txt file of SQL here .
This is now becoming a fully woking relational database
Code The database here is called student2 and the SQL can be used to create it in exactly the same way as before. Don't forget you need 'student2.db' and 'sqlite3.dll' in the same folder as your project.

Design Screenshot
 

Executable Screenshot
 

SourceCode Source code

---

Top

Buttons instead of DBNavigator

Introduction Useful as the DBNavigator is, it's also interesting to see how to build a database without it. Here it is.
There is a lso a 'search function here- very easy to implement.

Code The navigation buttons all involve single line instructions like SQLQuery.next. There is a mixture of DBedits and normal edit boxes. The delete and update (edit) functions are basically the same and share a set of DBEdits. The insert button uses normal edit boxes, because they don't need to be linked to the database.

Design Screenshot

 

Executable Screenshot

 

SourceCode Source code

---

Top

Using LazReport

Introduction There is a package you can install into Lazarus that lets you view and print the data nicely formatted. It is called LazReport. This package doesn't come ready-installed because not everyone will need it.
Code The code explains how to install the LazReport package. It will take about 15 minutes whenyou do it for the first time. You need to make sure that Printer4Lazarus is also installed - it probably is.
The components you will use are

• frDBDataSet (LazReport palette) -- > Datasource
• frReport (LazReport palette) --> Datasource

Design Screenshot

 

Executable Screenshot
 



SourceCode Source code

---

Top

Tutorial Summary

Tutorial	Uses	Resources	SQL	Purpose
Connect	SQLite3Connection	Student.db	Student.txt	To get a basic connection
DBGrid and DBNavigator	SQLTransacction, SQLQuery, DataSource, DBGrid, DBNavigator	Student.db	Student.txt	To show DBNavigator working
DBEdit	DBEdit	Student.db	Student.txt	To show DBEdits in action
DBMemo	DBMemo, DBImage	Student.db, Sounds folder, Details folder, Pictures folder	Student.txt	Multimedia database
SQL	StringGrid, Memo	Student.db	Student.txt	Using SELECT and INSERT commands directly
Multiple tables		Student2.db	Student2.txt	Using 4 related tables in your program
Buttons	Locate()	Student.db	Student.txt	How to implemet the database without DBNavigator
LazReport		Student.db, LazReport package frDBDataSet, frReport	Student.txt	How to make a table look good