The sqldb.pp unit documentation has been added into LUFDOC and notes will be added by Powtils developers, however it is not completed yet.
Inside the FCL-DB directory included with Free Pascal sources are some subdirectories in the /packages/fcl-db/ area:
All base classes are in the db unit, the dbconst unit contains the used resourcestrings. memds contains a dataset-class in memory which can be read from, and written to a stream sqldb contains a framework to work with several SQL-based databases as Interbase, Firebird, MySQL, PostgreSql, Sqlite3, ODBC and Oracle dbase contains the tDbf components, to work with DBASE and FoxPro file-based databases sdf contains a dataset class to use text files directly as a database. That could be fixed-size, or limited (SDF) sqlite contains dataset classes to use Sqlite and Sqlite3 unmaintained contains some obsolete units which were replaced by better alternatives, or which are old tests which are not needed anymore
Here is an introduction on how to use FCL-DB:
From the TSQLConnection point-of-view the following methods are called if a
select-statement is used:
OPEN:
Prepare: (is only called when prepared is false)
- AllocateCursorHandle (only if the cursor <> nil)
- Preparestatement
Execute:
- Execute
- AddFieldDefs (only if called for the first time after a prepare)
GETNEXTPAKCET: (probably called several times, of course)
- Fetch
- Loadfield
CLOSE:
- FreeFieldBuffers
- UnPrepareStatement (Only if prepare is False, thus if prepared queries
were not supported)
UnPrepare:
- UnPrepareStatement
DESTROY:
- DeAllocateCursorHandle (Also called if the Connection is changed)
From the TSQLConnection point-of-view the following methods are called if a non-
select-statement is used (execsql):
Prepare: (is only called when prepared is false)
- AllocateCursorHandle (only if the cursor <> nil)
- Preparestatement
Execute:
- Execute
- UnPrepareStatement (Only if prepare is False, thus if prepared queries
were not supported)
UNPREPARE:
- UnPrepareStatement
DESTROY:
- DeAllocateCursorHandle (Also called if the Connection is changed)
A short description of what each method in a TSQLConnection should do:
* Function AllocateCursorHandle : TSQLCursor; override;
This function creates and returns a TSQLcursor which can be used by any query
for the used type of database. The cursor is only database-dependent, it is
deallocated when the connection of the query changes, or if the query is
destroyed.
* Procedure DeAllocateCursorHandle(var cursor : TSQLCursor); override;
This function deallocates the TSQLCursor, and sets its value to nil.
* procedure PrepareStatement(cursor: TSQLCursor;ATransaction : TSQLTransaction;
buf : string; AParams : TParams); override;
This functions prepares the query which is given in buf.
It's only called if Prepared is True.
If the database supports prepared queries for the kind of sql-statement (in
cursor.FStatementType) and the prepare was successfully, then cursor.FPrepared
is set to True, so that prepare will not be called again, until UnPrepared
is called. (which sets FPrepared to False)
* procedure FreeFldBuffers(cursor : TSQLCursor); override;
This procedure is called if a Select-query is closed. This procedure is used to
handle all actions which are needed to close a select-statement.
Using FCL-Db isn't difficult, just follow these simple steps and you'll be mastering it in no time.
FCL-DB allows connecting to different database and storage technologies, such as memory datasets, SDF, DBase and SQL-based. Here, the SQL-Based method will be explained.
First of all, a connection to the database must be established before sending or extracting data from it. FCL-Db currently supports Interbase, Firebird, MySQL, PostgreSql, Sqlite3, ODBC and Oracle, so you must decide which connection unit want to include in the uses clause of your unit/program.
This list shows database / connection unit relations:
As an example, if you want to connect to Interbase, you must include the units IBconnection and SQLDb.
The steps you must always follow before attempting to execute a query are:
Now, to use queries, just instantiate a TSQLQuery and assign its database property to the connection created before.
To illustrate the previous paragraphs, nothing better than an example.
program fcldbdemo;
{$mode objfpc}{$H+}
uses
SysUtils,
SqlDb,
IBConnection;
var
conn: TIBConnection;
query: TSQLQuery;
transaction: TSQLTransaction;
begin
conn := TIBConnection.Create(nil);
query := TSQLQuery.Create(nil);
transaction := TSQLTransaction.Create(nil);
try
try
with conn do
begin
HostName := 'localhost';
UserName := 'sysdba';
Password := 'masterkey';
DatabaseName := 'C:\dev\blog\company.ib';
Connected := True;
end;
conn.Transaction := transaction;
query.DataBase := conn;
query.SQL.Text := 'select * from customers';
query.Open;
//Important: if you want to get the RecordCount,
//the dataset must be at Last record.
query.Last;
writeln(query.RecordCount);
query.First;
while not query.EOF do
begin
writeln(query.FieldByName('name').AsString);
query.Next;
end;
finally
transaction.Free;
query.Free;
conn.Free;
end;
except
on E: Exception do
writeln(E.message);
end;
readln;
end.