unit Unit1;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, sqlite3conn, sqldb;
type
TTestDB = class(TObject)
SQLConn: TSQLite3Connection;
SQLQuery: TSQLQuery;
SQLTran: TSQLTransaction;
private
procedure CreateTable;
public
constructor Create;
destructor Destroy; override;
function OpenDB(FileName: string): boolean;
procedure CloseDB(Save: boolean = True);
function GetData(ID: integer): TBytes;
procedure SetData(ID: integer; Data: TBytes);
function NewRecord(Data: TBytes): integer;
end;
{ TForm1 }
TForm1 = class(TForm)
procedure FormCreate(Sender: TObject);
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
procedure Test;
var
FileName: string;
bytes: TBytes;
TestDB: TTestDB;
i: integer;
begin
FileName := ExtractFilePath(Application.ExeName);
FileName := ConcatPaths([FileName, 'test.db']);
DeleteFile(FileName);
TestDB := TTestDB.Create;
TestDB.OpenDB(FileName);
SetLength(bytes, 5 * sizeof(integer));
for i := 0 to 4 do
PInteger(@bytes[i * Sizeof(integer)])^ := i + 1;
// Write
TestDB.NewRecord(bytes);
// Read
bytes := TestDB.GetData(1);
for i := 0 to 4 do
begin
ShowMessage(IntToStr(PInteger(@bytes[i * Sizeof(integer)])^));
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
Test;
end;
constructor TTestDB.Create;
var
LibFile: string;
AppPath: string;
begin
inherited Create;
SQLConn := TSQLite3Connection.Create(nil);
SQLTran := TSQLTransaction.Create(nil);
SQLQuery := TSQLQuery.Create(nil);
SQLConn.Transaction := SQLTran;
SQLTran.DataBase := SQLConn;
SQLQuery.DataBase := SQLConn;
AppPath := ExtractFilePath(Application.ExeName);
{$ifdef MSWINDOWS}
LibFile := ConcatPaths([AppPath, 'lib', 'sqlite3.dll']);
{$else}
LibFile := ConcatPaths([AppPath, 'lib', 'libsqlite3.so']);
{$endif}
if FileExists(LibFile) then
SQLiteLibraryName := LibFile;
end;
destructor TTestDB.Destroy;
begin
FreeAndNil(SQLQuery);
FreeAndNil(SQLTran);
FreeAndNil(SQLConn);
inherited Destroy;
end;
procedure TTestDB.CreateTable;
begin
SQLConn.ExecuteDirect('Create Table "main"("data" Blob);');
end;
function TTestDB.OpenDB(FileName: string): boolean;
begin
SQLConn.DatabaseName := FileName;
SQLConn.Open;
SQLTran.Active := True;
CreateTable;
end;
procedure TTestDB.CloseDB(Save: boolean = True);
begin
if SQLTran.Active then
if Save then
SQLTran.Commit
else
SQLTran.Rollback;
SQLConn.Close(True);
end;
function TTestDB.NewRecord(Data: TBytes): integer;
begin
SQLQuery.SQL.Text :=
'Insert Into main (data) Values (:data)';
SQLQuery.Params.ParamByName('data').AsBytes := Data;
SQLQuery.ExecSQL;
SQLQuery.SQL.Text := 'Select last_insert_rowid() From main';
SQLQuery.Open;
Result := SQLQuery.Fields[0].AsInteger;
SQLQuery.Close;
end;
function TTestDB.GetData(ID: integer): TBytes;
begin
SQLQuery.SQL.Text := 'Select data From main where rowid=:rowid';
SQLQuery.Params.ParamByName('rowid').AsInteger := ID;
SQLQuery.Open;
Result := SQLQuery.Fields[0].AsBytes;
SQLQuery.Close;
end;
procedure TTestDB.SetData(ID: integer; Data: TBytes);
begin
SQLQuery.SQL.Text := 'Update main Set data=:data Where rowid=:rowid';
SQLQuery.Params.ParamByName('data').AsBytes := Data;
SQLQuery.Params.ParamByName('rowid').AsInteger := ID;
SQLQuery.ExecSQL;
end;
end.